oracle中查看执行计划的常用方法

共计 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 小编将为大家输出更多高质量的实用文章！