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

Hi G,

Thank you for your information.
I did not know you could do:

 set statement_id = 'statement_1'
This is absolutely very useful for me.

Now, my approach is:

1. I create a table DOM_SQL to store all the SQL-statements-need to be run, and a statement_id.
2. I will use KSH and SQL statement and sqlplus to archive the following:

Do:
 explain plan set statement_id = 'statement_id-from DOM-SQL' into plan_table for
each SQLSTATEMENT in DOM_SQL table.

3)
In my plan table, now I have a statement_id match to the real statement that I am interested in.

I re-read the utlxpls.sql script to look for the STATEMENT_ID that I am interested in.

So I could make my own report now.
I could make a report just will look at DOM-SQL table, and report all the plan for that table and could dump out the SQL statement too.

Thank you.

Dominica Received on Thu May 05 2005 - 18:32:54 CDT