ORACLE SQL優化工具系列之--EXPLAIN PLAN
對於oracle資料庫來說，sql語句的優化可能是對效能提升最為明顯的，當然對於DBA來說，也是挑戰性比較大的。為了優化一個複雜的SQL語句，比如語句執行時間過長，我們根據語句的寫法，利用我們的經驗做出一些改動，當然是可以的，但更好的方法是獲取語句的執行計劃，看看語句在資料庫內部使用了什麼樣的資源，是按照什麼樣的步驟來執行的，比如採用什麼樣的關聯方法、什麼樣的關聯順序，以及對錶的訪問方法等。
為了獲取語句的執行計劃，我們可以採用多種方法和工具，比如toad工具，plsqldeveloper工具等，在我的文章當中，我們只會使用oracle自己的工具，比如本文將要介紹到的explainplan，oracle還有一些工具，autotrace 、sqltrace、tkprof、oem等，我將在以後的某個時間一一介紹。
ORACLE的explain plan工具的作用只有一個，獲取語句的執行計劃
1.語句本身並不執行，ORACLE根據優化器產生理論上的執行計劃
2.語句的分析結果存放在表PLAN TABLE中
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> desc plan_table
Name Null? Type
------------------------------------------------------------------------- ------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
根據上面的演示片段，我們可以猜到PLANTABLE有可能是一個公用的同義詞，實際上他指向sys使用者的一個全域性臨時表PLAN_TABLE$我們來確認一下
SQL> conn / as sysdba
Connected.
SQL> col table_owner for a10
SQL> col table_name for a20
SQL> col db_link for a15
SQL> set linesize 120
SQL> set pagesize 60
SQL> select * from dba_synonyms wheresynonym_name='PLAN_TABLE';
OWNER SYNONYM_NA TABLE_OWNETABLE_NAME DB_LINK
---------- ---------- ---------- -----------------------------------
PUBLIC PLAN_TABLESYS PLAN_TABLE$
SQL> select table_name,TEMPORARY from dba_tableswhere table_name='PLAN_TABLE$';
TABLE_NAME T
-------------------- -
PLAN_TABLE$ Y
既然是一個公用的同義詞，那所有的使用者就都可以使用，當然了，如果你願意，你也可以在自己的使用者（schema）下，單獨的來建表 plantable，你可以使用$ORACLE_HOME/rdbms/admin/utlxplan.sql,這個sql裡其實很簡單，就是建表而已。
下面的演示，我使用oracle預設提供的plan table，這個方式在oracle10g之後是預設存在的方式。
關於explain plan的語法
explain plan [set statement_id='text'] [into your plantable] for statement
稍微解釋一下，中括號中的內容是可以有，也可以沒有的
[set statement_id='text']
給for 後面要分析的語句指定一個名稱，這樣在plan table中比較容易找到相關語句的操作
[into your plantable]
把分析結果放到你指定的表中，這個表名稱可以任意，但是結構要和plan table 一樣，預設就是plan table
我們來看一個實際的例子，這裡我們只是獲取語句的執行計劃，並不會對得到的執行計劃做解釋
SQL> conn scott/tiger
Connected.
SQL> explain planfor
2 selectempno,ename,sal,comm
3 from emp
4 where empno=7369;
Explained.
語句分析後，在plan table中可以看到有3條記錄
SQL> select count(*) from plan_table;
COUNT(*)
----------
3
如何得到語句的執行計劃，我們可以有三種方法
1.直接的編寫SQL語句，查詢plan table表，並做格式化處理，這個方法比較麻煩，我在這裡不做演示
2.通過一個table函式呼叫dbms_xplan包，在這個包中主要有三個3個函式display、display_cursor、display_awr，我們這裡只是用display，關於其他的函式，以後我會單獨介紹
SQL> select * fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 16| 1 (0)| 00:00:01 |
| 1 | TABLEACCESS BY INDEX ROWID|EMP | 1 | 16| 1 (0)| 00:00:01 |
|* 2 | INDEXUNIQUESCAN | PK_EMP| 1| | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
14 rows selected.
3.通過utlxpls.sql或者utlxplp.sql指令碼來實現，指令碼存放的位置$ORACLE_HOME/rdbms/admin/
SQL> !ls$ORACLE_HOME/rdbms/admin/utlxpl*
/u01/app/oracle/product/11.2.0/db/rdbms/admin/utlxplan.sql
/u01/app/oracle/product/11.2.0/db/rdbms/admin/utlxpls.sql
/u01/app/oracle/product/11.2.0/db/rdbms/admin/utlxplp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 16| 1 (0)| 00:00:01 |
| 1 | TABLEACCESS BY INDEX ROWID|EMP | 1 | 16| 1 (0)| 00:00:01 |
|* 2 | INDEXUNIQUESCAN | PK_EMP| 1| | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("EMPNO"=7369)
14 rows selected.