3 Replies Latest reply: Jan 26, 2013 8:56 AM by sb92075 RSS

    Oracle 11g Migration performance issue

    118545
      Hello,

      There a performance issue with Migration from Oracle 10g(10.2.0.5) to Oracle 11g(11.2.0.2).

      Its very simple statement hanging for more than a day and later found that query plan is very very bad. Example of the query is given below:

      INSERT INTO TABLE_XYZ
      SELECT F1,F2,F3
      FROM TABLE_AB, TABLE_BC
      WHERE F1=F4;

      While looking at cost in Explain plan :

      on 10g --> 62567
      0n 11g --> 9879652356776

      Strange thing is that

      Scenario 1: if I issue just query as shown below, will display rows immediately :

      SELECT F1,F2,F3
      FROM TABLE_AB, TABLE_BC
      WHERE F1=F4;

      Scenario 2: If I create a table as shown below, will work correctly.

      CREATE TABLE TABLE_XYZ AS
      SELECT F1,F2,F3
      FROM TABLE_AB, TABLE_BC
      WHERE F1=F4;

      What could be the issue here with INSERT INTO <TAB> SELECT <COL> FROM <TAB1>?
        • 1. Re: Oracle 11g Migration performance issue
          Hoek
          Are the table and index statistics on 11g up-to-date?

          Can you post the complete execution plans (formatted)?
          See #7, 8 and 9 here: {message:id=9360002}
          More tips for posting tuning requests: {message:id=9360003}
          • 2. Re: Oracle 11g Migration performance issue
            118545
            Table:

            CREATE TABLE AVN_WRK_F_RENEWAL_TRANS_T
            (
            "PKSRCSYSTEMID" NUMBER(4,0) NOT NULL ENABLE,
            "PKCOMPANYCODE" VARCHAR2(8 CHAR) NOT NULL ENABLE,
            "PKBRANCHCODE" VARCHAR2(8 CHAR) NOT NULL ENABLE,
            "PKLINEOFBUSINESS" NUMBER(4,0) NOT NULL ENABLE,
            "PKPRODUCINGOFFICELIST" VARCHAR2(2 CHAR) NOT NULL ENABLE,
            "PKPRODUCINGOFFICE" VARCHAR2(8 CHAR) NOT NULL ENABLE,
            "PKEXPIRYYR" NUMBER(4,0) NOT NULL ENABLE,
            "PKEXPIRYMTH" NUMBER(2,0) NOT NULL ENABLE,
            "CURRENTEXPIRYCOUNT" NUMBER,
            "CURRENTRENEWEDCOUNT" NUMBER,
            "PREVIOUSEXPIRYCOUNT" NUMBER,
            "PREVIOUSRENEWEDCOUNT" NUMBER
            )
            SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
            (
            INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
            )
            TABLESPACE "XYZ" ;


            Explain Plan(With Insert Statement and Query):_

            INSERT STATEMENT, GOAL = ALL_ROWS               Cost=9110025395866     Cardinality=78120     Bytes=11952360
            LOAD TABLE CONVENTIONAL     Object owner=ODS     Object name=AVN_WRK_F_RENEWAL_TRANS               
            NESTED LOOPS OUTER               Cost=9110025395866     Cardinality=78120     Bytes=11952360
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_WRK_F_RENEWAL_TRANS_1ST     Cost=115     Cardinality=78120     Bytes=2499840
            VIEW PUSHED PREDICATE     Object owner=ODS          Cost=116615788     Cardinality=1     Bytes=121
            SORT GROUP BY               Cost=116615788     Cardinality=3594     Bytes=406122
            VIEW     Object owner=SYS     Object name=VW_DAG_1     Cost=116615787     Cardinality=20168     Bytes=2278984
            SORT GROUP BY               Cost=116615787     Cardinality=20168     Bytes=4073936
            NESTED LOOPS OUTER               Cost=116614896     Cardinality=20168     Bytes=4073936
            VIEW     Object owner=SYS          Cost=5722     Cardinality=20168     Bytes=2157976
            NESTED LOOPS               Cost=5722     Cardinality=20168     Bytes=2097472
            HASH JOIN               Cost=924     Cardinality=1199     Bytes=100716
            NESTED LOOPS                         
            NESTED LOOPS               Cost=181     Cardinality=1199     Bytes=80333
            TABLE ACCESS BY INDEX ROWID     Object owner=ODS     Object name=INWARDSPOLICYDETAILS     Cost=159     Cardinality=1199     Bytes=39567
            INDEX RANGE SCAN     Object owner=ODS     Object name=IX_INWPOLDTLS_SYSCOMPANYBRANCH     Cost=7     Cardinality=1199     
            INDEX UNIQUE SCAN     Object owner=ODS     Object name=PK_AVN_D_MASTERPOLICYDETAILS     Cost=0     Cardinality=1     
            TABLE ACCESS BY INDEX ROWID     Object owner=ODS     Object name=AVN_D_MASTERPOLICYDETAILS     Cost=1     Cardinality=1     Bytes=34
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYLOBMAPPING     Cost=741     Cardinality=288498     Bytes=4904466
            VIEW PUSHED PREDICATE     Object owner=ODS          Cost=4     Cardinality=1     Bytes=20
            FILTER                         
            SORT AGGREGATE                    Cardinality=1     Bytes=21
            TABLE ACCESS BY GLOBAL INDEX ROWID     Object owner=ODS     Object name=AVN_F_TRANSACTIONS     Cost=4     Cardinality=1     Bytes=21
            INDEX RANGE SCAN     Object owner=ODS     Object name=PK_AVN_F_TRANSACTIONS     Cost=3     Cardinality=1     
            VIEW PUSHED PREDICATE     Object owner=ODS          Cost=5782     Cardinality=1     Bytes=95
            SORT GROUP BY               Cost=5782     Cardinality=2485     Bytes=216195
            VIEW     Object owner=SYS     Object name=VW_DAG_0     Cost=5781     Cardinality=2485     Bytes=216195
            SORT GROUP BY               Cost=5781     Cardinality=2485     Bytes=278320
            HASH JOIN               Cost=5780     Cardinality=2485     Bytes=278320
            VIEW     Object owner=SYS     Object name=VW_GBC_15     Cost=925     Cardinality=1199     Bytes=73139
            SORT GROUP BY               Cost=925     Cardinality=1199     Bytes=100716
            HASH JOIN               Cost=924     Cardinality=1199     Bytes=100716
            NESTED LOOPS                         
            NESTED LOOPS               Cost=181     Cardinality=1199     Bytes=80333
            TABLE ACCESS BY INDEX ROWID     Object owner=ODS     Object name=INWARDSPOLICYDETAILS     Cost=159     Cardinality=1199     Bytes=39567
            INDEX RANGE SCAN     Object owner=ODS     Object name=IX_INWPOLDTLS_SYSCOMPANYBRANCH     Cost=7     Cardinality=1199     
            INDEX UNIQUE SCAN     Object owner=ODS     Object name=PK_AVN_D_MASTERPOLICYDETAILS     Cost=0     Cardinality=1     
            TABLE ACCESS BY INDEX ROWID     Object owner=ODS     Object name=AVN_D_MASTERPOLICYDETAILS     Cost=1     Cardinality=1     Bytes=34
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYLOBMAPPING     Cost=741     Cardinality=288498     Bytes=4904466
            VIEW     Object owner=SYS     Object name=VW_GBF_16     Cost=4854     Cardinality=75507     Bytes=3850857
            SORT GROUP BY               Cost=4854     Cardinality=75507     Bytes=2340717
            VIEW     Object owner=ODS          Cost=4207     Cardinality=75507     Bytes=2340717
            SORT GROUP BY               Cost=4207     Cardinality=75507     Bytes=1585647
            PARTITION HASH ALL               Cost=3713     Cardinality=75936     Bytes=1594656
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_F_TRANSACTIONS     Cost=3713     Cardinality=75936     Bytes=1594656


            Explain Plan(Only Query):_

            SELECT STATEMENT, GOAL = ALL_ROWS               Cost=62783     Cardinality=89964     Bytes=17632944
            HASH JOIN OUTER               Cost=62783     Cardinality=89964     Bytes=17632944
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_WRK_F_RENEWAL_TRANS_1ST     Cost=138     Cardinality=89964     Bytes=2878848
            VIEW     Object owner=ODS          Cost=60556     Cardinality=227882     Bytes=37372648
            HASH GROUP BY               Cost=60556     Cardinality=227882     Bytes=26434312
            VIEW     Object owner=SYS     Object name=VW_DAG_1     Cost=54600     Cardinality=227882     Bytes=26434312
            HASH GROUP BY               Cost=54600     Cardinality=227882     Bytes=36005356
            HASH JOIN OUTER               Cost=46664     Cardinality=227882     Bytes=36005356
            VIEW     Object owner=SYS          Cost=18270     Cardinality=227882     Bytes=16635386
            HASH JOIN               Cost=18270     Cardinality=227882     Bytes=32587126
            HASH JOIN               Cost=12147     Cardinality=34667     Bytes=2912028
            HASH JOIN               Cost=10076     Cardinality=34667     Bytes=2322689
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_D_MASTERPOLICYDETAILS     Cost=137     Cardinality=34667     Bytes=1178678
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYDETAILS     Cost=9934     Cardinality=820724     Bytes=27083892
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYLOBMAPPING     Cost=741     Cardinality=866377     Bytes=14728409
            VIEW     Object owner=ODS          Cost=5195     Cardinality=227882     Bytes=13445038
            HASH GROUP BY               Cost=5195     Cardinality=227882     Bytes=4785522
            PARTITION HASH ALL               Cost=3717     Cardinality=227882     Bytes=4785522
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_F_TRANSACTIONS     Cost=3717     Cardinality=227882     Bytes=4785522
            VIEW     Object owner=ODS          Cost=26427     Cardinality=227882     Bytes=19369970
            HASH GROUP BY               Cost=26427     Cardinality=227882     Bytes=18686324
            VIEW     Object owner=SYS     Object name=VW_DAG_0     Cost=26427     Cardinality=227882     Bytes=18686324
            HASH GROUP BY               Cost=26427     Cardinality=227882     Bytes=25294902
            HASH JOIN               Cost=20687     Cardinality=227882     Bytes=25294902
            VIEW     Object owner=SYS     Object name=VW_GBC_15     Cost=12826     Cardinality=34667     Bytes=2080020
            HASH GROUP BY               Cost=12826     Cardinality=34667     Bytes=2912028
            HASH JOIN               Cost=12147     Cardinality=34667     Bytes=2912028
            HASH JOIN               Cost=10076     Cardinality=34667     Bytes=2322689
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_D_MASTERPOLICYDETAILS     Cost=137     Cardinality=34667     Bytes=1178678
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYDETAILS     Cost=9934     Cardinality=820724     Bytes=27083892
            TABLE ACCESS FULL     Object owner=ODS     Object name=INWARDSPOLICYLOBMAPPING     Cost=741     Cardinality=866377     Bytes=14728409
            VIEW     Object owner=SYS     Object name=VW_GBF_16     Cost=7059     Cardinality=227882     Bytes=11621982
            HASH GROUP BY               Cost=7059     Cardinality=227882     Bytes=6836460
            VIEW     Object owner=ODS          Cost=5195     Cardinality=227882     Bytes=6836460
            HASH GROUP BY               Cost=5195     Cardinality=227882     Bytes=4785522
            PARTITION HASH ALL               Cost=3717     Cardinality=227882     Bytes=4785522
            TABLE ACCESS FULL     Object owner=ODS     Object name=AVN_F_TRANSACTIONS     Cost=3717     Cardinality=227882     Bytes=4785522
            • 3. Re: Oracle 11g Migration performance issue
              sb92075
              How do I ask a question on the forums?
              SQL and PL/SQL FAQ



              scroll down to #9 to learn how to use
               tags!