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

In Oracle 10g:

1. Enable trace using Client Identifier.
2. Run all your sqls.
3. Disable trace.
4. Extract your trace file (containing only your SQLs) using trcsess command.
5. Either tkprof the trace file or use Trace Analyzer (see metalink) which shows plans, binds, waits, all indexes, and lot more.

HTH,
Uday

Niall Litchfield wrote:

>It does. Mea Culpa
>
>Niall
>DA Morgan wrote:
>
>
>>Niall Litchfield wrote:
>>
>>
>>
>>><dominica_at_gmail.com> wrote in message
>>>news:1115256129.754966.230680_at_o13g2000cwo.googlegroups.com...
>>>In 9i there is a new view V$SQLPLAN which will contain the plans
>>>
>>>
>for your
>
>
>>>statements. I'd recommend that you use that for the upgraded db.
>>>
>>>For the 8i part of it I guess you'd need to write some sort of
>>>
>>>
>script to
>
>
>>>populate plan_table with tyhe plan for each statement (generating a
>>>
>>>
>new
>
>
>>>statement id for each).
>>>
>>>I'd suggest not bothering with comparing plans until you have
>>>
>>>
>identified
>
>
>>>those statements that perform poorly after the upgrade.
>>>
>>>HTH
>>>
>>>
>>In 10g that is v$sql_plan. I presume it also has the underscore
>>in 9i.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)
>>
>>
>
>
>
Received on Wed May 11 2005 - 17:10:16 CDT