【最佳化】explain plan for 方式存取執行計劃

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

explain plan for 方式存取sql的執行計劃檢視的只是最近的Plan table中的資訊

 

實驗：

1.以explain plan for 的方式進行操作存入sql執行計劃；

SYS@ORA11GR2>conn hr/hr  

Connected.

HR@ORA11GR2>explain plan for select count(*) from employees;

 

Explained.

 

2.檢視dbms_xplan包

SYS@ORA11GR2>desc dbms_xplan

3.以basic模式檢視執行計劃：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'basic'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3580537945

 

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

| Id  | Operation        | Name         |

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

|   0 | SELECT STATEMENT |              |

|   1 |  SORT AGGREGATE  |              |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |

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

 

9 rows selected.

 

4.以typical模式檢視執行計劃：（預設是typical）

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'typical'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3580537945

 

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

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

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

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

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

 

9 rows selected

 

5. 以serial模式檢視執行計劃：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'serial'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3580537945

 

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

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

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

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

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

 

9 rows selected.

 

6. 以all模式檢視執行計劃：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'all'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3580537945

 

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

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

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

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

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

 

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

 

PLAN_TABLE_OUTPUT

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

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

 

   1 - SEL$1

   2 - SEL$1 / EMPLOYEES@SEL$1

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT(*)[22]

 

20 rows selected.

 

7. 以advanced模式檢視執行計劃：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'advanced'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3580537945

 

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

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

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

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

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

 

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

 

PLAN_TABLE_OUTPUT

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

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

 

   1 - SEL$1

   2 - SEL$1 / EMPLOYEES@SEL$1

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMAIL"))

 

PLAN_TABLE_OUTPUT

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

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

 

Column Projection Information (identified by operation id):

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

 

 

PLAN_TABLE_OUTPUT

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

   1 - (#keys=0) COUNT(*)[22]

 

34 rows selected.