5 Replies Latest reply: Jan 2, 2013 1:14 PM by 845697 RSS

    SQL Performance very slow after upgrade.

    845697
      We recently upgraded from 10.2.0.5 to 11.2.0.1, and the performance of some of our SQL queries has slowed considerably. In one example a query that runs almost instantly in 10g takes over 30 seconds in 11g. When I run an autotrace, the LAST_CR_BUFFER_GETS in 10g is 1067 and in 11g it's 621006!!!

      I don't know why the same exact query would produce such different results. I'm sure it is a tuning issue, but any ideas on where to start would be much appreciated.
        • 1. Re: SQL Performance very slow after upgrade.
          Iordan Iotzov
          The optimizer in 11.2.0.1 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 11.2.0.1. Are they up to date? You give SQL Tuning Adviser a try if you are licensed.

          Next, you either hint the query in 11.2.0.1 to get the 10g execution plan or (if you have appropriate licenses) try to seed SPM in 11.2.0.1 with stable execution plans from 10.2

          Iordan Iotzov
          http://iiotzov.wordpress.com/
          • 2. Re: SQL Performance very slow after upgrade.
            Srini Chavali-Oracle
            11.2.0.1 has many known performance issues. If you are going to upgrade, your target should have been 11.2.0.3. 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]

            HTH
            Srini
            • 3. Re: SQL Performance very slow after upgrade.
              845697
              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?


              Thanks again!
              • 4. Re: SQL Performance very slow after upgrade.
                Rob_J
                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:
                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;
                And then I change them to these:
                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');
                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.

                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.
                • 5. Re: SQL Performance very slow after upgrade.
                  845697
                  Thanks for the help. After re-gathering all stats, the performance is back to where it should be.