This discussion is archived
6 Replies Latest reply: Jul 24, 2012 4:57 AM by KuljeetPalSingh RSS

11g migraion failed sql queries

Kapil Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I would run STATS on all the tables and indexes with "AUTO_SAMPLE_SIZE" option.
  • 6. Re: 11g migraion failed sql queries
    KuljeetPalSingh Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points