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

"dominica_at_gmail.com" <dominica_at_gmail.com> wrote in news:1115256129.754966.230680_at_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
>
>

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764599577.html Received on Wed May 04 2005 - 21:05:52 CDT