oracle中查看执行计划的常用方法
本篇内容介绍了“oracle中查看执行计划的常用方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
本文介绍了oracle中查看执行计划常用的方法。
1、EXPLAIN PLAN命令
2、AUTOTRACE开关
3、DBMS_XPLAN
4、10046事件
1、EXPLAIN PLAN命令
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 |
。。。省略部分
==》真实的执行计划应该是INDEXFAST FULL SCAN
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)
“oracle中查看执行计划的常用方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
