这几天帮一个朋友优化一个后台JOB，发现需要使用UTLXPLAN。自己测试使用，记录如下。
utlxplan是Oracle提供的查看SQL语句执行计划的工具，相对于AUTOTRACE使用UTLXPLAN不需要真实执行完该SQL语句，对于长查询的语句选择使用UTLXPLAN尽快获得执行来分析，使
用UTLXPLAN是基于数据库收集的统计数据，所以此时如果想获得更准确地执行计划，就需要统计数据的精确了，这点要注意。
下面是使用UTLXPLAN的步骤。
1、创建PLAN_TABLE，存储执行计划。
下面是使用UTLXPLAN的步骤。
1、创建PLAN_TABLE，存储执行计划。
SQL> connect /as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
SQL> grant all on sys.plan_table to public;
Grant succeeded.
在11G中plan_table已经创建好了，同时创建了同义词同义词。
SQL> select synonym_name,table_name from dba_synonyms
where synonym_name='PLAN_TABLE';
SQL> select synonym_name,table_name from dba_synonyms
where synonym_name='PLAN_TABLE';
SYNONYM_NAME TABLE_NAME
------------------------------ ------------------
PLAN_TABLE PLAN_TABLE$
------------------------------ ------------------
PLAN_TABLE PLAN_TABLE$
使用UTLXPLAN。
SQL> CONNECT scott/oracle
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';
SQL> CONNECT scott/oracle
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> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3625962092
--------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
4 - access("E"."DEPTNO"="D"."DEPTNO")
18 rows selected.
从执行计划可以看出，访问EMP表使用了全表扫描，但是有一个明显的过滤条件filter("E"."ENAME"='SMITH')，所以在优化该语句时可以考虑在该列创建索引（小表有可能走全表
扫描）。
如果有多个用户执行相同的SQL语句，但是二者的执行计划不同，此时可以设置STATEMENT_ID标示该语句。如下所示。
SQL> explain plan set statement_id='TSH' for
2 select *
3 from emp e ,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';
2 select *
3 from emp e ,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';
Explained.
SQL> set line 120
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','BASIC'));
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','BASIC'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------------
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 |
| 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 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
12 rows selected.
-----------------------------------------------------------------------------------------------
12 rows selected.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','TYPICAL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3625962092
-----------------------------------------------------------------------------------------------
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 |
| 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 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
4 - access("E"."DEPTNO"="D"."DEPTNO")
18 rows selected.
这里表DISPLAY函数接受三个参数。
TABLE_NAME:'PLAN_TABLE'
STATEMENT_ID:默认是NULL，查询最近的一个SQL语句，或者指定一个ID。
FORMAT:控制显示的详细程度,TYPICAL,BASIC,ALL,SERIAL,(advanced 没有记录在文档）。以下是ADVANCED参数的查询结果。
这里表DISPLAY函数接受三个参数。
TABLE_NAME:'PLAN_TABLE'
STATEMENT_ID:默认是NULL，查询最近的一个SQL语句，或者指定一个ID。
FORMAT:控制显示的详细程度,TYPICAL,BASIC,ALL,SERIAL,(advanced 没有记录在文档）。以下是ADVANCED参数的查询结果。
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3625962092
--------------------------------------------------------------------------------------------
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 |
| 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 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
-------------------------------------------------------------
Outline Data
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
/*+
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
---------------------------------------------------------------------------------------------
/*+
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
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
----------------------------------------------------------------------------------------------
DB_VERSION('11.2.0.1')
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")
4 - access("E"."DEPTNO"="D"."DEPTNO")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
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],
"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],
"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 - (#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".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]
----------------------------------------------------------------------------------------------
"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.
我们再执行一次查询。此时我们在表EMP上创建一个索引。
SQL> create index idx_emp_ename on emp(ename);
SQL> create index idx_emp_ename on emp(ename);
Index created.
SQL> explain plan set statement_id='TSH1' for
2 select *
3 from emp e,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';
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','TSH1','TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2977454843
--------------------------------------------------------------------------------------------------
Plan hash value: 2977454843
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
4 - access("E"."ENAME"='SMITH')
5 - access("E"."DEPTNO"="D"."DEPTNO")
5 - access("E"."DEPTNO"="D"."DEPTNO")
19 rows selected.
我们指定查询STATEMENT_ID='TSH1'在PLAN_TABLE中的执行计划。可以看出，此时表EMP的访问使用了索引。COST下降。
我们指定查询STATEMENT_ID='TSH1'在PLAN_TABLE中的执行计划。可以看出，此时表EMP的访问使用了索引。COST下降。