ORACLE执⾏计划explain说明
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());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.
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.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。