ORACLE SQL优化工具之--EXPLAIN PLAN

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.