Re: What is the best method to collect EXPLAIN PLAN for a lot of SQL statement

dominica_at_gmail.com wrote:
> Hi All,
>
>
> I am curious how other expert DBA do.
>
> What is the best method to collect EXPLAIN PLAN data?

DBMS_XPLAN built-in package.

> Let me explain what I am going to archieve.
>
> I have this TEST ORACLE UPGRADE PROJECT from 8.1.7.4 to 10g (or 9.2).
>
> I am planning to do these on a CLONE-TEST-DATABASE (has same data like
> prod):
> 1) dbms_stat(like analysis all the tables)
> 2) I find out a bunch of highly used SQL statements from
> V$SQLAREA ( I think I could find them).
> 3)
> do this:
> explain plan for: Select * from TABLE1 where BLAH .. BLAH..
>
> Each plan will be stored inside PLAN_TABLE table.

I am at a loss to understand why you are doing this at all. If the purpose is to generate identical plans you shouldn't want to. If the purpose is to compare the plans it is meaningless. Take the SQL statements, strip out any hints, run them and tune them per the many good tuning guides such as those written by the members of the Oak Table.

Anything else is a waste of time unless you bill by the hour.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)