Oracle中的执行计划
使用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是无效的。
