ORACLE SQL优化工具之--EXPLAIN PLAN

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.