【优化】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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126497/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126497/