在进行SQL诊断和优化时，通常都需要获取SQL的执行计划，通过执行计划来判断SQL的执行是否合理，那么如何来获取SQL的执行计划就显得非常重要了。
上一节介绍的AutoTrace功能是获得SQL执行计划的方法之一，在这一节，继续讨论SQL执行计划获取方法及相关诊断应用。
1. 通过V$SQL_PLAN获得执行计划
从Oracle9i开始，Oracle开始通过V$SQL_PLAN等视图进行SQL执行计划的记录，通过这个视图，可以获取正在执行中或者仍然缓存着的SQL执行计划，从而可以帮助我们进行实时准确的数据库诊断。在Oracle9i中，可以通过自定义编写的一些脚本来获取SQL的执行计划，如通过HASH_VALUE（可以通过V$SESSION或者V$SQL、V$SQL_PLAN视图获得SQL的HASH_VALUE）输入来获取SQL及其执行计划：
oracle@/opt/oracle/tools$./getplan_by_hashvalue.sh 3870760741 SQL_TEXT -------------------------------------------------------------------------------- select count(uspl.numusplguid) from hy_usersubplan_log uspl,hy_serviceplan sp,hy_serviceinfo s,hy_spinfo pvd,hy_platform pf where uspl.numsplanguid + 0 = sp.numplanguid and sp.numsvrguid = s.numsvrguid and s.numspguid + 0 = pvd.numspguid and pvd.numptguid+ 0 = pf.numptguid and pf.vc2platformid = :1 and pvd.vc2spcode = :2 and s.vc2service_id = :3 and s.vc2ispack = :4 and uspl.vc2enabledflag = 'Y' and uspl.datstart <= sysdate and nvl(uspl.datend, sysdate + 1) >= sysdate and uspl.vc2userid = :5 HASH_VALUE EXECUTIONS PER_GETS MODULE ---------- ---------- ------------ ------------------------------------ 3870760741 30458 78.4 JDBC Thin Client -------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | -------------------------------------------------------------------------------- |SELECT STATEMENT |----- 3870760741 ----| | | 28 | |SORT AGGREGATE | | 1 | 269 | | | NESTED LOOPS | | 1 | 269 | 28 | | NESTED LOOPS | | 1 | 204 | 9 | | NESTED LOOPS | | 1 | 178 | 7 | | NESTED LOOPS | | 1 | 100 | 6 | | TABLE ACCESS BY INDEX ROWID|HY_PLATFORM | 1 | 20 | 2 | | INDEX UNIQUE SCAN |HYUIDX_PLATFORMID | 97 | | 1 | | TABLE ACCESS FULL |HY_SERVICEINFO | 1 | 80 | 4 | | TABLE ACCESS BY INDEX ROWID |HY_SPINFO | 1 | 78 | 1 | | INDEX UNIQUE SCAN |HYPK_SPINFO | 97 | | | | TABLE ACCESS BY INDEX ROWID |HY_SERVICEPLAN | 2 | 52 | 2 | | INDEX RANGE SCAN |HYUIDX_SERVICEPLAN | 2 | | 1 | | PARTITION LIST ALL | | | | | | TABLE ACCESS BY LOCAL INDEX R|HY_USERSUBPLAN_LOG | 1 | 65 | 19 | | INDEX RANGE SCAN |HYIDX_USPL_USERID | 72 | | 18 | --------------------------------------------------------------------------------
该脚本的编码内容如下：
oracle@/opt/oracle/tools$cat getplan_by_hashvalue.sh
#!/bin/ksh
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<EOF
set lines 121
set pages 999
col sql_text format a80
col module format a36
col per_gets format 999999999.9
select sql_text from v\$sqltext_with_newlines where hash_value=$1 order by piece;
select hash_value,executions,buffer_gets/decode(executions,0,1,executions) as per_gets,a.module
from v\$sqlarea a where a.hash_value=$1;
set heading off
select '--------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v\$sql_plan where hash_value = $1
and child_number = (select max(child_number) from v\$sql_plan where hash_value = $1))
union all
select '--------------------------------------------------------------------------------' from dual;
exit
EOF以下介绍一个实际的诊断案例供参考。
曾经遇到过这样一次性能问题，开发人员编写的一个存储过程，其中包含了一系列的事务处理，大约有10个左右的DML事务执行，每一个SQL在SQL*Plus中执行都很迅速，但是一旦放在过程中执行，通过参数传入一个sysdate，整个过程的执行就变得非常缓慢，无法成功完成。数据库环境为Oralce9iR2：
SQL> select * from v$version where rownum <2; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
收到这个问题首先需要确定哪个SQL是真正引起性能问题的罪魁祸首。首先对Procedure进行一点修改，在每个DML事务执行之前在一张临时创建的测试表中插入一个时间，前后两个时间相减可以得到每个SQL独立执行的时间。通过这个办法，发现在执行到第8个SQL时，响应失去。也就是说，这个SQL是导致性能缓慢的根本原因。
找到这个SQL接下来的事情就变得简单一些，修改这个过程，在Procedure之前增加一个跟踪：
create or replace procedure cmop_servdetail_d_eygle (m_datstat date) Authid Current_User is begin execute immediate 'alter session set sql_trace=true'; ………………
那么当再次执行这个过程时，SQL的执过程被记录到一个Trace文件中，但是注意，由于SQL可能暂时无法完成，所以执行计划等信息并不会输出，但是由于SQL的HASH过程首先完成，在Trace文件的输出中，首先打印出了SQL的HASH VALUE值，这里是：hv=3740055767
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 ===================== PARSING IN CURSOR #2 len=32 dep=1 uid=28 oct=42 lid=28 tim=12567557125437 hv=3943786303 ad='20e289d8' alter session set sql_trace=true END OF STMT EXEC #2:c=0,e=7735,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=12567557124267 ===================== PARSING IN CURSOR #2 len=4721 dep=1 uid=28 oct=2 lid=28 tim=12567557130962 hv=3740055767 ad='1270b8c0' INSERT into cmo_servdetail_d (vc2dayguid, 。。。。。。。。 and a.vc2bt = c.vc2cmbt and a.vc2cid = c.vc2cmcid and c.numsvrguid = b.numsvrguid group by b.numsvrguid, b.vc2service_id, substr(a.vc2mid, 0, 4), a.vc2ua, c.vc2urltype END OF STMT
得到这个HASH VALUE值之后就可以通过v$sql_plan来获得这个SQL的执行计划，另外一个需要说明的是，我在诊断某个SQL问题时通常习惯将这个SQL创建到一张临时表中，以避免可能对v$sql_plan的反复查询带来的消耗。
对于这个案例我只需要发出如下语句：
create table t as select * from v$sql_plan where hash_value=3740055767
通过查询可以获得这条问题SQL的执行计划：
-------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | -------------------------------------------------------------------------------- |INSERT STATEMENT |----- 3740055767 ----| | | 123 | |SORT GROUP BY | | 2 | 532 | 123 | | FILTER | | | | | | HASH JOIN | | 2 | 532 | 115 | | TABLE ACCESS BY LOCAL INDEX R|CM_URLLOG_JUMP | 1 | 127 | 100 | | NESTED LOOPS | | 1 | 201 | 100 | | TABLE ACCESS FULL |HY_SVR_REF_URL_CMCID | 11 | 814 | 3 | | PARTITION RANGE ITERATOR | | | | | | BITMAP CONVERSION TO ROWID| | | | | | BITMAP AND | | | | | | BITMAP CONVERSION FROM R| | | | | | SORT ORDER BY | | | | | | INDEX RANGE SCAN |CMIDX_URLLOG_JUMP_DA | 6 | | 1 | | BITMAP CONVERSION FROM R| | | | | | INDEX RANGE SCAN |CMIDX_URLLOG_JUMP_CI | 6 | | 19 | | TABLE ACCESS FULL |HYO_SERVICEPLAN | 10K| 694K| 14 | --------------------------------------------------------------------------------
这就是这个SQL的执行计划，来对比一下SQL*Plus中执行这条SQL的执行计划：
Execution Plan ---------------------------------------------------------- 0 INSERT STATEMENT Optimizer=CHOOSE (Cost=35 Card=12 Bytes=3192) 1 0 SORT (GROUP BY) (Cost=35 Card=12 Bytes=3192) 2 1 FILTER 3 2 HASH JOIN (Cost=26 Card=12 Bytes=3192) 4 3 HASH JOIN (Cost=11 Card=1 Bytes=201) 5 4 TABLE ACCESS (FULL) OF 'HY_SVR_REF_URL_CMCID' (Cost=3 Card=11 Bytes=814) 6 4 PARTITION RANGE (ITERATOR) 7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CM_URLLOG_JUMP' (Cost=6 Card=127720 Bytes=16220440) 8 7 INDEX (RANGE SCAN) OF 'CMIDX_URLLOG_JUMP_DAT'(NON-UNIQUE) (Cost=2 Card=229897) 9 3 TABLE ACCESS (FULL) OF 'HYO_SERVICEPLAN' (Cost=14 Card=10946 Bytes=711490)
注意到这两个执行计划完全不同，速度快的执行计划对于PARTITION RANGE访问是通过一个索引来完成的；而对于速度慢的执行计划，这里却使用了2个索引进行位图转换：
PARTITION RANGE ITERATOR BITMAP CONVERSION TO ROWIDS BITMAP AND BITMAP CONVERSION FROM ROWIDS SORT ORDER BY INDEX RANGE SCAN CMIDX_URLLOG_JUMP_DAT BITMAP CONVERSION FROM ROWIDS INDEX RANGE SCAN CMIDX_URLLOG_JUMP_CID
正是这个转换使得性能大为缩减。
显然这个错误的选择是由于CMIDX_URLLOG_JUMP_CID索引的存在，再加上绑定变量的影响，CBO最终选择了错误的执行计划，为了快速的解决问题，在确认之后，我们直接Drop掉了这个索引。
此时再次运行过程，发现很快完成，此时的执行计划通过同样的方法可以获得：
-------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | -------------------------------------------------------------------------------- |INSERT STATEMENT |----- 3740055767 ----| | | 35 | |SORT GROUP BY | | 12 | 3K| 35 | | FILTER | | | | | | HASH JOIN | | 12 | 3K| 26 | | HASH JOIN | | 1 | 201 | 11 | | TABLE ACCESS FULL |HY_SVR_REF_URL_CMCID | 11 | 814 | 3 | | PARTITION RANGE ITERATOR | | | | | | TABLE ACCESS BY LOCAL INDEX|CM_URLLOG_JUMP | 128K| 15M| 6 | | INDEX RANGE SCAN |CMIDX_URLLOG_JUMP_DA | 230K| | 2 | | TABLE ACCESS FULL |HYO_SERVICEPLAN | 10K| 694K| 14 | --------------------------------------------------------------------------------
现在的执行计划恢复了正常。注意到错误的执行计划选择了bitmap convert的执行计划，而两个索引都是B*Tree索引。这种转换是Oracle9i引入的，同时一个隐含参数被用来控制这种转换：
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
Enter value for par: _b_tree_bitmap_plans
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%_b_tree_bitmap_plans%'
NAME VALUE DESCRIB
------------------------ ------ ------------------------------------
_b_tree_bitmap_plans TRUE enable the use of bitmap plans for tables w. only B-tree indexes如果这种性能衰减的转换经常发生，可以将这个隐含参数设置为FALSE.
2.EXPLAIN PLAN FOR与DBMS_XPLAN获取
在前面已经简单提到过，Explain Plan For和DBMS_XPLAN包结合可以用于获取SQL的执行计划。本节我们将对这两者的结合使用进行进一步的介绍。
EXPLAIN PLAN命令可以在后台对SQL进行解析，并将SQL执行计划加载到执行计划表中（默认名称为PLAN_TABLE），这是EXPLAIN PLAN的作用，其通常的使用方法是在SQL*PLUS中输入类似如下命令：
Explain plan <set statement_id = ‘text’> <into your plan table>for statement
其中通过set statement_id = ‘text’ 可以为SQL进行名称标记，“into your plan table”默认的是plan_table表，通常使用格式如下：
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno=7788;
执行计划生成之后，剩下的就是展现问题，DBMS_XPLAN包就是用来实现这一功能的，该PACKAGE自Oracle9iR2引入，初始的只具有一个函数：
SQL> desc dbms_xplan FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TABLE_NAME VARCHAR2 IN DEFAULT STATEMENT_ID VARCHAR2 IN DEFAULT FORMAT VARCHAR2 IN DEFAULT
而在Oracle10g中该Package的功能得到了极大的增强。
DBMS_XPLAN.DISPLAY可以被调用来返回执行计划，调用过程类似如下：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
值得注意的是，DBMS_XPLAN还能从存储SGA内的指针显示“实时”执行计划，通过查看V$SESSION视图，可以找到会话执行SQL的SQL ID，拥有了这个SQL ID之后可以通过DBMS_XPLAN.DISPLAY_CURSOR来获得Cursor所使用的执行计划。
此外DBMS_XPLAN.DISPLAY_AWR函数还可用来查询Oracle 10g的自动工作负载库(Automatic Workload Repository, AWR)获得的历史SQL语句，并显示它的执行计划。
下面我们通过Oracle10g中的测试应用来展示一下这个Package对于SQL执行计划的获取与展现。
首先通过EXPLAIN PLAN来生成执行计划：
SQL> EXPLAIN PLAN set statement_id = 'NO' FOR 2 SELECT * FROM emp WHERE empno=7788; Explained.
然后通过查询展现以上生成的执行计划：
SQL> SELECT plan_table_output
2 FROM TABLE( DBMS_XPLAN.DISPLAY('PLAN_TABLE','NO','ALL') );
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
28 rows selected.以上功能可以用于显示已知SQL的执行计划，但是很多时候我们需要直接从数据库中获得其他应用SQL的执行计划。从Oracle9i开始，V$SQL_PLAN_STATISTICS、V$SQL_PLAN 视图被引入用于记录SQL的执行统计信息以及执行计划，但是在Oracle9i中，从以上视图获取执行计划通常需要自己手工编写脚本，实现起来较为复杂，在Oracle10g中新的增强被引入，DBMS_XPLAN.DISPLAY_CURSOR可以很容易的帮助我们实现以上需求，执行该功能需要对V$SESSION、V$SQL、V$SQL_PLAN 、V$SQL_PLAN_STATISTICS_ALL就有访问权限。此时使用scott用户进行，需要首先对SCOTT用户授权：
grant select on v_$session to scott; grant select on v_$sql_plan to scott; grant select on v_$sql to scott;
DISPLAY_CURSOR的参数需要如下：
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN DEFAULT CURSOR_CHILD_NO NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT
现在看看DISPLAY_CURSOR的输出：
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 1m225m1612xvg, child number 0
-------------------------------------
SELECT d.dname, SUM(e.sal) AS sum_sal FROM dept d,emp e WHERE
d.deptno = e.deptno GROUP BY d.dname
Plan hash value: 2006461124
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | HASH GROUP BY | | 14 | 672 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 672 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement在Oracle10g中，可以通过V$SESSION或V$SQL等视图来获取不同会话的SQL_ID以及SQL_CHILD_NUMBER来获得其SQL执行计划：
SQL> select sid,username,sql_id,sql_child_number 2 from v$session where sql_id is not null; SID USERNAME SQL_ID SQL_CHILD_NUMBER ---------- ------------------------------ ------------- ---------------- 360 SMSNP bgg2p17bx7y1v 2 372 SYS 6zn53xvm7zth4 0 386 SMSNP 1ug8zqhw906tb 0 393 SMSNP fx3kcsrcm7hcb 106 396 SMSNP 4t47xyz571hrh 90 401 SMSNP 1ug8zqhw906tb 0 405 SYS 65vuzhm491wk9 1 422 SMSNP 1bkv8cyq1c9f1 1 429 4gd6b1r53yt88 0
注意，某些SQL的执行计划可能老化无法获取，以下是通过v$session信息获得的一个SQL执行计划：
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bgg2p17bx7y1v',2,'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID bgg2p17bx7y1v, child number 2
-------------------------------------
SELECT COUNT(*) FROM "NP_IO_MT" "A1" WHERE "A1"."MSGDATE">SYSDATE@!-.0104166666666666666666666666666666666667 AND
"A1"."HYSERVICECODE"='QWOZQX' AND EXISTS (SELECT 0 FROM "NP_IO_MO" "A2" WHERE "A2"."PHONE"="A1"."PHONE" AND
"A2"."MSGDATE">SYSDATE@!-.0104166666666666666666666666666666666667 AND "A2"."HYSERVICECODE"='HZXDBM')
Plan hash value: 4119176645
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 56 | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 56 | 9 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 29 | 5 (0)| 00:00:01 | KEY | 19 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| NP_IO_MT | 1 | 29 | 5 (0)| 00:00:01 | KEY | 19 |
|* 5 | INDEX RANGE SCAN | IND_IO_MT_MSGDATE | 6 | | 3 (0)| 00:00:01 | KEY | 19 |
| 6 | PARTITION RANGE ITERATOR | | 1 | 27 | 4 (0)| 00:00:01 | KEY | 19 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| NP_IO_MO | 1 | 27 | 4 (0)| 00:00:01 | KEY | 19 |
|* 8 | INDEX RANGE SCAN | IND_IO_MO_MSGDATE | 9 | | 2 (0)| 00:00:01 | KEY | 19 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / A1@SEL$1
5 - SEL$5DA710D3 / A1@SEL$1
7 - SEL$5DA710D3 / A2@SEL$2
8 - SEL$5DA710D3 / A2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A1"."HYSERVICECODE"='QWOZQX')
5 - access("A1"."MSGDATE">SYSDATE@!-.0104166666666666666666666666666666666667)
7 - filter(("A2"."HYSERVICECODE"='HZXDBM' AND "A2"."PHONE"="A1"."PHONE"))
8 - access("A2"."MSGDATE">SYSDATE@!-.0104166666666666666666666666666666666667)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "A1"."PHONE"[VARCHAR2,40]
4 - "A1"."PHONE"[VARCHAR2,40]
5 - "A1".ROWID[ROWID,10]
8 - "A2".ROWID[ROWID,10]3.通过AWR获取SQL执行计划
前面介绍的DBMS_XPLAN包还有另外一个功能，通过dbms_xplan.display_awr函数来获取AWR中的SQL执行计划。这个函数的主要参数需要是SQL_ID:
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN PLAN_HASH_VALUE NUMBER(38) IN DEFAULT DB_ID NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT
通过AWR生成的报告关于SQL部分都会包含SQL_ID一项内容，通过这个SQL_ID就可以查询AWR中的SQL执行计划，以下是Oracle10g AWR报告的一个输出片段：
Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- 9 2 1,083 0.0 6.9 1dbqwa8xts1g3 insert into HY_SIMULATESRC (DATTIMESTAMP, VC2SRCDESC, VC2MOBILE, VC2IMSI, VC2MOB ILEIP, VC2MEMO, VC2PLANKEY, VC2SRCTYPE, VC2UA, VC2PROVID, NUMGUID) values (:1, : 2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
获得SQL的SQL_ID，就可以通过DBMS_XPLAN来输出执行计划，以下输出的SQL具有多个子指针，执行计划各不相同，在使用绑定变量的情况下，Oracle数据库也会通过绑定变量Peeking来获得更为准确的执行：
SQL> select * from table(dbms_xplan.display_awr('072t81cu41xfj'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 072t81cu41xfj
--------------------
SELECT DECODE(COUNT(*), 0, 1, 0) FROM MGMT_SEVERITY WHERE TARGET_GUID = :B3 AND
METRIC_GUID = :B2 AND KEY_VALUE = :B1
Plan hash value: 356059170
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
| 2 | INDEX RANGE SCAN| SEVERITY_PRIMARY_KEY | 1 | 41 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SQL_ID 072t81cu41xfj
--------------------
SELECT DECODE(COUNT(*), 0, 1, 0) FROM MGMT_SEVERITY WHERE TARGET_GUID = :B3 AND
METRIC_GUID = :B2 AND KEY_VALUE = :B1
Plan hash value: 2975161209
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
| 2 | INDEX FAST FULL SCAN | SEVERITY_PRIMARY_KEY | 1966 | 78640 | 24 (0)| 00:00:01|
-----------------------------------------------------------------------------------------已选择30行。
通过dbms_xplan.display_awr函数获取的SQL执行计划来自dba_hist_sql_plan视图，通过历史数据记录，甚至一些被老化的SQL执行计划仍然可以被查询到。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
