上篇中，我们介绍了几种获取执行计划的方法。本篇我们继续探讨其他获取到执行计划详细信息的方法。
4、从shared_pool中直接抽取执行计划
我们执行过的SQL，在Oracle中会将执行计划缓存一段时间，就在shared_pool的library cache中。这是真实使用的执行计划，我们可以使用手段加以抽取展现。
在shared_pool中，执行计划主要是以shared cursor方式进行保存，也就是父子游标方式。一个父游标parent cursor联动若干child cursor，每个child cursor对应一个单独的执行计划。
SQL> select /*+ exp_demo */* from scott.emp where empno=7323;
未选定行
从v$sql和v$sqlarea中获取到对应的计划。
//从v$sqlarea中获取到父游标；
SQL> select substr(sql_text,1,20), sql_id, address, version_count,executions from v$sqlarea where sql_text like 'select /*+ exp_demo */*%';
SUBSTR(SQL_TEXT,1,20) SQL_ID ADDRESS VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- -------- ------------- ----------
select /*+ exp_demo a78616x8uja32 2254266C 1 1
//从v$sql中获取到子游标；
SQL> select sql_id, child_number, executions from v$sql where sql_id='a78616x8uja32';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
a78616x8uja32 0 1
获取到sql_id和child_number之后，就可以使用dbms_xplay.display_cursor方法进行抽取。
SQL> select * from table(dbms_xplan.display_cursor('a78616x8uja32',0));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID a78616x8uja32, child number 0
-------------------------------------
select /*+ exp_demo */* from scott.emp where empno=7323
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7323)
已选择19行。
这种方式获取到的执行计划是最准确的执行计划。同样display_cursor也是支持format参数。当使用绑定变量时，还可以抽取出bind peeking的变量取值。
5、从AWR报告库中获取执行计划
直接从shared_pool中获取执行计划，虽然是最准确的但存在实效的问题。如果执行一段时间之后，执行计划shared cursor会由于LRU算法被剔除shared_pool。或者因为环境变化，让执行计划重新生成。所以，很多时候，我们需要更多时间进行SQL分析。
这时候我们就需要AWR（Automatic Workload Repository）的镜像snapshot功能。每个固定时间，Oracle AWR会将系统状况已快照的方式保存下来。这个过程中，也就会将这些shared pool执行计划保存下来。
我们通常使用AWR报告时，发现问题SQL的情况。如下：
我们发现sql_id=’ 4x74bc7r4npq4’的SQL存在执行时间长的问题。此时，该SQL可能已经被置换出SGA，所以可以使用dbms_xplan的display_awr方法抽取AWR存储获取执行计划。
SQL> select * from table(dbms_xplan.display_awr('4x74bc7r4npq4',format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4x74bc7r4npq4
--------------------
select ticket0_.SEQ_NUMBER as SEQ1_324_, ticket0_.VERSION as
VERSION324_, ticket0_.CREATE_DATE as CREATE3_324_, ticket0_.CREATE_USER
（篇幅原因，有删节……）
ticket0_.WEB_SALE_I as WEB121_324_ from BSD_TICKET ticket0_ where
TDNR=:1 and TACN=:2
Plan hash value: 3282229029
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11382 (100)| |
| 1 | TABLE ACCESS FULL| BSD_TICKET | 1 | 582 | 11382 (1)| 00:02:17 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TICKET0_@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TICKET0_"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position): //绑定变量时用的bind peeking值；
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): '1661663695'
2 - :2 (VARCHAR2(30), CSID=873): '618'
97 rows selected
6、使用10046事件跟踪
传统获取执行计划的方法，是使用10046跟踪事件。通过开启事件跟踪，生成跟踪trace文件。最后通过分析跟踪文件，定位到真实的执行计划。分别按照如下步骤完成：
ü 开启10046跟踪事件，执行诊断SQL
SQL> alter session set events='10046 trace name context forever, level 12';
Session altered
SQL> select * from scott.emp where empno=7323;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
ü 定位跟踪文件
由于使用的是Oracle 10g，笔者使用自定义的函数进行获取。
SQL> select f_get_trace_name from dual;
F_GET_TRACE_NAME
--------------------------------------------------------------------------------
C:\TOOL\ORACLE\ORACLE\PRODUCT\10.2.0\ADMIN\OTS\UDUMP\ots_ora_3388.trc
说明：如果是在Oracle 11g，可以检索视图v$diag_info来获取当前会话的诊断文件名称。
ü 使用tkprof工具进行跟踪文件处理
由于.trc文件大都是粗格式文档，不宜于阅读。所以可以使用tkprof工具对跟踪文件进行处理。
D:\>tkprof ots_ora_3388.trc result.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期三 8月 10 10:04:34 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
从处理结果文件result.txt中，我们可以找到对应SQL的执行计划信息。
select *
from
scott.emp where empno=7323
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.02 0 1 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID EMP (cr=1 pr=0 pw=0 time=45 us)
0 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=30 us)(object id 51152)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.03 0.03
该种方法比较复杂，但是获取到的信息很精确。同时，也可以获取到关于SQL处理三阶段（Parse、Execute和Fetch）的相应处理内容。
7、结论
SQL执行计划是我们研究Oracle、研究Oracle优化器的一个重要手段工具。本篇系列关注如何获取SQL的执行计划，列举出六种详细的手段和方法。不同方法均有其优缺点和适应环境，选择正确的方法才可以起到最好的效果。
扫一扫
248
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
