Vidya Bala's Blog

Search My Blog

Add RSS Feed

Friday, April 13, 2007

Transferring System Statistics to a TEST environment

Can we transfer system statistics from a Production environment to a Test environment? Most documentation talk about simulating a workload in your test environment and gather system statistics in test before implementing system statistics in Production. We are on Oracle 9i ( in Production - we have not gathered system statsitics in Production so far. The decision has been made to start gathering System Statistics in Production. Testing this in a test environment prior to implementation seems like a challenge - given that our load test simulation may be very very different from Production.

Is it not possible to Gather System Stats in Production into a Staging Table , export the staging table to the Test environment and then import the Stats into the data dictionary in Test? Even if the above is possible , some key questions still remain while gathering System Stats into a Staging Table in Production -

a)is performance of Production impacted while System Stats is being gathered to a staging table?
b)SQL in the SGA invalidated ? - all documents that I have read so far tell me the answer is "No"
c)Since stats are gathered into a Staging Table , I am assuming no execution plans should change until stats are imported into the data dictionary in Production .

It will be great to know if any one has run into the same issue.


  • the DBMS_STATS package has a group of procedures to store statistics in a specially crafted stats table and to transfer stored stats from the table to the dictionary. I have used this to move stats from one machine to another so that I could reproduce a production bug on a smaller system. I wrote about this on my blog (search for test cases)

    Have a look in the documentation for supplied PL/SQL packages

    By Blogger Pete_S, at 2:26 PM  

  • Pete,

    Thanks for the response. I still had one question - in our case we have no system stats to export. We will have to gather the system stats from Production to a staging table and import that into test. While gathering system stats in Production to a staging table ? will execution plans change in PROD? will performance get impacted in PROD? - I would think the answer would be no since the stats collection is to a staging table.

    your response will be greatly appreciated.

    By Blogger Vidya Balasubramanian, at 3:05 PM  

  • You can store the stats directly in a stats table.

    Create a stats table using DBMS_STATS.CREATE_STAT_TABLE. Then call GATHER_SYSTEM_STATS and supply the newly created stat table name as one of the arguments. You then need to create transfer the contents of the stats table to your dev system - (exp/imp, database link, materialized view will all work). Then apply the stats by using DBMS_STATS.IMPORT_SYSTEM_STATS procedure - you may well need DBA privs to run that one!

    Gathering stats to a table does not change the dictionary stats.

    By Blogger Pete_S, at 7:23 AM  

  • There should be no impact and no plans will be invalidated as the optimizer in production never accesses the data in the staging table.

    By Blogger MD, at 7:52 AM  

Post a Comment

<< Home