怎么使用utlxplan

這篇文章將為大家詳細講解有關怎么使用utlxplan，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

這幾天幫一個朋友優化一個后臺JOB，發現需要使用UTLXPLAN。自己測試使用，記錄如下。

utlxplan是Oracle提供的查看SQL語句執行計劃的工具，相對于AUTOTRACE使用UTLXPLAN不需要真實執行完該SQL語句，對于長查詢的語句選擇使用UTLXPLAN盡快獲得執行來分析，使

用UTLXPLAN是基于數據庫收集的統計數據，所以此時如果想獲得更準確地執行計劃，就需要統計數據的精確了，這點要注意。
下面是使用UTLXPLAN的步驟。
1、創建PLAN_TABLE，存儲執行計劃。

SQL> connect /as sysdba
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';

     SYNONYM_NAME                   TABLE_NAME
     ------------------------------ ------------------
     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';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   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';

Explained.

SQL> set line 120
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','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    |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
12 rows selected.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','TYPICAL'));

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 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

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

   3 - filter("E"."ENAME"='SMITH')
   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參數的查詢結果。

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','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 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
      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")

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

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]

61 rows selected.

我們再執行一次查詢。此時我們在表EMP上創建一個索引。
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';

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH1','TYPICAL'));

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   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")

19 rows selected.
我們指定查詢STATEMENT_ID='TSH1'在PLAN_TABLE中的執行計劃。可以看出，此時表EMP的訪問使用了索引。COST下降。

關于怎么使用utlxplan就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。