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

Thread: Set autotrace

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

    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 03:39 PM.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  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
    218

    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  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 07: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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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