对线上SQL 的监控其中有个比较重要的工作就是：执行计划的监控。
有时SQL 执行计划的变更，可能会对原有应用产生比较大的影响。
下面操作找出数据库里面发生sql 执行计划的步骤：

[oracle@LinuxServer ls]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 13 10:09:04 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> @unstable_plans
Enter value for min_stddev:
old 20: where norm_stddev > nvl(to_number('&min_stddev'),2)
new 20: where norm_stddev > nvl(to_number(''),2)
Enter value for min_etime:
old 21: and max_etime > nvl(to_number('&min_etime'),.1)
new 21: and max_etime > nvl(to_number(''),.1)

SQL_ID        SUM(EXECS)   MIN_ETIME   MAX_ETIME   NORM_STDDEV
------------- ---------- ----------- ----------- -------------
616xum7163qaq        142         .85        3.38        2.0984
fknsm3cu34zan        282         .44        2.20        2.8386
a9pp0xdc1j3bk       4367         .20        1.04        2.9221
gjm43un5cy843        110         .22        1.18        3.1614
c987y4kxdxwb7      37318         .04         .34        5.7862
95wuhmmw3fcxy       2409         .18        1.69        5.8245
09tv9580k7ctt     194220        1.16       14.38        8.0530
f26rxz9ds94wv      12226         .06        1.92       21.8603
8f1sjvfxuup9w         89         .05        2.05       23.9245
5cpfvg72t1mqc        107         .03        2.63       35.6640
39gy1utb0d95n        191        1.95      143.89       51.5816
fd6u64jjvt1ps       3163         .04        3.39       52.6453
09gnjsgq2v4q8        276         .03        3.84      102.2798
5cb2z4hjhpkyf        519         .00        1.39      319.4673
14 rows selected.

SQL> @find_sql.sql

Enter value for sql_text:
Enter value for sql_id: fd6u64jjvt1ps
SQL_ID         CHILD PLAN_HASH        EXECS         ETIME     AVG_ETIME USERNAME      SQL_TEXT
------------- ------ ---------- ------------ ------------- ------------- ------------- -----------------------------------------
fd6u64jjvt1ps     25 3612756989          781         75.40           .10 WG_XXX           select count(t.BUSI_NO) as chkSuccCount,
fd6u64jjvt1ps     24 3612756989           19          1.53           .08    WG_XXX     select count(t.BUSI_NO) as chkSuccCount,
fd6u64jjvt1ps     23 3774165794            6         16.16          2.69 WG_XXX           select count(t.BUSI_NO) as chkSuccCount,
fd6u64jjvt1ps     22 3612756989          477         17.39          .04 WG_XXX      select count(t.BUSI_NO) as chkSuccCount,
fd6u64jjvt1ps     21 3612756989            1           .01           .01      WG_XXX           select count(t.BUSI_NO) as chkSuccCount,
fd6u64jjvt1ps     20 3612756989            1           .01           .01    WG_XXX           select count(t.BUSI_NO) as chkSuccCount,

SQL> @awr_plan_change.sql

Enter value for sql_id: fd6u64jjvt1ps
SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
     23363      1 06-OCT-15 02.00.33.521 AM      fd6u64jjvt1ps      3612756989        1,454         .036          583.1
     23459      1 10-OCT-15 02.00.18.939 AM      fd6u64jjvt1ps      3774165794           32        3.393       83,641.8
     23459      1 10-OCT-15 02.00.18.939 AM      fd6u64jjvt1ps      3612756989        1,677         .053     424.7

SQL> @dplan_awr.sql

Enter value for sql_id: fd6u64jjvt1ps
Enter value for plan_hash_value: 3612756989
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fd6u64jjvt1ps
--------------------
select count(t.BUSI_NO) as chkSuccCount, nvl(sum(abs(t.trans_amt)), 0)

Plan hash value: 3612756989

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name                | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |       |   274 (100)|          |       |       |
|   1 | UNION-ALL                           |                     |       |       |            |          |       |       |
|   2 |   SORT AGGREGATE                     |                     |     1 |    35 |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE            |                     |     1 |    35 |   137   (0)| 00:00:02 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| A |     1 |    35 |   137   (0)| 00:00:02 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                | IDX_A    |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
|   6 |   SORT AGGREGATE                     |                     |     1 |    35 |            |          |       |       |
|   7 |    PARTITION RANGE SINGLE            |                     |     1 |    35 |   137   (0)| 00:00:02 |   KEY |   KEY |
|   8 |     TABLE ACCESS BY LOCAL INDEX ROWID| B |     1 |    35 |   137   (0)| 00:00:02 |   KEY |   KEY |
|   9 |      INDEX RANGE SCAN                | IDX_B |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

unstable_plans.sql

select *

from (select sql_id,

sum(execs),

min(avg_etime) min_etime,

max(avg_etime) max_etime,

stddev_etime / min(avg_etime) norm_stddev

from (select sql_id,

plan_hash_value,

execs,

avg_etime,

stddev(avg_etime) over(partition by sql_id) stddev_etime

from (select sql_id,

plan_hash_value,

sum(nvl(executions_delta, 0)) execs,

(sum(elapsed_time_delta) /

decode(sum(nvl(executions_delta, 0)),

0,

1,

sum(executions_delta)) / 1000000) avg_etime

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

group by sql_id, plan_hash_value))

group by sql_id, stddev_etime)

where norm_stddev > nvl(to_number('&min_stddev'), 2)

and max_etime > nvl(to_number('&min_etime'), .1)

order by norm_stddev

轻松搞定～

ITPub博客

查看数据库执行计划发生变化的方法和脚本

最新文章