Re: autotrace and live plan
From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Wed, 2 Sep 2009 18:44:34 -0500
Message-Id: <377D6644-04F5-4223-BBE8-8E6BD27F1465_at_enkitec.com>
I think Dave's right - definitely does an explain plan. Here's another "proof".
SQL> set autotrace on
SQL> select * from kso1 where rownum < 5;
Plan hash value: 4162151349
Date: Wed, 2 Sep 2009 18:44:34 -0500
Message-Id: <377D6644-04F5-4223-BBE8-8E6BD27F1465_at_enkitec.com>
I think Dave's right - definitely does an explain plan. Here's another "proof".
SQL> select sql_id,sql_text from v$sql where sql_text like '%kso%';
SQL_ID SQL_TEXT
------------- -----------------------------------------
2svw50fynzdzr select sql_id,sql_text from v$sql where s
ql_text like '%kso%'
SQL> -- only my select from v$sql is in the shared pool with '%kso%'
SQL>
SQL> set autotrace on
SQL> select * from kso1 where rownum < 5;
OBJECT_NAME
DBA_CONS_COLUMNS DBA_CONS_COLUMNS DBA_LOG_GROUP_COLUMNS DBA_LOG_GROUP_COLUMNS
Execution Plan
Plan hash value: 4162151349
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |00:00:01 |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 264 | 3 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| KSO1 | 1548 | 99K| 3 (0)|
Predicate Information (identified by operation id):
1 - filter(ROWNUM<5)
Note
- dynamic sampling used for this statement
Statistics
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> set autotrace off
SQL> select sql_id,sql_text from v$sql where sql_text like '%kso%';
SQL_ID SQL_TEXT
------------- -----------------------------------------
59zr23qasfmqu EXPLAIN PLAN SET STATEMENT_ID='PLUS429496
7295' FOR select * from kso1 where rownum
< 5
crqjjxb4m2su9 select * from kso1 where rownum < 5 2svw50fynzdzr select sql_id,sql_text from v$sql where s
ql_text like '%kso%'
SQL> -- Now I have my statement, the query of v$sql, AND THE EXPLAIN PLAN This is from an 11.1.0.7 database but it behaves the same way on 10.2. There may be some cases where it does something different, but I haven't seen one.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Sep 2, 2009, at 5:54 PM, Herring Dave - dherri wrote:
>
> 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.
> ***************************************************************************--
> http://www.freelists.org/webpage/oracle-l
> *
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 02 2009 - 18:44:34 CDT