Oracle中的执行计划(原创)

执行计划概述
所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也 可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的SQL仅仅是 描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，是由数据库来决定的。
查看执行计划
使用autotrace sqlplus系统参数：
SQL> set autotrace trace on
SQL> select * from dual;
DUM
---
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          6  consistent gets
          4  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
各个资源解释如下
Recursive Calls：

系统或者用户执行sql时需要执行的相关内部递归sql。具体解释可看下文
Number of recursive calls generated at both the user and system level.   
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
Recursive calls can be generated by the following activities:
1、An object requiring an additional extent for storage (dynamic extension)
2、Misses on the dictionary cache
3、Firing of database triggers 
4、DDL statements
5、Execution of SQL statements within stored procedures, packages, functions, and anonymous PL/SQL blocks
6、Enforcement of referential integrity constraints
If Oracle is making an inordinate number of recursive calls, try to determine which of the previously listed activities is causing most of the recursive calls. Run the application through TKPROF with EXPLAIN PLAN to see what the application is doing.
Also, monitor the number of extents in the database to see if there is noticeable dynamic extension. If the recursive calls are caused by dynamic extension, you can reduce the number of calls by allocating larger extents to the relevant objects. A dictionary cache that is too small can also cause recursive calls.
DB Block Gets（当前请求的块数目）
表示从当前内存中直接获取，而不是通过undo表空间构造的一致性读的块数目。
Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）
这里的概念 是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要 查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产生了一致性 读。关于一致性读，读者可参考：
http://czmmiao.iteye.com/blog/1294307
Physical Reads（物理读）
就是从磁盘上读取数据块的数量，其产生的主要原因是：
1、 在数据库高速缓存中不存在这些块
2、 全表扫描
3、 磁盘排序
它们三者之间的关系大致可概括为：
逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'phsical reads'。
Sorts(disk):
Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
磁盘中的排序
bytes sent via SQL*Net to client:
Total number of bytes sent to the client from the foreground processes.
通过网络发送给客户端的数据
bytes received via SQL*Net from client:
Total number of bytes received from the client over Oracle Net.
通过网络从客户端接收到的数据
SQL*Net roundtrips to/from client:
Total number of Oracle Net messages sent to and received from the client.
注意： autotrace 命令是先执行SQL，再出执行计划，如果SQL耗时巨大，则不现实。
使用explain plan for语句：
SQL> explain plan for select * from dual;
    Explained.
SQL> select * from table(DBMS_XPLAN.display);
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2137789089
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                                                              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                       |                   |  8168   | 16336 |    21   (0)| 00:00:01 |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |             |       |
    |     |
    ---------------------------------------------------------------------------------------------
这样就可以在执行SQL之前查看执行计划了
设置SQL_TRACE参数
设置当前session的 SQL_TRACE
SQL> alter session set SQL_TRACE=true;
SQL> select * from dual;
SQL> alter session set SQL_TRACE=false;
设置当前系统的 SQL_TRACE
SQL> alter system set SQL_TRACE=true;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
       SID    SERIAL# USERNAME
    ------ ---------- ------------------
       127      31923 A
       128      54521 B
       129      48940 B
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
SQL> select * from dual;
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
使用10046事件进行查询：
10046事件级别：
Lv1  - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4  - Level 1 + 绑定值(bind values)
Lv8  - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
..OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> select * from dual;
SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
       SID    SERIAL# USERNAME
    ------ ---------- ------------------
       127      31923 A
       128      54521 B
       129      48940 B   
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
SQL> select * from dual;
SQL> exec dbms_system.set_ev(127,31923,10046,0,'A');
注意：常用的查看执行计划方法为 autotrace和explain plan for。通过设置sql_trace和10046事件来查看执行计划的情况较少
注：无法使用autotrace的解决办法（9i）：
SQL>start $ORACLE_HOME/rdbms/admin/utlxplan.sql;
SQL>create public synonym plan_table for plan_table;
SQL>grant ALL on plan_table to public;

