Oracle查看SQL执行计划的方式详解程序员

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 / [email protected]$1

  2 – SEL$1 / [email protected]$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 / [email protected]$1

  2 – SEL$1 / [email protected]$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 / [email protected]$1

  2 – SEL$1 / [email protected]$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

 

4、通过10046 事件查看

1)查看当前session:

13:29:52 SYS@ prod>grant alter session to scott;

Grant succeeded.

13:44:31 SCOTT@ prod>alter session set events ‘10046 trace name context forever,level 12’;

Session altered.

 

13:44:53 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

    EMPNO ENAME             SAL     DEPTNO

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

     7788 SCOTT            3000         20

Elapsed: 00:00:00.10

13:45:51 SCOTT@ prod>alter session set events ‘10046 trace name context off’;

Session altered.

Elapsed: 00:00:00.03

 

[[email protected] ~]$ ls -lt /u01/app/oracle/diag/rdbms/prod/prod/trace/|more

total 1256

-rw-r—– 1 oracle oinstall 27801 May 16 13:46 prod_ora_4995.trc

-rw-r—– 1 oracle oinstall   177 May 16 13:46 prod_ora_4995.trm

-rw-r—– 1 oracle oinstall  1122 May 16 13:34 prod_j000_5188.trc

-rw-r—– 1 oracle oinstall    59 May 16 13:34 prod_j000_5188.trm

……

[[email protected] ~]$ tkprof /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_4995.trc /home/oracle/emp_0416.txt sys=no

TKPROF: Release 11.2.0.1.0 – Development on Fri May 16 13:47:41 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

[[email protected] ~]$ cat emp_0416.txt

SQL ID: bqz9ujgnn4jzu

Plan Hash: 2949544139

select empno,ename,sal,deptno

from

emp where empno=7788

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.07       0.09          1         66          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          2          0           1

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

total        4      0.07       0.09          1         68          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84  

Rows     Row Source Operation

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

     1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)

     1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)

Elapsed times include waiting on following events:

 Event waited on                             Times   Max. Wait  Total Waited

 —————————————-   Waited  ———-  ————

 db file sequential read                         1        0.00          0.00

 SQL*Net message to client                       2        0.00          0.00

 SQL*Net message from client                     2       19.13         19.13

********************************************************************************

 

2)查看其它session:

14:12:23 SYS@ prod>select sid,serial#,username from v$session where username is not null;

      SID    SERIAL# USERNAME

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

        1          5 SYS

42          9 SCOTT

6 rows selected.

Elapsed: 00:00:00.10

14:12:47 SYS@ prod>desc dbms_monitor

PROCEDURE SESSION_TRACE_DISABLE

Argument Name                  Type                    In/Out Default?

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

SESSION_ID                     BINARY_INTEGER          IN     DEFAULT

SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT

PROCEDURE SESSION_TRACE_ENABLE

Argument Name                  Type                    In/Out Default?

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

SESSION_ID                     BINARY_INTEGER          IN     DEFAULT

SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT

WAITS                          BOOLEAN                 IN     DEFAULT

BINDS                          BOOLEAN                 IN     DEFAULT

PLAN_STAT                      VARCHAR2                IN     DEFAULT

 

14:13:11 SCOTT@ prod>select sid from v$mystat where rownum=1;

      SID

———-

       42

14:13:41 SYS@ prod>exec dbms_monitor.SESSION_TRACE_ENABLE(42,9,waits=>true,binds=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

 

14:13:25 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7369;

 

    EMPNO ENAME             SAL     DEPTNO

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

     7369 SMITH             800         20

Elapsed: 00:00:00.03

14:14:29 SYS@ prod>exec dbms_monitor.SESSION_TRACE_disable(42,9);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

 

[[email protected] ~]$ cat emp_0416.txt

SQL ID: fyydvbdw2uq6q

Plan Hash: 2949544139

select empno,ename,sal,deptno

from

emp where empno=7369

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          2          0           1

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

total        4      0.00       0.00          0          2          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84  

Rows     Row Source Operation

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

     1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)

     1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)

Elapsed times include waiting on following events:

 Event waited on                             Times   Max. Wait  Total Waited

 —————————————-   Waited  ———-  ————

 SQL*Net message to client                       2        0.00          0.00

 SQL*Net message from client                     1        0.00          0.00

 

5、通过autotrace查看

SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
SET AUTOTRACE TRACEONLY EXPLAIN
–使用SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY时,目标SQL已经被执行过,所以在SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY 的情况下能看到目标SQL
的实际消耗情况。
–使用SET AUTOTRACE TRACEONLY EXPLAIN时,如果执行的是SELECT语句,则该SQL并没有被执行,但如果执行的是DML语句,情况就不一样了,此时的DML语句是会
被ORACLE执行的。
–需要特别说明的是,虽然使用SET AUTOTRACE命令所得到的执行计划可能是不准确的,因为SET AUTOTRACE命令所显示的执行计划都是源自于explain plan 命令。

案例:

 

15:32:11 SYS@ prod>conn scott/tiger

Connected.

15:32:17 SCOTT@ prod>set autotrace on

15:32:21 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

    EMPNO ENAME             SAL     DEPTNO

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

     7788 SCOTT            3000         20

Elapsed: 00:00:00.03

Execution Plan

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

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)

Statistics

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

       168  recursive calls

         0  db block gets

        38  consistent gets

         0  physical reads

         0  redo size

       736  bytes sent via SQL*Net to client

       523  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         5  sorts (memory)

         0  sorts (disk)

         1  rows processed

15:32:26 SCOTT@ prod>set autotrace trace

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

Elapsed: 00:00:00.02

Execution Plan

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

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)

Statistics

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

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       736  bytes sent via SQL*Net to client

       523  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

 

15:34:59 SCOTT@ prod>set autotrace on exp

15:35:04 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

    EMPNO ENAME             SAL     DEPTNO

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

     7788 SCOTT            3000         20

Elapsed: 00:00:00.02

Execution Plan

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

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)

15:35:12 SCOTT@ prod>set autotrace on statis

15:35:20 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

    EMPNO ENAME             SAL     DEPTNO

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

     7788 SCOTT            3000         20

Elapsed: 00:00:00.03

Statistics

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

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       736  bytes sent via SQL*Net to client

       523  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

15:35:26 SCOTT@ prod>

原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/7194.html

(0)
上一篇 2021年7月17日
下一篇 2021年7月17日

相关推荐

发表回复

登录后才能评论