0

获取SQL执行计划的方法

Eygle 2019-07-24
36
摘要:获取SQL执行计划的方法

在进行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执行计划仍然可以被查询到。


「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部