Copy Stats from Production to QA [message #50589] |
Fri, 22 March 2002 12:51 |
rajesh
Messages: 173 Registered: November 1998
|
Senior Member |
|
|
We are using dbms_stats to compute statistics.
We are planning to gather the stats at the Production DB level and
then import the stats into our QA environment, so that the execution
plan of all the queries should be same between Production and QA.
We have tested this on two tables to startwith. We computed the stats
for these two tables on Production DB using the following.
exec dbms_stats.gather_table_stats('ABC,'TAB1');
exec dbms_stats.gather_table_stats('ABC','TAB2');
where ABC is the name of schema and TAB1 and TAB2 are the tables.
Now we updated the stats on the QA DB to make it look like the
Production Stats
and we are running the same query against these PROD and QA database
and are getting two different explain plan, whereas "technically" we
should get the same explain plan.
I would really appreciate if someone could HELP me figure out what is
wrong.
Thanks in Advance
Rajesh
|
|
|
Re: Copy Stats from Production to QA [message #50590 is a reply to message #50589] |
Fri, 22 March 2002 13:49 |
saleem
Messages: 65 Registered: January 2002
|
Member |
|
|
you have to run the stats generation for each db through on each db. using an update statement to move teh stats from one instance to another is just not right. i've never heard of taht being done before, and am strongly suspicious of it being remotely close to replicatign what's going on the db. when the stats are generated all kinds of tables in the background are most likely changed by oracle. just run the dbms_stats package again on the clone db and then see what's going on. and also rebuild your indexes.
|
|
|