RE: autotrace and live plan

From: Herring Dave - dherri <>
Date: Wed, 2 Sep 2009 17:54:13 -0500
Message-ID: <>

Kyle, Tanel Poder had a session at Hotsos in '06 that showed how you could see what autotrace was doing:

oradebug setmypid
oradebug event 10046 trace name context forever, level 4; set autotrace on

I tried the above and got:

select /*+ opt_param('parallel_execution_enabled',                                    'false') EXEC_FROM_DBMS_XPLAN */ * from PLAN_TABLE where 1=0

SELECT /*+ opt_param('parallel_execution_enabled', 'false') */                          /* EXEC_FROM_DBMS_XPLAN */ id, position, level - 1 depth, operation, options, object_name, cardinality, bytes, temp_space, cost, io_ cost, cpu_cost , null , partition_start, partition_stop, object_node, other_tag, distribution, NULL, access_predicates, filter_predicates , other, null, null , remarks, null, null, null, null, null, null, null,

                            null, null, null, null, null,
                            null, null, null, null from PLAN_TABLE start  with id = 0
                        and  timestamp >=
                                (select max(timestamp)
                                 from PLAN_TABLE where id=0  and statement_id = :stmt_id and  nvl(statement_id, ' ')
                                                     not like 'SYS_LE%')
                                   and  nvl(statement_id, ' ')
                                                     not like 'SYS_LE%' and statement_id = :stmt_id connect by (prior id = parent_id
                                  and prior nvl(statement_id, ' ') =
                                            nvl(statement_id, ' ')
                                  and prior timestamp <= timestamp)
                                  or (prior nvl(object_name, ' ')
                                                     like 'SYS_LE%'
                                  and  prior nvl(object_name, ' ') =
                                             nvl(statement_id, ' ')
                                  and id = 0 and prior timestamp <= timestamp)
                      order siblings by id

Assuming I've read the tracefile correctly, autotrace is using a form of DBMS_XPLAN, not getting the "live" plan as you hoped.

Of course I'm sure Tanel has a way of changing this, as in that session I remember he showed how you could "adjust" autotrace to display different default statistics. ☺

David C. Herring  | DBA, Acxiom Database Services

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. |

From: [] On Behalf Of kyle Hailey Sent: Tuesday, September 01, 2009 12:58 PM To: ORACLE-L
Subject: autotrace and live plan

Does autotrace ever show the live plan, ie the plan from v$sql_plan? I traced autotrace on 9iR2 and 10gR2 and in both cases they use "explain plan" to generate the explain plan. I guess that fine since theoretically within my same session the calculated execution plan, ie "explain plan", would have to be the same as the  actual executed plan, but with v$sql_plan in place, I somehow find it disconcerting that the plan isn't extracted from v$sql_plan.


The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

i0zX+n{+i^ Received on Wed Sep 02 2009 - 17:54:13 CDT

Original text of this message