殊途同归：如何获得一个SQL的真实执行计划
崔华，网名 dbsnake
Oracle ACE Director，ACOUG 核心专家
如何查看一个sql的真实执行计划呢？用dbms_xplan.display_cursor(‘hash_value’,‘child_number’, 'advanced')是其中的一种很重要的方法。
我负责的一个库，在移植了大量数据后，跑最后一个运维作业的时候这个运维作业始终阻塞在这样的一个sql上：
update saldat setsdaprs ='C',sdatno = :4 where
sdaprf = :1 and ((sdafrm = :2 andsdatkt =:3) or (sdactt = :5 and sdactp =:6))
在分区表saldat上是存在两个对应的组合global index的：
SQL> selectindex_name,table_name,column_name from dba_ind_columns
2 where index_name in('PK_SALDAT_SDATKT','IDX_SALDAT_SDACTT');
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------------------------------------ ---------------------------
IDX_SALDAT_SDACTT SALDAT SDACTP
IDX_SALDAT_SDACTT SALDAT SDACTT
IDX_SALDAT_SDACTT SALDAT SDAPRF
PK_SALDAT_SDATKT SALDAT SDATKT
PK_SALDAT_SDATKT SALDAT SDAFRM
PK_SALDAT_SDATKT SALDAT SDAPRF
来看一下上述sql的真实执行计划：
SQL> selecthash_value, child_number, sql_text from v$sql where sql_text like 'updatesaldat%';
HASH_VALUE CHILD_NUMBERSQL_TEXT
---------- --------------------------------------------------------------------------------
2888063519 0 updatesaldat set sdaprs ='C',sdatno = :4 where sdaprf =
SQL> select * fromtable(dbms_xplan.display_cursor(2888063519, 0 , 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
HASH_VALUE 2888063519, child number 0
--------------------------------------
update saldat setsdaprs ='C',sdatno = :4 where sdaprf =:1 and ((sdafrm =:2 and sdatkt =:3) or (sdactt = :5 and sdactp =:6))
Plan hash value:1829738473
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| | |
| 1 | UPDATE | SALDAT | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 37 | 0 (0)| ROW L | RO
|* 3 | INDEX RANGE SCAN | PK_SALDAT_SDATKT | 1 | | 0 (0)| | |
----------------------------------------------------------------------------------------------------
Query Block Name /Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / SALDAT@UPD$1
3 - UPD$1 / SALDAT@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
INDEX_RS_ASC(@"UPD$1""SALDAT"@"UPD$1" ("SALDAT"."SDAPRF""SALDAT"."SDAFRM" "SALDAT"."SDATKT")
*/
Peeked Binds(identified by position):
--------------------------------------
2 - :1 (VARCHAR2(30), CSID=852): '999'
3 - :2 (VARCHAR2(30), CSID=852): '160'
4 - :3 (VARCHAR2(30), CSID=852): '2332143'
5 - :5 (VARCHAR2(30), CSID=852): '160'
6 - :6 (VARCHAR2(30), CSID=852): '2332143'
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter((("SDAFRM"=:2AND "SDATKT"=:3) OR ("SDACTT"=:5 AND"SDACTP"=:6)))
3 -access("SDAPRF"=:1)
从结果里可以看到上述SQL阻塞的原因是：Oracle把本来应该用作驱动查询条件（即上述执行计划的access部分）的where条件当作了校验查询条件（即上述执行计划的filter部分）。
知道了原因后，第一感觉就是尝试用use_concat提示，但实际的执行情况是不好的，用use_concat得不到我们想要的执行计划，Oracle这里选择了index skip scan：
UPDATE STATEMENT, GOAL= ALL_ROWS Cost=1276778 Cardinality=2 Bytes=70
UPDATE Objectowner=IPRA Object name=SALDAT
CONCATENATION
TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=IPRA Object name=SALDAT Cost=1276774 Cardinality=1 Bytes=35
INDEX SKIP SCAN Object owner=IPRA Objectname=IDX_SALDAT_SDACTT Cost=444676 Cardinality=6558757
TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=IPRA Object name=SALDAT Cost=4 Cardinality=1 Bytes=35
INDEX RANGE SCAN Object owner=IPRA Object name=IDX_SALDAT_SDACTT Cost=3 Cardinality=1
改变上述index skip scan的方法就是收集一下统计信息，上述分区表saldat没有子分区，所以不需要指定granularity，Oracle依然可以正确的收集global statistics：
SQL> exec dbms_stats.gather_table_stats(ownname=> 'IPRA',tabname => 'SALDAT',
estimate_percent => 30,cascade => TRUE,method_opt => 'FOR ALLCOLUMNS SIZE 1' );
PL/SQL proceduresuccessfully completed
收集完后会发现上述sql的执行计划还是未变，这是正常的，因为这里使用了绑定变量（后面我会专门用一节的内容指出绑定变量对执行计划的影响）：
上述库现在还处于数据移植阶段，所以我们可以放心的flush shared pool，这里flush shared pool的目的就是让上述sql硬解析，屏蔽掉绑定变量对其执行计划的影响：
sys@IPRACA>alter system flush shared_pool;
System altered.
当执行完上述flush语句后，现在我们可以看到执行计划已经变成了期望的形式：
SQL> select * fromtable(dbms_xplan.display_cursor(2888063519, 0 , 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
HASH_VALUE 2888063519, child number 0
--------------------------------------
update saldat setsdaprs ='C',sdatno = :4 where sdaprf =:1 and ((sdafrm = :2 and sdatkt=:3) or (sdactt = :5 and sdactp =:6))
Plan hash value:2813126325
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 7 (100)| | | |
| 1 | UPDATE | SALDAT | | | | | | |
| 2 | CONCATENATION | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 35 | 3 (0)| 00:00:01 | RO
|* 4 | INDEX UNIQUE SCAN | PK_SALDAT_SDATKT | 1| | 2 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 35 | 4 (0)| 00:00:01 | RO
|* 6 | INDEX RANGE SCAN | IDX_SALDAT_SDACTT | 1 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
Query Block Name /Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
3 - UPD$1_1 / SALDAT@UPD$1
4 - UPD$1_1 / SALDAT@UPD$1
5 - UPD$1_2 / SALDAT@UPD$1_2
6 - UPD$1_2 / SALDAT@UPD$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
OUTLINE_LEAF(@"UPD$1_1")
USE_CONCAT(@"UPD$1" 8)
OUTLINE_LEAF(@"UPD$1_2")
OUTLINE(@"UPD$1")
INDEX_RS_ASC(@"UPD$1_1""SALDAT"@"UPD$1" ("SALDAT"."SDAPRF""SALDAT"."SDAFRM" "SALDAT"."SDATKT
INDEX_RS_ASC(@"UPD$1_2""SALDAT"@"UPD$1_2" ("SALDAT"."SDACTP""SALDAT"."SDAPRF" "SALDAT"."SDAC
*/
Peeked Binds(identified by position):
--------------------------------------
2 - :1 (VARCHAR2(30), CSID=852): '999'
3 - :2 (VARCHAR2(30), CSID=852): '160'
4 - :3 (VARCHAR2(30), CSID=852): '2332143'
5 - :5 (VARCHAR2(30), CSID=852): '160'
6 - :6 (VARCHAR2(30), CSID=852): '2332143'
Predicate Information(identified by operation id):
---------------------------------------------------
4 - access("SDAPRF"=:1AND "SDAFRM"=:2 AND "SDATKT"=:3)
5 - filter((LNNVL("SDATKT"=:3) ORLNNVL("SDAFRM"=:2)))
6 - access("SDACTP"=:6AND "SDAPRF"=:1 AND "SDACTT"=:5)
在作出上述修改后，运维作业顺利在规定时间内跑完。
在上面的内容中，我们已经描述了在Oracle 10g及其后续版本里可以使用dbms_xplan.display_cursor('hash_value', 'child_number','advanced')来得到一个sql的真实执行计划，但可惜的是dbms_xplan.display_cursor在Oracle 9i里是不存在的。那在Oracle 9i中如果我们想得到一个sql的真实执行计划，我们该怎么办呢？
答案是查询v$sql_plan。
本文提供一个存储过程printql，可以把SPID或者SID所对应的sql和其真实执行计划打印出来，而且9i/10g/11g都可以用。printsql的核心代码是来源于老熊的这篇文章——在Oracle 9i下的display_cursor脚本（http://www.laoxiong.net/oracle9i_display_cursor.html）。
我们来看一下printsql的用法：
/*
功能： 打印对应spid或sid所对应的sql以及其执行计划
作者：老熊，dbsnake
创建日期：2010-11-12
输入参数：i_n_id: 输入的spid或sid
i_vc_idtype : 输入的ID的类型，'SPID'表示输入的是spid，'SID'表示输入的是sid.
输出参数：无
输入输出参数：无
调用到的存储过程：无
*/
printsql的源码在原文链接下载，下面来看一个应用printsql的实际例子：
OS端top里显示SPID为1212576的进程占用了14.2%的CPU，现在我们想看看这个进程到底在做什么：
Name PID CPU% PgSp Owner ClientV2 0 Press:
oracle 1212576 14.2 6.0 oracle ServerV3 0 "h" for help
topas 294994 0.2 3.1 oracle ClientV3 0 "q" to quit
oracle 241960 0.1 4.8 oracle
直接执行一下printsql，传入1212576就可以看到结果了：
SQL> setserveroutput on size 1000000
SQL> execprintsql(1212576,'SPID');
--------------------------------------------------------------------------------------
select * from caipra.uplbas where ubatkt=2222222 and ubafrm=450
--------------------------------------------------------------------------------------
The session id is 356
The status is ACTIVE
The sql hash value is352212893
The child cursornumber is 0
The prev hash value is352212893
The prev child cursornumber is 0
The osuser is cuihua
The machine isWORKGROUP\Y8908CUIHUA
The terminal isY8908CUIHUA
The program issqlplus.exe
The event is db file scattered read
--------------------------------------------------------------------------------------
alter system kill session '356,652' immediate;
--------------------------------------------------------------------------------------
The hash_value is352212893
The child_number is 0
The plan_hash_value is4237911507
The execution is 2
The buffer_gets is 311300
The gets_per_exec is155650
The rows_processed is2
The rows_per_exec is 1
The disk_reads is 210483
The reads_per_exec is105241.5
The cpu_time is35.462323
The cpu_per_exec is17.7311615
The ELAPSED_TIME is72.283601
The ela_per_exec is36.1418005
--------------------------------------------------------------------------------------
HASH_VALUE: 352212893 CHILD_NUMBER: 0
-------------------------------------------------------------------------------------------------------------------
select * fromcaipra.uplbas where ubatkt=2222222 and ubafrm=450
Plan hash value:4237911507
------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Pstart | Pstop |
------------------------------------------------------------------
| 1 | PARTITION RANGE ALL | | 1 | 1 | 24 |
| * 2 | TABLE ACCESS FULL | UPLBAS | 1 | 1 | 24 |
------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter((TO_NUMBER("UBATKT")=2222222AND TO_NUMBER("UBAFRM")=450))
从结果里可以看到printsql已经帮你把这个process正在执行的sql，其真实的执行计划以及Kill这个session的语句打印出来了。