9 Replies Latest reply: Jan 13, 2014 9:55 AM by Rakesh RSS

Performance Issue after database migration

Rakesh Newbie
Currently Being Moderated

Hi,

 

Recently we have migrated one of database from Sun OS to Linux.We are having issues with the some of the query performance.

We did a full export and full import to migrate the database. below is teh database, os version of bothe database.

 

With full database export the database should have been copied but i see both the database are using different plans. Any suggestins on this would be helpful.

 

Current database ;

oracle version 9.2.0.7.0

OS version -solaris 5.9

 

query plan:

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

 

---------------------------------------------------------------------------------------

| Id  | Operation                       |  Name               | Rows  | Bytes | Cost  |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                     |     1 |    40 | 82093 |

|   1 |  SORT AGGREGATE                 |                     |     1 |    40 |       |

|   2 |   NESTED LOOPS OUTER            |                     |   819K|    31M| 82093 |

|   3 |    NESTED LOOPS OUTER           |                     |   819K|    25M| 82092 |

|   4 |     NESTED LOOPS                |                     |   819K|    20M| 82091 |

|   5 |      INDEX FAST FULL SCAN       | UK_U_SUPPLIER_PART  |   819K|  5605K|    91 |

|   6 |      TABLE ACCESS BY INDEX ROWID| S_ROT_CLASS         |     1 |    19 |     1 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

|*  7 |       INDEX UNIQUE SCAN         | UK_S_ROT_CLASS1     |     1 |       |       |

|*  8 |     INDEX UNIQUE SCAN           | UK_S_USR_CLASS      |     1 |     7 |       |

|*  9 |    INDEX UNIQUE SCAN            | UK_S_USR_CLASS      |     1 |     7 |       |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   7 - access("SUP"."OBJ_ID"="ROT"."OBJ_ID")

   8 - access("ROT"."S_ROT_CREATE_USR_ID"="USR1"."OBJ_ID"(+))

   9 - access("ROT"."S_ROT_USR_ID"="USR2"."OBJ_ID"(+))

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

 

Note: cpu costing is off

 

 

 

New database :

oracle version 9.2.0.7.0

OS version RHEL 4.

 

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

 

-----------------------------------------------------------------------------

| Id  | Operation             |  Name               | Rows  | Bytes | Cost  |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                     |     1 |    40 |   101K|

|   1 |  SORT AGGREGATE       |                     |     1 |    40 |       |

|   2 |   NESTED LOOPS        |                     |  1769K|    67M|   101K|

|   3 |    NESTED LOOPS OUTER |                     |   354M|    10G|   101K|

|   4 |     NESTED LOOPS OUTER|                     |   354M|  8795M|   101K|

|   5 |      TABLE ACCESS FULL| S_ROT_CLASS         |   354M|  6427M|   101K|

|*  6 |      INDEX UNIQUE SCAN| UK_S_USR_CLASS      |     1 |     7 |       |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

|*  7 |     INDEX UNIQUE SCAN | UK_S_USR_CLASS      |     1 |     7 |       |

|*  8 |    INDEX UNIQUE SCAN  | UK_U_SUPPLIER_PART  |     1 |     7 |       |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   6 - access("ROT"."S_ROT_CREATE_USR_ID"="USR1"."OBJ_ID"(+))

   7 - access("ROT"."S_ROT_USR_ID"="USR2"."OBJ_ID"(+))

   8 - access("SUP"."OBJ_ID"="ROT"."OBJ_ID")

 

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

