RE: autotrace and live plan
Date: Wed, 2 Sep 2009 17:54:13 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3606962F46_at_CWYMSX04.Corp.Acxiom.net>
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
SELECT * FROM dual;
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. | www.acxiom.com
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.
BestÂ
Kyle
http://perfvision.com
http://oraclemonitor.com
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.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^ Received on Wed Sep 02 2009 - 17:54:13 CDT