使用 DBMS_XPLAN 查看执行计划

SQL 优化的第一步，是查看 SQL 的执行计划。今天我们来说说查看执行计划的常用的一种方式 ：使用DBMS_XPLAN

如果PLan_table  不存在。可以通过下面脚本创建：
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

1. DISPLAY 函数
SQL> CONN scott/tiger
Connected.
SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> SET LINESIZE 130
SQL> SET PAGESIZE 20
SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                    |         |       |       |            |          |
|   2 |   NESTED LOOPS                   |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

DBMS_XPLAN.DISPLAY 函数可以接受3个可选参数.
table_name 表名，默认是 'PLAN_TABLE'。
statement_id 要显示的ID  默认为空 NULL,表示最近在PLAN_TABLE的执行计划。
format  控制要输出的格式, 默认为 'TYPICAL'.  其他的值有 'BASIC', 'ALL', 'SERIAL'.  'ADVANCED' 。

SQL> EXPLAIN PLAN SET STATEMENT_ID='LS' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','LS','BASIC'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | EMP     |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
------------------------------------------------

12 rows selected.

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','LS','ADVANCED'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
       "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

61 rows selected.

ORACLE 10G 之后，oracle 引入了 DISPLAY_CURSOR 这个函数。DISPLAY_CURSOR函数并不是从PLAN_TABLE 返回记录。而是从游标缓存获取数据。
这些数据来自于V$SQL_PLAN_STATISTICS_ALL, V$SQL 和V$SQL_PLAN。
所以用DISPLAY_CURSOR 看到的执行计划是实际执行过的执行计划，是真实的。

SQL> set linesize 300
SQL> set pagesize 300
SQL> SELECT * FROM   emp e, dept d
  2  WHERE  e.deptno = d.deptno
  3  AND    e.ename  = 'SMITH';


SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  az3q3dcu2tzzx, child number 0
-------------------------------------
SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename
 = 'SMITH'

Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@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")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
       "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],
       "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]


67 rows selected.

还有几个函数包大家自行实验：
 DISPLAY_AWR
 DISPLAY_SQLSET
 DISPLAY_SQL_PLAN_BASELINE
 DISPLAY_PLAN

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1812528/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1812528/