Oracle查看SQL执行计划的方式

小咪咪 2022-07-12 10:45 323阅读 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

\--------------------------------------------------------------------------------------

\--------------------------------------------------------------------------------------

|   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

\--------------------------------------------------------------------------------------

\--------------------------------------------------------------------------------------

|   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

\--------------------------------------------------------------------------------------

\--------------------------------------------------------------------------------------

|   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'));