6 Replies Latest reply: Jul 24, 2012 6:57 AM by kuljeet singh - RSS

    11g migraion failed sql queries

    Kapil
      Hi,

      As part of maintainace, DB is migrated from 10g to 11g.

      On 10g the tempsace is 10G and on 11g it is 20G. But still some of the bulk reports are failing with unable to extend temp segment on 11g. But running on same data volume on 10g the report executes fine. There is no change in data or objects as both dbs are connected with golden gate.

      The data base parameter on 10g and 11g has kept same by DBA. Please advise what could be the probable cause of errors ?

      The db versons are-
      10g :
      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for Solaris: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
      11g
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE    11.2.0.2.0      Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      
      SQL> 
        • 1. Re: 11g migraion failed sql queries
          sybrand_b
          The probable cause of error is you didn't set up a test database to test the application under 11g, and 'assumed' nothing would change.
          However, usually in every major release the CBO is revised, resulting in different execution paths.
          Also you, if you migrated the database by dumps, the clustering factor of many indexes can have changed, resulting in different execution plans.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: 11g migraion failed sql queries
            Tubby
            Kapil wrote:
            Hi,

            As part of maintainace, DB is migrated from 10g to 11g.

            On 10g the tempsace is 10G and on 11g it is 20G. But still some of the bulk reports are failing with unable to extend temp segment on 11g. But running on same data volume on 10g the report executes fine. There is no change in data or objects as both dbs are connected with golden gate.

            The data base parameter on 10g and 11g has kept same by DBA. Please advise what could be the probable cause of errors ?
            First guess would be inefficient execution plans.

            Pick a specific SQL you're having a problem with and compare what it did on the pre-upgrade instance (hopefully you have one) with what it's doing now.

            Cheers,
            • 3. Re: 11g migraion failed sql queries
              Kapil
              Thanks to both of you...

              Yes, as you correctly directed.. the query plan on 11g and 10g is different.

              Please advise how this can be fixed ? Is there anything that can be done at configuration level which DBA can fix. Or the complex queries has to be reopend and fixed on 11g env by dev ?

              Your adivse is valuable as this will help to estimate cost.
              • 4. Re: 11g migraion failed sql queries
                Srini Chavali-Oracle
                It is impossible to tell without specifics. In general, most queries will run faster on later versions of the database. Pl see if these MOS Docs can help

                Query Performance Degradation - Upgrade Related - Recommended Actions [ID 745216.1]     
                Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results [ID 1320966.1]     
                Using SQL Performance Analyzer to Test SQL Performance Impact of an Upgrade [ID 562899.1]     
                Testing SQL performance impact of upgrade from 10g to 11g using SQL Performance Analyzer [ID 1363104.1]     
                How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g [ID 465787.1]     
                Tips for avoiding upgrade related query problems [ID 167086.1]     
                Recording Access Path Information Prior to an upgrade to 10g or 11g [ID 466350.1]     

                Have you applied the latest PSU patch for 11.2.0.2 ? How was the database upgraded ?

                HTH
                Srini
                • 5. Re: 11g migraion failed sql queries
                  user578245
                  I would run STATS on all the tables and indexes with "AUTO_SAMPLE_SIZE" option.
                  • 6. Re: 11g migraion failed sql queries
                    kuljeet singh -
                    Also have a look

                    http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf


                    Please close the thread if you feel you have the answer and keep the Oracle forum clean.
                    https://forums.oracle.com/forums/ann.jspa?annID=885