Team was working on the upgrade issue from 10.2.0.4 to 18.104.22.168 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 10.2.0.4) to final version (22.214.171.124)
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