Sudden Performance Change after database upgrade

Team was working on the upgrade issue from to where some queries were taking long time. In this case, since there were lot of intermediate releases,  check should be done that from which release actually the issues starts 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.

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 to final version (
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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s