檢視SQL執行計劃的方式

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

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.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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

[oracle@rh6 ~]$ 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

......

[oracle@rh6 ~]$ 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

[oracle@rh6 ~]$ 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

[oracle@rh6 ~]$ 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

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

檢視SQL的執行計劃方法

相關文章