Oracle查看SQL执行计划的方式-Oracle

Oracle 21NN 4个月前 (06-27) 62次浏览 已收录 0个评论 扫描二维码

Oracle查看SQL执行计划的方式。

获取Oracle sql执行计划并查看执行计划,是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式:

基本有以下几种方式

1、通过sql_trace初始化参数

2、通过Autotrace

3、通过explain plan

4、通过dbms_xplan.display_cursor

5、通过dbms_xplan.display_awr

6、通过10046事件

1、通过explain plan 工具

12:24:00 SCOTT@ prod>explain plan for

12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788;

Explained.

Elapsed: 00:00:00.22

12:24:16 SCOTT@ prod>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 – access(“EMPNO”=7788)

14 rows selected.

Elapsed: 00:00:01.14

2、通过DBMS_XPLAN.display_cursor查看

12:52:37 SCOTT@ prod>desc dbms_xplan

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

如果以scott用户访问需要进行授权:

12:31:44 SYS@ prod>select * from dict where upper(table_name)=’V$SESSION’;

TABLE_NAME COMMENTS

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

V$SESSION Synonym for V_$SESSION

Elapsed: 00:00:00.09

12:31:09 SYS@ prod>grant select on V_$SESSION to scott;

Grant succeeded.

Elapsed: 00:00:00.10

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,’advanced’));

PLAN_TABLE_OUTPUT

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

User has no SELECT privilege on V$SQL_PLAN

解决权限不足:

12:42:33 SYS@ prod>grant select any table to scott;

Grant succeeded.

12:43:46 SYS@ prod>show parameter o7

NAME TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY boolean TRUE

12:44:54 SYS@ prod>

案例:dbms_xplan.display_cursor

12:42:45 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

EMPNO ENAME SAL DEPTNO

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

7788 SCOTT 3000 20

Elapsed: 00:00:00.08

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,’all’));

PLAN_TABLE_OUTPUT

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

SQL_ID bqz9ujgnn4jzu, child number 0

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

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 – SEL$1 / EMP@SEL$1

2 – SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

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

2 – access(“EMPNO”=7788)

Column Projection Information (identified by operation id):

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

1 – “EMPNO”[NUMBER,22], “ENAME”[VARCHAR2,10], “SAL”[NUMBER,22],

“DEPTNO”[NUMBER,22]

2 – “EMP”.ROWID[ROWID,10], “EMPNO”[NUMBER,22]

32 rows selected.

Elapsed: 00:00:00.05

案例:

12:49:10 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

EMPNO ENAME SAL DEPTNO

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

7788 SCOTT 3000 20

Elapsed: 00:00:00.00

12:50:06 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,’advanced’));

PLAN_TABLE_OUTPUT

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

SQL_ID bqz9ujgnn4jzu, child number 0

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

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 – SEL$1 / EMP@SEL$1

2 – SEL$1 / EMP@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″ “EMP”@”SEL$1” (“EMP”.”EMPNO”))

END_OUTLINE_DATA

*/

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

2 – access(“EMPNO”=7788)

Column Projection Information (identified by operation id):

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

1 – “EMPNO”[NUMBER,22], “ENAME”[VARCHAR2,10], “SAL”[NUMBER,22],

“DEPTNO”[NUMBER,22]

2 – “EMP”.ROWID[ROWID,10], “EMPNO”[NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.06

12:50:21 SCOTT@ prod>

这种方法在 SQLPLUS中查看刚执行过的 SQLSQLSQL的执行计划 。

— dbms_xplan.display_cursor传入的前两个参数值均为 null,null第三个参数是 “advanced”第三个参 数也可以是 “all”得到的显示结果,少了 “Outline data”部分的内容 。

sql>select sql_text,sql_id,hash_value,child_number from v$sql

2* where sql_text like ‘select empno,ename,sal%’

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

————————————————– ————- ———- ————

select empno,ename,sal,deptno from emp where empno bqz9ujgnn4jzu 3913435130 0

=7788

Elapsed: 00:00:00.04

13:00:25 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(‘bqz9ujgnn4jzu’,0,’advanced’));

PLAN_TABLE_OUTPUT

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

SQL_ID bqz9ujgnn4jzu, child number 0

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

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 – SEL$1 / EMP@SEL$1

2 – SEL$1 / EMP@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″ “EMP”@”SEL$1” (“EMP”.”EMPNO”))

END_OUTLINE_DATA

*/

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

2 – access(“EMPNO”=7788)

Column Projection Information (identified by operation id):

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

1 – “EMPNO”[NUMBER,22], “ENAME”[VARCHAR2,10], “SAL”[NUMBER,22],

“DEPTNO”[NUMBER,22]

2 – “EMP”.ROWID[ROWID,10], “EMPNO”[NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.14

3、通过DBMS_XPLAN.display_awr

使用方法dbms_xplan.display_cursor 能够得到sql执行计划的前提条件是该SQL还在共享池中,而如果执行计划的前提条件是该SQL还在共享池中,而如果执行计划的前提条件是该 还在共享池中,而如果SQLSQLSQL的执行计划已经被刷出共享池,那么只要该SQL的执行计划被ORACLE采集到 AWR Repository中, 就可以用该方法来查看 。

12:24:00 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

13:10:56 SYS@ prod>exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

13:11:37 SYS@ prod>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.31

13:16:28 SYS@ prod>select * from table(dbms_xplan.display_cursor(‘bqz9ujgnn4jzu’,0,’advanced’));

PLAN_TABLE_OUTPUT

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

SQL_ID: bqz9ujgnn4jzu, child number: 0 cannot be found

13:21:53 SYS@ prod>desc dbms_xplan

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT

DB_ID NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

13:30:15 SCOTT@ prod>select * from table(dbms_xplan.display_awr(‘bqz9ujgnn4jzu’));

PLAN_TABLE_OUTPUT

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

SQL_ID bqz9ujgnn4jzu

——————–

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

14 rows selected.

Elapsed: 00:00:00.30


本文:Oracle查看SQL执行计划的方式-Oracle 本文链接:https://www.21nn.cn/shujuku/oracle/49265.html 本站所以图片、文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如有侵权请邮件与我们联系处理。i@ki4.cn
喜欢 (0)
[1353713598@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址