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

From: <dominica_at_gmail.com>
Date: 4 May 2005 18:22:09 -0700
Message-ID: <1115256129.754966.230680@o13g2000cwo.googlegroups.com>

Hi All,

   I am curious how other expert DBA do.

What is the best method to collect EXPLAIN PLAN data?

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.

4) My problem is
I don't want to run
@?/rdbms/admin/utlxpls.sql
immediately.

I want to make a script to
do

explain plan for: SQL statement 1
explain plan for: SQL statement 2
explain plan for: SQL statement 3

Since I do have a lot of SQL statements to ran. And then later on , try to print out a report, that show each SQL statement1 and the plan.

Somehow, I find UTLXPLS.sql only show the plan. Then I don't know which SQL statements it map to.

I have to repeat the above step(1) to step(4) after upgrade the server to 10g.

And then print out the plan and compare.

Any ideas will be highly appreciate.

Dominica Received on Wed May 04 2005 - 20:22:09 CDT