Re: copy export plan from one database to another

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 04 Mar 2004 07:36:46 -0700
Message-Id: <6.0.3.0.2.20040304072739.02b59c30@pop.centrexcc.com>

copy the statistics from the dev/tst database to production:

on dev/tst run dbms_stats.export_schema_stats: you need to create the stattab table if you have not already done so supply a value for statid or else it can take hours
export the stattab table on dev/tst using the export utility
import the stattab table into production using the import utility
selectively import the statistics for tables used in sql with performance problems since the latest statistics gathering: dbms_stats.import_table_stats(... statid=> ... cascade=>true)

In future have the dbms_stats.gather procedure save the current statistics to the stattab table before replacing them with the new statistics. Then you do not need steps 1-3 above.
Remember - the first mantra for a dba should be "always know how to back out what you are doing".

At 04:45 PM 3/3/2004, you wrote:

>Hi there,
>
>We are in a bit of a panic right now, and I am busily searching the manuals,
>
>But is it possible to copy the explain plan that looks good in your
>dev/test database to the production
>database and force your production database to use that explain plan ??
>
>I will figure out what went wrong with the production plan once the panic
>is over.
>
>Thanks
>
>Darren
>
>--------------------------------------------------------------------------------------------------------------------------
>
>Darren Browett P.Eng This
>message was transmitted
>Data Administrator using 100%
>recycled electrons
>Information and Communication Technology
>City of Coquitlam
>P:(604)927 - 3614
>E:dbrowett_at_coquitlam.ca
>---------------------------------------------------------------------------------------------------------------------------
>
>

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 08:44:52 CST