如何读懂执行计划
创建实验环境如下
SQL> create table t1(id int,name varchar2(500));
Table created.
SQL>  create table t2(id int,name varchar2(500));
Table created.
SQL> create index ind_t1 on t1(id);
Index created.
SQL>  create index ind_t2 on t2(id);
Index created.
SQL> create index ind_t2_name on t2(name);
Index created.
SQL> insert into t1 select object_id,object_name from dba_objects;
50430 rows created.
SQL> exec dbms_stats.gather_table_stats('HR','T1',cascade=>true,method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
SQL> set autotrace trace explain
执行如下查询
SQL> select * from t1 where id in (select ID FROM t2 where name='AA')
Execution Plan
----------------------------------------------------------
Plan hash value: 3232435503
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   293 |     5  (20)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |     1 |   293 |     5  (20)| 00:00:01 |
|   3 |    SORT UNIQUE              |        |     1 |   265 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL       | T2     |     1 |   265 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("NAME"='AA')
   5 - access("ID"="ID")
Note
-----
   - dynamic sampling used for this statement
以上执行计划中各列的意义如下
ID：序号，注意，ID的到小并不代表执行的先后顺序
Operation：当前的操作内容
Rows列：当前操作的cardinality(9i)，Oracle估算当前操作的返回结果集的行数，这个行源可能是一个表，一个索引，也可能是一个子查询。
Bytes：操作的数据量大小
Cost(cpu)：Oracle计算出来的一个数值(代价),用于说明SQL执行的代价。
Time:Oracle估算当前操作所需的时间。
执行计划中缩进度越大，表示越早执行，当两行的缩进一样时，最上面的最先被执行。上面的执行计划需从 缩进度 最大的行读取，他是最先执行的步骤。具体过程为： ID4->ID3->ID5->ID2->ID1->ID0
翻译成文字如下：
在t2表中根据name=AA查找匹配的行，找出所有行并排序后与t1表上的索引ID_1中相应的ID进行关联，然后重复，直到把排序的结果集扫描完，这 个过程叫做nested loops。当整个T2表扫描完后，会产生一个结果集，这个结果集是IND_T1的一个索引结果集。然后Oracle根据索引键值上的rowid去T1表 中找相应的记录，即ID1.然后返回结果，即ID0。
上面的filter和access都是按照谓词的条件对数据进行过滤的方式，他们的区别如下：
filter，表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。
access，表示谓词条件的值会影响数据的访问路径(表还是索引，这里是索引）。
结果集Rows对执行计划的影响
当前操作的rows(9i中为cardinality，10g替换为rows)表示Oracle估算当 前操作的返回结果集的行数，这个行源可能是一个表，一个索引，也可能是一个子查询。rows表示CBO估算当前操作预期获取的记录数，这个值对CBO做出 正确的执行计划至关重要。如果CBO获得的rows不准确（通常是没有做分析或者分析数据过旧导致的），在执行成本计算上会出现偏差，从而导致CBO产生 错误的出执行计划 。具体可参加下列例子
SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> update t set id=99 where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id);
Index created.
采用dynamic_sampling(t 0)的方式禁止对t表的动态采用。
SQL> select /*+ dynamic_sampling(t 0) */ * from t where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   194 | 15326 |    50   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   194 | 15326 |    50   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |    77 |       |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
可以看到CBO猜测ID为1的数据有194条，相对于总数来说较少，故采用了索引而不是全表扫描。
SQL> select * from t where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 47242 |  3644K|    56   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 47242 |  3644K|    56   (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)
Note
-----
   - dynamic sampling used for this statement
上面的查询中，CBO通过动态采用，估算出表中的实际数据量为47242,非常接近实际数据量50249,故采用了全表扫描。
可见，rows的多少直接影响了执行计划的选择
在多表关联查询或者SQL中有自查询时，每个关联表或是自查询的rows对主表的影响非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询rows值来计算出最后的执行计划。
对于多表查询，CBO使用每个关联表返回的行数决定使用什么样的访问方式来做关联（比如nested loops join或是hash join）;对于子查询，它的rows将决定子查询是使用索引还是使用全表扫描的方式访问数据。
实验步骤如下
采用cardinality(t2 1000)的方式告诉CBO从T2表中获取10000条记录;
SQL>select * from t1 where id in (select  /*+ dynamic_sampling(t2 0) cardinality(t2 10000) */ id from t2 where name='AA')
Execution Plan
----------------------------------------------------------
Plan hash value: 2007208103
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 50430 |    14M|       |   288   (2)| 00:00:04 |
|*  1 |  HASH JOIN SEMI              |             | 50430 |    14M|  1976K|   288   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL          | T1          | 50430 |  1378K|       |    56   (2)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2          | 10000 |  2587K|       |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IND_T2_NAME |     1 |       |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"="ID")
   4 - access("NAME"='AA')
通过 cardinality(t2 1000)的方式模拟出了子查询中返回的结果集数，因此生成执行计划时，CBO选择了HASH JOIN SEMI。
当结果集变小时执行计划如下
SQL> select * from t1 where id in (select /*+ dynamic_sampling(t2 0) cardinality(t2 1) */ id from t2 where name='AA')
Execution Plan
----------------------------------------------------------
Plan hash value: 2850018061
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |   293 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T1          |     1 |    28 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |             |     1 |   293 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE                 |             |     1 |   265 |     1   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2          |     1 |   265 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IND_T2_NAME |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | IND_T1      |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("NAME"='AA')
   6 - access("ID"="ID")
可以看到因为子查询的结果集为1,所以CBO选择了netsted loops的方式进行关联。
两表关联的执行计划结果与上面类似，读者可根据如下语句自行进行实验，笔者在此也不再赘述。
select /*+ dynamic_sampling(t2 10000) cardinality */ * from t1,t2 where t1.id=t2.id;
select /*+ dynamic_sampling(t2 1) cardinality */ * from t1,t2 where t1.id=t2.id;
以上的例子主要说明了rows对CBO生成执行计划的影响，在看执行计划时要特别关注。一定要注意每个操作的rows值，如果这个值明显不对，那么很可能操作的表的分析数据出了问题或者表没有分析。
DDL的执行计划
从Oracle10g开始，可以通过EXPLAIN PLAN FOR查看DDL语句的执行计划了。
在9i及以前版本，Oracle只能看到DML的执行计划，不过从10g开始，通过EXPLAIN PLAN FOR的方式，已经可以看到DDL语句的执行计划了。
这对于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX，ALTER INDEX REBUILD等语句有很大的帮助。
举个简单的例子，Oracle的文档上对于索引的建立有如下描述：
The optimizer can use an existing index to build another index. This results in a much faster index build.
如果看不到DDL的执行计划，只能根据执行时间的长短去猜测Oracle的具体执行计划，但这种方法没有足够的说服力。但是通过DDL的执行计划，就使得结果一目了然了。下面就来验证下 Oracle文档上的说法
SQL> create table t_ddl as select * from dba_objects;
Table created.
SQL> explain plan for create index t_ddl_idx on t_ddl(object_name);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1333762510
------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           | 58238 |  3753K|   297   (1)| 00:00:04 |
|   1 |  INDEX BUILD NON UNIQUE| T_DDL_IDX |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           | 58238 |  3753K|            |          |
|   3 |    TABLE ACCESS FULL   | T_DDL     | 58238 |  3753K|   160   (2)| 00:00:02 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 5242K bytes
14 rows selected.
SQL> create index t_owner_name on t_ddl(owner,object_name);
Index created.
SQL> explain plan for create index t_name on t_ddl(object_name);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2111373224
---------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |              | 58238 |  3753K|   297   (1)| 00:00:04 |
|   1 |  INDEX BUILD NON UNIQUE| T_NAME       |       |       |            |          |
|   2 |   SORT CREATE INDEX    |              | 58238 |  3753K|            |          |
|   3 |    INDEX FAST FULL SCAN| T_OWNER_NAME |       |       |            |          |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 5242K bytes
14 rows selected.
可以看到，根据现有的创建索引创建索引的速度确实会更快。笔者个认为，对于DDL的执行计划，最有价值的是在进行DDL操作前的开销评估。
SQL> SET AUTOT ON
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
Index created.
注意，查看DDL的执行计划需要使用EXPLAIN PLAN FOR，AUTOTRACE对于DDL是无效的。

 

参考至：《让Oracle跑得更快》谭怀远著
            http://blog.csdn.net/zhoubo200/article/details/5345019
            http://wenku.baidu.com/view/c687e7325a8102d276a22fe7.html
            http://blog.csdn.net/tianlesoftware/article/details/5827245
            http://www.cnblogs.com/kelin1314/archive/2009/12/08/1619414.html

本文原创，转载请注明出处、作者
如有错误，欢迎指正

邮箱:[e-mail]

评论

1 楼 jinnianshilongnian 2012-03-31  

支持一下 虽然用mysql