Set autotrace

[pascal01]

Hi,
I'm trying to set autotrace on. I need the explain plan, no statistics, no output to screen. How do I achieve this?

[marist89]

set autotrace traceonly explain

SQL> select * from xyz;

         X Y         Z
---------- --------- --------------------
         1 22-OCT-03 foo

SQL> set autotrace traceonly explain
SQL> select * from xyz
  2  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=34)
   1    0   TABLE ACCESS (FULL) OF 'XYZ' (Cost=1 Card=1 Bytes=34)

[st2003]

set autotrace traceonly explain

Gives you no output and stats but only explain plan..

ST2003

[pascal01]

I set autotrace traceonly explain and its still running. I had to kill the session... I believe its still collecting stats?? I only need the explain plan?

[marist89]

No, it's not collecting stats. It's actually running your query, just not displaying results. That's the way AUTOTRACE works. If you just want the explain plan, use:

explain plan set statement_id = 'foo1' for
select * from xyz

and then run a query over your plan_table.

[jmodic]

Originally posted by marist89
No, it's not collecting stats. It's actually running your query, just not displaying results. That's the way AUTOTRACE works.

No, that's not the way it works. SET AUTOTRACE TRACEONLY EXPLAIN does never actualy run the query, it only parses it, without executing. It doesn't have to gather any statistics or anything, just parsing the query.

It is SET AUTOTRACE TRACEONLY STATISTICS that indeed has to run the query (without displaying the query resultset) in order to be able to display you the statistics of the query.

[marist89]

I stand corrected. I was thinking it worked like TRACEONLY, didn't realize it was different.