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

60次阅读

没有评论

共计 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

—————————————————————————-

—————————————————————————-

| 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

—————————————————————————-

—————————————————————————-

| 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

——————————————————————————–

——————————————————————————–

| 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

。。。省略部分

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

——————————————————————————————

——————————————————————————————

| 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

。。。。。省略部分

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

————————————————————————————————————-

————————————————————————————————————-

| 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

——————————————————————————————

——————————————————————————————

| 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

————————————————————————–

————————————————————————–

| 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

。。。省略部分

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

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

正文完

发表至：数据库

2023-08-01

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

phpmyadmin出现无法连接提示的解决方法

怎么将Mysql数据导入到MongoDB

如何使用MySQL自动化运维工具goinception

jsp如何链接mysql数据库

mysql误删除数据怎么恢复