Re: Where to get plantable information about runtime queries!?!

Hi John,
Enable the SqL_Trace at the session level (ALTER SESSION SET SQL_TRACE=TRUE; or
dbms_system.set_sql_trace_in_session('SID','SERIAL#',TRUE); Run the Program;
Disable the Trace ( Replace 'TRUE' wth 'FALSE' in those statements);

This will generate a Trace file in $USER_DUMP_DEST as specified in u'r Init or config.ora files.

Then do a
tkprof <trace_file> <output file> explain=user/password sys=no

which will generate detailed report about all the Queries,Cpu consumed,Execuion path,Logical and Physical reads etc..

hope this helps

-Thiru

John Vernes wrote:

> Hello,
>
> we have a lot of programs working with pl/sql. In some programs
> we would like to save the query, together with the way the
> query accesses it's data. So we want to store the plan table
> for the query.
>
> I know, we can run the query and store the information in the
> plan_table using same stategy as @utlxplan.sql
> But we don't want to do that. Oracle stores the plan table
> somewhere in it's data dictionary.
>
> Is there anyone who knows where I can find this information???
> It would be very usefull to me.
>
> Thanx in advance.
>
> -John
Received on Mon Aug 23 1999 - 14:53:23 CDT