Home » RDBMS Server » Server Administration » Copy Stats from Production to QA
Copy Stats from Production to QA [message #50589] Fri, 22 March 2002 12:51 Go to next message
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 Go to previous message
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.
Previous Topic: Re: Most Urgent - Import Database from Sybase to Ms-Sql Server 7.0
Next Topic: Process for Rollback Segment.
Goto Forum:
  


Current Time: Tue Dec 24 22:51:08 CST 2024