共计 9729 个字符，预计需要花费 25 分钟才能阅读完成。
本篇内容介绍了“oracle 中查看执行计划的常用方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
本文介绍了 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 |
。。。省略部分
==》真实的执行计划应该是 INDEX FAST 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 中查看执行计划的常用方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
![Frank#smith[Kfyzeiuqujbpygze,2,5]](https://cravatar.cn/avatar/ebd2642d86a4b40100d29dc76ec89b0d?s=96&d=monsterid&r=r)
