ORACLE SQL优化工具之--EXPLAIN PLAN

转载 2013年12月05日 15:42:32

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.

 

相关文章推荐

Oracle 执行计划(Explain Plan) 说明

如果要分析某条SQL的性能问题,通常我们要先看SQL的执行计划,看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的,却有一天突然性能很差,如果排除了系统资源和阻塞的原因,那么基本可以断...

Oracle EXPLAIN PLAN用法

使用EXPLAIN PLAN语句来确定Oracle数据库下指定SQL语句的执行计划,这个语句插入每一步执行计划的行描述到指定表中。你也可使用EXPLAIN PLAN语句作为SQL跟踪工具的一部分。 ...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

oracle EXPLAIN的使用

在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下 1、EXPLAIN的使用    Oracle RD...

利用 Oracle EM 企业管理器 进行oracle SQL的优化(自动生成索引)

应用情景 项目中有大量的SQL,尤其是涉及到统计报表时,表关联比较多,当初开发建表时也没搞好索引关联的,上线后发现查询很低。Oracle自带的EM控制台带有自动优化功能,能给出优化方案,本人主要利用...

Oracle性能优化图文详解——利用第三方工具

开发中或者是正在运行的系统性能显著恶化的场合,需要进行性能优化。当听到性能优化时,有些人可能会感觉到非常困难,如果使用OB的话,通过使用索引或者内存等可以非常简单的进行性能优化。这篇文章将要介绍怎样使...

Oracle-SQL Explain Plan解读

概述执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述。 基于ORACLE的应用系统很多性能问题,是由应用系统SQL性能低劣引起的,所以,SQL的性能优化很重要,分析与优化SQL的性能我...

Oracle SQL执行计划基线总结(SQL Plan Baseline)

Oracle 11g开始,提供了一种新的固定执行计划的方法,即SQL plan baseline,中文名SQL执行计划基线(简称基线),可以认为是OUTLINE(大纲)或者SQL PROFILE的改进...
  • oradh
  • oradh
  • 2014年06月13日 09:36
  • 3258

使用 EXPLAIN PLAN 获取SQL语句执行计划

SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE SQL优化工具之--EXPLAIN PLAN
举报原因:
原因补充:

(最多只允许输入30个字)