关闭

ORACLE SQL优化工具之--EXPLAIN PLAN

8821人阅读 评论(0) 收藏 举报
分类:

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    
  selectempno,ename,sal,comm     
  from emp
  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|      (0)| 00:00:01 |
  1 |  TABLEACCESS BY INDEX ROWID|EMP      1 |    16|      (0)| 00:00:01 |
|*  2 |   INDEXUNIQUESCAN        | PK_EMP|    1|           (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|      (0)| 00:00:01 |
  1 |  TABLEACCESS BY INDEX ROWID|EMP      1 |    16|      (0)| 00:00:01 |
|*  2 |   INDEXUNIQUESCAN        | PK_EMP|    1|           (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 -access("EMPNO"=7369)
14 rows selected.

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4425173次
    • 积分:35627
    • 等级:
    • 排名:第129名
    • 原创:357篇
    • 转载:1190篇
    • 译文:0篇
    • 评论:97条
    最新评论