Re: Copy Stats from Production to QA

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sat, 23 Mar 2002 09:52:55 +1100
Message-ID: <a7gclq$8ov$1@lust.ihug.co.nz>

You probably already know this, but it's not just statistics that govern the optimizer's behaviour. Are the init.ora's of the two databases *identical*? Specifically, something like db_file_multiblock_read_count? Also, the optimizer goal (or mode) needs to be identical. And you should avoid CHOOSE. Did you collect statistics on the indexes, too?

To force the issue, you might want to resort to stored outlines.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Rajesh" <rk_oracle_at_yahoo.com> wrote in message
news:12e37fd7.0203221442.2ce28335_at_posting.google.com...


> DB : 8172

> OS : HP_UX 11

>

> 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

Received on Fri Mar 22 2002 - 16:52:55 CST