Sudden Performance Change after Upgrade


I was working on the upgrade issue from 10.2.0.4 to 11.2.0.2 where some queries were taking long time. In this case, since there were lot of intermediate releases so we had to check in which release actually the issues start coming.

Found the solution for this in the excellent blog by Coskan, where he described step by step methodology to understand and resolve these issues.

http://coskan.wordpress.com/2011/01/17/11-2-0-2-performance-stories/

This is kind of narrow down approach in which things that are causing issues are caught (and fixed for some time)and then work on them with Oracle to have proper fix

It is based on the view v$system_fix_control which contains the list of bugs fixed in particular release.

Following is the approach :

1. Get the sql_id from for which plan is changing across releases
2. Get the explain plan in all the versions(get from the v$system_fix_control) starting from the start version (say 10.2.0.4) to final version (11.2.0.2)
3. Version in which plan changes, get the list of all bug fixed in that
4. Then for that bug fixes, try to “off” one of the fixes to get the right performance
5. Followup with Oracle for that to resolve that issue in proper manner

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s