DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Set autotrace

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Set autotrace

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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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)
    Last edited by marist89; 10-22-2003 at 02:39 PM.
    Jeff Hunter

  3. #3
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    set autotrace traceonly explain

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

    ST2003

  4. #4
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Hi

    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?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    Code:
    explain plan set statement_id = 'foo1' for
    select * from xyz
    and then run a query over your plan_table.
    Jeff Hunter

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Last edited by jmodic; 10-23-2003 at 06:30 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I stand corrected. I was thinking it worked like TRACEONLY, didn't realize it was different.
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width