This content has been marked as final. Show 5 replies
The optimizer in 126.96.36.199 is different than the one in 10.2.0.5, so some of the execution plans would be different. While most of the plans would get better, some may get worse. Your query may be one of those that regressed after the upgrade.
Now, the first thing to check are the statistics in 188.8.131.52. Are they up to date? You give SQL Tuning Adviser a try if you are licensed.
Next, you either hint the query in 184.108.40.206 to get the 10g execution plan or (if you have appropriate licenses) try to seed SPM in 220.127.116.11 with stable execution plans from 10.2
18.104.22.168 has many known performance issues. If you are going to upgrade, your target should have been 22.214.171.124. Is this a test instance or a production instance ? Has the gather statistics job been run ?
Pl see if these MOS Docs can help
Query Performance Degradation - Upgrade Related - Recommended Actions [ID 745216.1]
Avoiding Upgrade Related Query Issues [ID 167086.1]
How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g [ID 465787.1]
Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]
Best Practices: Proactively Avoiding Database and Query Performance Issues [ID 1482811.1]
Thanks for the MOS Docs, I'll read thru them tonight.
After importing our data into 11g we ran the following command for all schema's:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema name',cascade=>TRUE);
Is there another command we should run?
That command will gather stats but the default values may be different between 10g and 11g. You should check what they are. Here are the ones that I change:
And then I change them to these:
select dbms_stats.GET_PREFS('AUTOSTATS_TARGET') from dual; select dbms_stats.GET_PREFS('CASCADE') from dual; select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual; select dbms_stats.GET_PREFS('METHOD_OPT') from dual; select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual; select dbms_stats.GET_PREFS('GRANULARITY') from dual; select dbms_stats.GET_PREFS('PUBLISH') from dual; select dbms_stats.GET_PREFS('INCREMENTAL') from dual; select dbms_stats.GET_PREFS('DEGREE') from dual;
Your preferences might be different, but we don't use histograms and we always sample 100% of the data. Check out what the other parameters mean in the Oracle docs.
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('CASCADE','TRUE'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT','100'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 1'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('NO_INVALIDATE','FALSE'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('GRANULARITY','ALL'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE');
Have a read of this about [url http://www.ora00600.com/scripts/statistics/restore_schema_stats.html]restoring schema stats and this one about [url http://www.ora00600.com/scripts/statistics/gather_system_stats.html]gathering system statistics.
When you gather stats you may need to flush the shared pool afterwards so that they SQL queries are hard parsed again and use the new stats. Use ALTER SYSTEM FLUSH SHARED_POOL but probably best to do that in a period of low activity.
Thanks for the help. After re-gathering all stats, the performance is back to where it should be.