Note: cpu costing is off

  • 1. Re: Performance Issue after database migration
    Hoek Guru
    Currently Being Moderated

    oracle version 9.2.0.7.0? Why a desupported version?

     

    Anyway:

    Did you gather fresh table stats?

  • 2. Re: Performance Issue after database migration
    Rakesh Newbie
    Currently Being Moderated

    no we didn't gather stats after the import. So i am assuming we are having same stats that of source.

  • 3. Re: Performance Issue after database migration
    Rakesh Newbie
    Currently Being Moderated

    This database is for a legacy application which doesn't support any higher oracle version.

  • 4. Re: Performance Issue after database migration
    JohnWatson Guru
    Currently Being Moderated

    You will get people telling you that 9i is de-supported, which is not true. Sustaining support continues indefinitely, so use it: download the terminal patchset, 9.2.0.8, and all the subsequent CPUs, and apply them. There is little point in trying to tune 9.2.0.7. Then analyze the database: tables and indexes, using options to build histograms and 100% sample size (you do not have everything setup by default in 9i). Gather system statistics for  few hours, your exec plan shows that you have not done this.

    Then run your query again: the problem may be fixed.

     

    Message was edited by: JohnWatson

    One more thing:

    set optinizer_dynamic_sampling=4

    this may compensate for your bad statistics.

  • 5. Re: Performance Issue after database migration
    Hoek Guru
    Currently Being Moderated

    You're on a different OS, so, as John already pointed out as well, you'll need fresh stats and upgrade your 9i to the latest version./.

  • 6. Re: Performance Issue after database migration
    Rakesh Newbie
    Currently Being Moderated

    i did a gather stat. But it didn't help. What other possible steps can i take.

     

    Below is then plan after the gather stat.

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    ----------------------------------------------------------------------------------------

    | Id  | Operation                |  Name               | Rows  | Bytes |TempSpc| Cost  |

    ----------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT         |                     |     1 |    40 |       |   453K|

    |   1 |  SORT AGGREGATE          |                     |     1 |    40 |       |       |

    |   2 |   NESTED LOOPS OUTER     |                     |  1727K|    65M|       |   453K|

    |   3 |    NESTED LOOPS OUTER    |                     |  1727K|    54M|       |   453K|

    |*  4 |     HASH JOIN            |                     |  1727K|    42M|    14M|   453K|

    |   5 |      INDEX FAST FULL SCAN| UK_U_SUPPLIER_PART  |   819K|  5603K|       |   113 |

    |   6 |      TABLE ACCESS FULL   | S_ROT_CLASS         |   354M|  6431M|       |   183K|

     

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |*  7 |     INDEX UNIQUE SCAN    | UK_S_USR_CLASS      |     1 |     7 |       |       |

    |*  8 |    INDEX UNIQUE SCAN     | UK_S_USR_CLASS      |     1 |     7 |       |       |

    ----------------------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

     

       4 - access("SUP"."OBJ_ID"="ROT"."OBJ_ID")

       7 - access("ROT"."S_ROT_CREATE_USR_ID"="USR1"."OBJ_ID"(+))

       8 - access("ROT"."S_ROT_USR_ID"="USR2"."OBJ_ID"(+))

     

     

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Note: cpu costing is off

     

    23 rows selected.

  • 7. Re: Performance Issue after database migration
    JohnWatson Guru
    Currently Being Moderated

    You haven't gathered any system stats, have you. And how did you gather object stats? The way I told you to, or just with defaults? And have you applied the 9.2.0.8 patchset yet?

  • 8. Re: Performance Issue after database migration
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    After you import the data, it is very likely, almost certain, that both table and index statistics would change (and we don't know if the statistics on the source system were "recent" or old).  This is because the number of table blocks might reduce, the clustering_factor might change etc.  So it is quite possible for execution plans to change as well.

     

     

    Hemant K Chitale

  • 9. Re: Performance Issue after database migration
    Rakesh Newbie
    Currently Being Moderated

    Hi John,

     

    Initially i didn't use the option to build histograms.used the below parameters.

    exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 100,cascade => TRUE,degree => 4)

     

    Now I collected the database stat and system stat  using below parameters.

     

    exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE,degree => 4)

    exec DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode=>'INTERVAL',interval=>10)

     

    please let me know if i am doing the correct way as you expect.

Legend

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