Hi,
I'm trying to set autotrace on. I need the explain plan, no statistics, no output to screen. How do I achieve this?
Printable View
Hi,
I'm trying to set autotrace on. I need the explain plan, no statistics, no output to screen. How do I achieve this?
set autotrace traceonly explain
Code: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)
set autotrace traceonly explain
Gives you no output and stats but only explain plan..
ST2003
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?
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:
and then run a query over your plan_table.Code:explain plan set statement_id = 'foo1' for
select * from xyz
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.Quote:
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.
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.
I stand corrected. I was thinking it worked like TRACEONLY, didn't realize it was different.