Re: Explain plan dilemma

From: Esteen R. Collins <ercollin_at_ix.netcom.com>
Date: 1995/07/15
Message-ID: <3u7og8$e0e_at_ixnews4.ix.netcom.com>#1/1


In <markjoDBqp9q.5AB_at_netcom.com> markjo_at_netcom.com (Mark Johnson) writes:
>
>Hello,
>
>select lpad(' ',2*level)||operation||' '||options||' '||object_name
>from plan_table
>where statement_id = '1'
>connect by prior id = parent_id
> and statement_id = '1'
>start with id = 1
>
> What is the problem here. This works fine if there are only
>statements with statements_ids of '1' in the plan_table. Otherwise it
 

>returns too many. The output looks something like the following.
>
> INDEX UNIQUE SCAN TABLES_UK1
> [add 16 of the above line here]
> INDEX UNIQUE SCAN TABLES_UK1
>TABLE ACCESS BY ROWID TABLES (1)
> INDEX UNIQUE SCAN TABLES_UK1
> INDEX UNIQUE SCAN TABLES_UK1
>
>Thanks
>
Instead of writing your own selects from the plan table. You might want to try an easier way. Turn SQL trace on for your session and then run TKPROF with the explain option. It produces the nice output. Below is an example of how you might do it:

SQL> alter session set sql_trace true;

SQL> Select .......

SQL> exit;

Find your trace file where ever your ora_nnnn.trc files may be located. Execute TKPROF statement.

Example:

tkprof ora_nnnn.trc <output file> explain=<user id/passwd who has a plan_table> sort=<any sort parameters>

nnnn - represents the process id of the SQL*Plus session which processed the SQL statment.

The above examples make an assumtion that you are on a UNIX platform. Refer to you SQL Reference manuals for assistance on TKPROF. If on UNIX just type tkprof at the prompt, the parameters should be listed for you. Received on Sat Jul 15 1995 - 00:00:00 CEST

Original text of this message