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

Note that there is a slight difference between producing an explain plan as in ...
explain plan set statement_id = 'STMT_1' into plan_table for
select ...
and collecting information using a trace (the first consuming less resource since it is only an estimate/and could turn out to be totaly inacurate, and the second method is based on facts, but requires you to actually run the statement).

Using the projected plan method, you can label your statements, and later produce the plan with something like ... select lpad( ' ', 2*(level-1))||OPERATION||' '||OPTIONS||' '||OBJECT_NAME||' '||DECODE(ID, 0, 'COST = '||POSITION) "QUERY PLAN"   from plan_table
 start with id = 0 and statement_id = 'STMT_1' connect by prior id = parent_id and statement_id = 'STMT_1';

Another ideal would be
In Oracle 10, you can "set autotrace on" in SQL Plus. Combining this with the spool command would provide you with one file with all the plans.

Another approach would be to turn tracing on your session and run all your SQL statement.
ALTER SESSION SET SQL_TRACE = true;
Select, Update, Delete ...
Then use tkprof to convert the trace file created on the database server to review the plan and cost generated for all statements. Received on Wed May 04 2005 - 20:58:28 CDT