Skip to Content
Introduction

If you compare a production environnent with its test system and notice different Oracle execution plans it might be useful to transfer the CBO statistics from one system to another. I will show you how this can be done. As this is not a everyday procedure we will add some backup steps to have a fallback possibility.

 

Procedure Overview

1.      Backup original table stats in the target system

2.      Fetch and export stats in the source system

3.      Import stats in the target system

4.      Switch back or cleanup

 

1. Backup original table stats in the target system

All operations should be done as the SAP schema user. I assume it is SAPSR3 in the following example. I will use table T000 as well. First create a table for the backup stats:

SQL> conn sapsr3
SQL> exec dbms_stats.create_stat_table('SAPSR3','BACKUPSTATS','');

Now we dump the actual stats into BACKUPSTATS:

SQL> exec dbms_stats.export_table_stats 
('SAPR3','T000',NULL,'BACKUPSTATS','T000_STATS',TRUE);

T000_STATS is just an identifier, as it is possible to have multiple table stats in a stat table.

2. Fetch and export the stats in the source system

Getting the stats is pretty much the same as in the step above, but this time executed on the source system:

SQL> conn sapsr3
SQL> exec dbms_stats.create_stat_table('SAPSR3','MYSTATS','');

Dump the stats into MYSTATS:

SQL> exec dbms_stats.export_table_stats 
('SAPR3','T000',NULL,'MY STATS','T000_STATS',TRUE);

Now we have to export the stats table, and move it to the target system. This can be done with BR*Tools as well, I am using exp/imp here.

orasid> exp sapsr3/*** file=tab.dmp log=exp.log
tables=MYSTATS rows=yes indexes=no

Move the tab.dmp to the target system.

 

3. Import the stats in the target system

Import the stats table:

orasid> imp sapr3/*** file=tab.dmp log=imp.log

Use dbms_stats.import_table_stats to overwrite the T000 stats.

SQL> exec dbms_stats.import_table_stats 
('SAPSR3','T000','NULL','MYSTATS','T000_STATS',TRUE,
no_invalidate=>FALSE);

 

4. Switch back or cleanup

If you are not pleased with the result, just re-import the original stats from the BACKUPSTATS table.

To make the changes stats permanent and prevent BR*Connect from overwriting the stats, you will need to lock the table stats generation, also set the set the Activ flag to ignore in transaction DB21.

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('SAPSR3','T000');

To cleanup we simply need to drop the stats tables MYSTATS (on source and target) and BACKUPSTATS.

 

Further Information

Oracle 10g DBMS_STATS

932975 – Oracle statistics for RFC tables

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann
    Hi there,

    nice blog sofar.
    As you may know (or not) all this is already integreated into the BRTOOLS.
    Check SAP Note 1033125 “Managing database statistics with BRSPACE”.

    In general, it should be mentioned that testing based on this kind of fake statistics has to be done very very carefully. There are many performance relevant characteristics that are not bound to the statistics and one will usually want the application run as good as well with more current a.k.a. different  statistics.

    Personally I see this feature more as an option to retain plan stability when doing things like reorganisations etc.

    regards,
    Lars

    (0) 
  2. Bernd Boecker
    Hi,

    it makes perfectly sense to “hack” sometimes…

    once I was faced to suggest if an index would be usefull for a certain SQL statement (incl. Joins, views), sure they had only few records in the dev. database – because it was not allowed to import the productive data into dev. DB.

    Well we could do a shot in the dark and transport the index to production and see what the statement would do.

    But -stop! It was a huge production table wich implied creating a big index for TESTING on production, and there was danger the new index could change the execution plan on existing statements in an unpredictable way.

    What we did was we extracted the statistics from production and “faked” stats in the dev. DB(we used ABAP routines wrapped around the dbms_stats.get_index_stats and set_index_stats procedures instead) – so no Admin was plagued with a “dirty” developer request like that.

    We could test the SQL statement’s execution plan, did several tries on index key order – and voila we could transport a well tested index to production.

    ********************************************
    But, you have to do this only if you have a throurough understanding of the CBO and it’s environment.
    ********************************************

    Bye

    yk

    (0) 

Leave a Reply