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 (9.2.0.6) 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.
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.
posted by Vidya Balasubramanian at
12:26 PM
4 Comments:
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
Pete Scott, 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
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
Pete Scott, 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
Mathias, at 7:52 AM
Post a Comment
<< Home