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

    SQL Performance very slow after upgrade.

      We recently upgraded from to, 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 is different than the one in, 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 Are they up to date? You give SQL Tuning Adviser a try if you are licensed.

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

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

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