本文介绍了oracle中查看执行计划常用的方法。
1、EXPLAIN PLAN命令
2、AUTOTRACE开关
3、DBMS_XPLAN
4、10046事件
1、EXPLAIN PLAN命令
- SQL> explain plan for select * from t where object_id=101151;
-
- Explained.
-
- SQL> select * from table(dbms_xplan.display);
-
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------
- Plan hash value: 470836197
-
- ---------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 158 | 2 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
- ---------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
-
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------
-
- 2 - access("OBJECT_ID"=101151)
==》1、SQL没有被实际执行,所以得到的执行计划可能是不准确的,尤其是包含绑定变量时。
2、PL/SQL Developer中的F5快捷键就是调用的explain plan命令
3、因没被实际执行,能快速返回执行计划
2、AUTOTRACE开关
语法: set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
示例:
- SQL> set autot on
- SQL> select object_name from t where object_id=999;
-
- OBJECT_NAME
- ------------------------------
- INCFIL
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1601196873
-
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 30 | 281 (1)| 00:00:04 |
- |* 1 | TABLE ACCESS FULL| T | 1 | 30 | 281 (1)| 00:00:04 |
- --------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_ID"=999)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 1031 consistent gets
- 1027 physical reads
- 0 redo size
- 533 bytes sent via SQL*Net to client
- 523 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
==> 1.该命令在sqlplus中使用。
2.除了执行计划外,还能看到相关的统计信息。
3.即使实际被执行获取的执行计划也可能不是正确的。
explain plan,AUTOTRACE无法获取真实执行计划示例:
- SQL> var a number;
- SQL> var b number;
- SQL> exec :a :=0;
-
- PL/SQL procedure successfully completed.
-
- SQL> exec :b :=70000;
-
- PL/SQL procedure successfully completed.
- SQL> explain plan for select count(*) from t where object_id between :a and :b;
-
- Explained.
-
- SQL> select * from table(dbms_xplan.display);
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- Plan hash value: 2213771543
-
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | FILTER | | | | | |
- |* 3 | INDEX RANGE SCAN| T_IDX | 180 | 900 | 2 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- 。。。。省略部分
- SQL> set autot traceonly
- SQL> select count(*) from t where object_id between :a and :b;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2213771543
-
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | FILTER | | | | | |
- |* 3 | INDEX RANGE SCAN| T_IDX | 180 | 900 | 2 (0)| 00:00:01 |
- 。。。省略部分
- SQL> select count(*) from t where object_id between :a and :b;
-
- COUNT(*)
- ----------
- 136544
-
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- SQL_ID 9cgwqzzvtw8wc, child number 0
- -------------------------------------
- select count(*) from t where object_id between :a and :b
-
- Plan hash value: 853742775
-
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 45 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- |* 2 | FILTER | | | | | |
- |* 3 | INDEX FAST FULL SCAN| T_IDX | 50561 | 246K| 45 (0)| 00:00:01 |
3、DBMS_XPLAN
DBMS_XPLANB包的常用子程序为:
DISPLAY:配合explain plan for 使用
DISPLAY_CURSOR:适用于sqlplus刚刚执行过的sql执行计划,或在存储在shared pool中的执行计划。
DISPLAY_AWR:sql的执行计划从shared pool中aga out后,如果执行计划被采集到awr报告中,那么就可以使用该方法查看执行计划。
示例:
- SQL> select status from t where owner=user;
- VALID
- VALID
- VALID
- 。。。省略部分
-
- 31206 rows selected.
-
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
-
- PLAN_TABLE_OUTPUT
- -----------------------------------------------------------------------------------------
- SQL_ID 7m7b6un3xtss3, child number 0
- -------------------------------------
- select status from t where owner=user
-
- Plan hash value: 47527108
-
- ------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 13 (100)| |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------
-
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
-
- 1 - SEL$1 / T@SEL$1
- 2 - SEL$1 / T@SEL$1
-
- Outline Data
- -------------
-
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
- DB_VERSION('11.2.0.1')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "DESC_T_INX")
- END_OUTLINE_DATA
- */
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))
- filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)
-
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
-
- 1 - "STATUS"[VARCHAR2,7]
- 2 - "T".ROWID[ROWID,10], "T"."SYS_NC00016$"[RAW,46]
-
- 46 rows selected.
==>1.相比AUTOTRACE开关来说看不到相关的统计信息,而且要等到语句执行完成。但获得的执行计划是真实的
2.这里format参数为advanced,相比较于参数all,多了Outline Data这部分的信息输出
3.这里的Rows列值为估计值,要想看到真实值可以将format参数设置为'ALLSTATS LAST'
format参数设置为'ALLSTATS LAST'示例:
- SQL> alter session set statistics_level =all;
-
- Session altered.
-
- SQL> select status from t where owner=user;
- VALID
- VALID
- VALID
- 。。。。。省略部分
-
- 31206 rows selected.
-
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------
- SQL_ID 7m7b6un3xtss3, child number 1
- -------------------------------------
- select status from t where owner=user
-
- Plan hash value: 47527108
-
- -------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
- -------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 31206 |00:00:00.05 | 5555 | 829 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2255 | 31206 |00:00:00.05 | 5555 | 829 |
- |* 2 | INDEX RANGE SCAN | DESC_T_INX | 1 | 70 | 31206 |00:00:00.02 | 2190 | 114 |
- -------------------------------------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))
- filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)
==》和advanced参数相比,少了部分输出,但是能够看到每一步获取的实际记录数。
输入sqlid来查看执行计划示例:
- SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user';
-
- SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
- ---------------------------------------- ------------- ------------- ----------
- select status from t where owner=user 7m7b6un3xtss3 2 3
-
- SQL> select * from table(dbms_xplan.display_cursor('7m7b6un3xtss3',0,'advanced'));
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- SQL_ID 7m7b6un3xtss3, child number 0
- -------------------------------------
- select status from t where owner=user
-
- Plan hash value: 47527108
-
- ------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 13 (100)| |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------
-
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
-
- 1 - SEL$1 / T@SEL$1
- 2 - SEL$1 / T@SEL$1
-
- Outline Data
- -------------
-
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
- DB_VERSION('11.2.0.1')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "DESC_T_INX")
- END_OUTLINE_DATA
- */
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))
- filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)
-
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
-
- 1 - "STATUS"[VARCHAR2,7]
- 2 - "T".ROWID[ROWID,10], "T"."SYS_NC00016$"[RAW,46]
display_awr 示例:
- SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user';
-
- no rows selected
-
- SQL> select * from table(dbms_xplan.display_cursor('7m7b6un3xtss3',0,'advanced'));
-
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------
- SQL_ID: 7m7b6un3xtss3, child number: 0 cannot be found
-
-
- SQL> select * from table(dbms_xplan.display_awr('7m7b6un3xtss3'));
-
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------
- SQL_ID 7m7b6un3xtss3
- --------------------
- select status from t where owner=user
-
- Plan hash value: 47527108
-
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 13 (100)| |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |
- | 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |
-
==>1、相同的执行计划也可以通过@?/rdbms/admin/awrsqrpt 命令获取。
4、10046方式
- SQL> alter session set tracefile_identifier=plan_10046;
-
- Session altered.
-
- SQL> alter session set events '10046 trace name context forever,level 12';
-
- Session altered.
-
- SQL> select status from t where owner=user;
- VALID
- VALID
- VALID
- 。。。省略部分
-
- 31206 rows selected.
-
- SQL>
- SQL> alter session set events '10046 trace name context off';
-
- Session altered.
==>可以根据diagnostic_dest 参数找到plan_10046的文件。
使用tkprof进行输出:
- [ora11@ora12c ~]$ tkprof /ora11_10/ora11/diag/rdbms/ora11/ora11/trace/ora11_ora_26758_PLAN_10046.trc PLAN_10046.trc
-
- TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 15 21:14:49 2017
-
- Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-
-
- SQL ID: 57fcnar0x2buq
- Plan Hash: 47527108
- select status
- from
- t where owner=user
-
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 1 0.00 0.00 11 57 0 0
- Execute 1 0.00 0.00 0 0 0 0
- Fetch 2082 0.13 0.13 1007 5555 0 31206
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 2084 0.14 0.13 1018 5612 0 31206
-
- Misses in library cache during parse: 1
- Optimizer mode: ALL_ROWS
- Parsing user id: SYS
-
- Rows Row Source Operation
- ------- ---------------------------------------------------
- 31206 TABLE ACCESS BY INDEX ROWID T (cr=5555 pr=1007 pw=0 time=76792 us cost=13 size=27060 card=2255)
- 31206 INDEX RANGE SCAN DESC_T_INX (cr=2190 pr=114 pw=0 time=29158 us cost=10 size=0 card=70)(object id 99885)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25923810/viewspace-2144083/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25923810/viewspace-2144083/