Oracle 執行計畫（Explain Plan） 說明__Oracle

 如果要分析某條SQL的效能問題，通常我們要先看SQL的執行計畫，看看SQL的每一步執行是否存在問題。 如果一條SQL平時執行的好好的，卻有一天突然效能很差，如果排除了系統資源和阻塞的原因，那麼基本可以斷定是執行計畫出了問題。

      

       看懂執行計畫也就成了SQL最佳化的先決條件。 這裡的SQL最佳化指的是SQL效能問題的定位，定位後就可以解決問題。

 

 

一．         查看執行計畫的三種方法

1.1 設定autotrace

序號	命令	解釋
1	SET AUTOTRACE OFF	此為預設值，即關閉Autotrace
2	SET AUTOTRACE ON EXPLAIN	只顯示執行計畫
3	SET AUTOTRACE ON STATISTICS	只顯示執行的統計資訊
4	SET AUTOTRACE ON	包含2,3兩項內容
5	SET AUTOTRACE TRACEONLY	與ON相似，但不顯示語句的執行結果

 

SQL> set autotrace on

SQL> select * from dave;

        ID NAME

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

         8 安慶

         1 dave

         2 bl

         1 bl

         2 dave

         3 dba

         4 sf-express

         5 dmm

 

已選擇8行。

 

執行計畫

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

Plan hash value: 3458767806

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     8 |    64 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DAVE |     8 |    64 |     2   (0)| 00:00:01 |

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

 

統計資訊

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

 

SQL>

 

1.2 使用SQL

SQL>EXPLAIN PLAN FOR sql語句;

SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

 

樣本：

SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;

已解釋。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3458767806

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     8 |    64 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DAVE |     8 |    64 |     2   (0)| 00:00:01 |

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

已選擇8行。

執行計畫

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

Plan hash value: 2137789089

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

| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

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

 

統計資訊

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

         25  recursive calls

         12  db block gets

        168  consistent gets

          0  physical reads

          0  redo size

        974  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          8  rows processed

SQL>

 

1.3 使用Toad,PL/SQL Developer工具

 

 

二．         Cardinality（基數）/ rows

Cardinality值表示CBO預期從一個行源（row source）返回的記錄數，這個行源可能是一個表，一個索引，也可能是一個子查詢。   在Oracle 9i中的執行計畫中，Cardinality縮寫成Card。 在10g中，Card值被rows替換。

 

這是9i的一個執行計畫，我們可以看到關鍵字Card：

       執行計畫

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=402)

   1    0   TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=1 Bytes=402)

 

Oracle 10g的執行計畫，關鍵字換成了rows：

執行計畫

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

Plan hash value: 2137789089

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

| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

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

 

Cardinality的值對於CBO做出正確的執行計畫來說至關重要。 如果CBO獲得的Cardinality值不夠準確（通常是沒有做分析或者分析資料過舊造成），在執行計畫成本計算上就會出現偏差，從而導致CBO錯誤的制定出執行計畫。

 

       在多表關聯查詢或者SQL中有子查詢時，每個關聯表或子查詢的Cardinality的值對主查詢的影響都非常大，甚至可以說，CBO就是依賴於各個關聯表或者子查詢Cardinality值計算出最後的執行計畫。

 

       對於多表查詢，CBO使用每個關聯表返回的行數（Cardinality）決定用什麼樣的訪問方式來做表關聯（如Nested loops Join 或 hash Join）。

          多表串連的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

              http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

 

對於子查詢，它的Cardinality將決定子查詢是使用索引還是使用全表掃描的方式訪問資料。

 

 

 

三． SQL 的執行計畫

       產生SQL的執行計畫是Oracle在對SQL做硬解析時的一個非常重要的步驟，它制定出一個方案告訴Oracle在執行這條SQL時以什麼樣的方式訪問資料：索引還是全表掃描，是Hash Join還是Nested loops Join等。 比如說某條SQL通過使用索引的方式訪問資料是最節省資源的，結果CBO作出的執行計畫是全表掃描，那麼這條SQL的效能必然是比較差的。

       Oracle SQL的硬解析和軟解析

       http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

 

樣本：

SQL> SET AUTOTRACE TRACEONLY;  -- 只顯示執行計畫，不顯示結果集

SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;

已選擇13行。

 

執行計畫

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

Plan hash value: 992080948

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   4 - access("A"."EMPNO"="B"."MGR")

       filter("A"."EMPNO"="B"."MGR")

   5 - filter("B"."MGR" IS NOT NULL)

 

統計資訊

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

          0  recursive calls

          0  db block gets

         11  consistent gets

          0  physical reads

          0  redo size

       2091  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         13  rows processed

SQL>

 

 

 

 

圖片是Toad工具查看的執行計畫。 在Toad 裡面，很清楚的顯示了執行的順序。 但是如果在SQLPLUS裡面就不是那麼直接。 但我們也可以判斷：一般按縮排長度來判斷，縮排最大的最先執行，如果有2行縮排一樣，那麼就先執行上面的。

 

 

3.1 執行計畫中欄位解釋：

       ID: 一個序號，但不是執行的先後順序。執行的先後根據縮排來判斷。

       Operation： 當前操作的內容。

       Rows： 當前操作的Cardinality，Oracle估計當前操作的返回結果集。

       Cost（CPU）：Oracle 計算出來的一個數值（代價），用於說明SQL執行的代價。

       Time：Oracle 估計當前操作的時間。

 

3.2 謂詞說明：

Predicate Information (identified by operation id):