ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 介绍几种获取SQL执行计划的方法(下)

介绍几种获取SQL执行计划的方法(下)

原创 Linux操作系统 作者:realkid4 时间:2011-08-11 19:19:08 0 删除 编辑

 

上篇中,我们介绍了几种获取执行计划的方法。本篇我们继续探讨其他获取到执行计划详细信息的方法。

 

4、从shared_pool中直接抽取执行计划

 

我们执行过的SQL,在Oracle中会将执行计划缓存一段时间,就在shared_poollibrary cache中。这是真实使用的执行计划,我们可以使用手段加以抽取展现。

 

shared_pool中,执行计划主要是以shared cursor方式进行保存,也就是父子游标方式。一个父游标parent cursor联动若干child cursor,每个child cursor对应一个单独的执行计划。

 

 

SQL> select /*+ exp_demo */* from scott.emp where empno=7323;

未选定行

 

 

v$sqlv$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_idchild_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分析。

 

这时候我们就需要AWRAutomatic Workload Repository)的镜像snapshot功能。每个固定时间,Oracle AWR会将系统状况已快照的方式保存下来。这个过程中,也就会将这些shared pool执行计划保存下来。

 

我们通常使用AWR报告时,发现问题SQL的情况。如下:

 

 

我们发现sql_id=’ 4x74bc7r4npq4’SQL存在执行时间长的问题。此时,该SQL可能已经被置换出SGA,所以可以使用dbms_xplandisplay_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处理三阶段(ParseExecuteFetch)的相应处理内容。

 

 

7、结论

 

SQL执行计划是我们研究Oracle、研究Oracle优化器的一个重要手段工具。本篇系列关注如何获取SQL的执行计划,列举出六种详细的手段和方法。不同方法均有其优缺点和适应环境,选择正确的方法才可以起到最好的效果。

 

pic.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-704700/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7754011