Re: trace SQL

Papadomichelakis John <michelos_at_spark.net.gr> wrote:

>Hi.
>
>
>How can I get some information of the execution plan
>of a SQL statement?
>
>I want to check if and how my queries take advantage of
>indexes, keys, etc...

First, run the script \oracle\rdbms7x\admin\utlxplan.sql in your schema. This creates the table plan_table which is used with the explain plan command.

Second, run the following command:

explain plan set statement_id = 'test' for (sql goes here)

ie. explain plan set statement_id = 'test' for select * from cat;

Third, run this sql to see the execution plan:

SET heading on
SET pagesize 70

SELECT lpad(' ',2*level)||Operation||' '||Options|| ' '||object_name Q_Plan
FROM plan_table
WHERE statement_id = 'test'
connect BY prior id=parent_id AND statement_id = 'test' start WITH id = 0;

DELETE FROM plan_table WHERE statement_id = 'test';

Hope this helps,
-Darren
Darren Mallette www.magi.com/~dooley Received on Sat Mar 15 1997 - 00:00:00 CST