博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
转 oracle 监控执行计划突然变化
阅读量:5859 次
发布时间:2019-06-19

本文共 15285 字,大约阅读时间需要 50 分钟。

 

 

 

##############1.

https://carlos-sierra.net/2014/11/02/finding-sql-with-performance-changing-over-time/

该sql 太复杂,执行时间太长,但是是很好参考资料。

Finding SQL with Performance changing over time

I upgraded my database a couple of weeks ago and now my users complain their application is slower. They do not provide specifics but they “feel” it is running slower. Sounds familiar?

Every once in a while I get a request that goes like this: “how can I find if some SQL on my database is performing worse over time?”

It is very hard to deal with the ambiguities of some problems like “finding SQL that performs worse or better over time”. But if you simplify the problem and consider for example “Elapsed Time per Execution”, then you can easily produce a script like the one below, which returns a small list of SQL statements that seem to experience either a regression or an improvement over time. It uses linear regression on the ratio between “Elapsed Time per Execution” and its Median per SQL.

Then, If you are suspecting you have some SQL that may have regressed and need a hand to identify them, you can try this script below. It is now part of a small collection of scripts that you can download and use for free out of the cscripts link on the right hand side of this page, under “Downloads”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
----------------------------------------------------------------------------------------
--
-- File name:   sql_performance_changed.sql
--
-- Purpose:     Lists SQL Statements with Elapsed Time per Execution changing over time
--
-- Author:      Carlos Sierra
--
-- Version:     2014/10/31
--
-- Usage:       Lists statements that have changed their elapsed time per execution over
--              some history.
--              Uses the ration between "elapsed time per execution" and the median of
--              this metric for SQL statements within the sampled history, and using
--              linear regression identifies those that have changed the most. In other
--              words where the slope of the linear regression is larger. Positive slopes
--              are considered "improving" while negative are "regressing".
--
-- Example:     @sql_performance_changed.sql
--
-- Notes:       Developed and tested on 11.2.0.3.
--
--              Requires an Oracle Diagnostics Pack License since AWR data is accessed.
--
--              To further investigate poorly performing SQL use sqltxplain.sql or sqlhc
--              (or planx.sql or sqlmon.sql or sqlash.sql).
--            
---------------------------------------------------------------------------------------
--
SPO sql_performance_changed.txt;
DEF days_of_history_accessed =
'31'
;
DEF captured_at_least_x_times =
'10'
;
DEF captured_at_least_x_days_apart =
'5'
;
DEF med_elap_microsecs_threshold =
'1e4'
;
DEF min_slope_threshold =
'0.1'
;
DEF max_num_rows =
'20'
;
 
SET
lin 200 ver
OFF
;
COL row_n
FOR
A2 HEA
'#'
;
COL med_secs_per_exec HEA
'Median Secs|Per Exec'
;
COL std_secs_per_exec HEA
'Std Dev Secs|Per Exec'
;
COL avg_secs_per_exec HEA
'Avg Secs|Per Exec'
;
COL min_secs_per_exec HEA
'Min Secs|Per Exec'
;
COL max_secs_per_exec HEA
'Max Secs|Per Exec'
;
COL plans
FOR
9999;
COL sql_text_80
FOR
A80;
 
PRO SQL Statements
with
"Elapsed Time per Execution"
changing over
time
 
WITH
per_time
AS
(
SELECT
h.dbid,
       
h.sql_id,
       
SYSDATE -
CAST
(s.end_interval_time
AS
DATE
) days_ago,
       
SUM
(h.elapsed_time_total) /
SUM
(h.executions_total) time_per_exec
  
FROM
dba_hist_sqlstat h,
       
dba_hist_snapshot s
 
WHERE
h.executions_total > 0
   
AND
s.snap_id = h.snap_id
   
AND
s.dbid = h.dbid
   
AND
s.instance_number = h.instance_number
   
AND
CAST
(s.end_interval_time
AS
DATE
) > SYSDATE - &&days_of_history_accessed.
 
GROUP
BY
       
h.dbid,
       
h.sql_id,
       
SYSDATE -
CAST
(s.end_interval_time
AS
DATE
)
),
avg_time
AS
(
SELECT
dbid,
       
sql_id,
       
MEDIAN(time_per_exec) med_time_per_exec,
       
STDDEV(time_per_exec) std_time_per_exec,
       
AVG
(time_per_exec)    avg_time_per_exec,
       
MIN
(time_per_exec)    min_time_per_exec,
       
MAX
(time_per_exec)    max_time_per_exec      
  
FROM
per_time
 
GROUP
BY
       
dbid,
       
sql_id
HAVING
COUNT
(*) >= &&captured_at_least_x_times.
   
AND
MAX
(days_ago) -
MIN
(days_ago) >= &&captured_at_least_x_days_apart.
   
AND
MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
),
time_over_median
AS
(
SELECT
h.dbid,
       
h.sql_id,
       
h.days_ago,
       
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
       
a.med_time_per_exec,
       
a.std_time_per_exec,
       
a.avg_time_per_exec,
       
a.min_time_per_exec,
       
a.max_time_per_exec
  
FROM
per_time h, avg_time a
 
WHERE
a.sql_id = h.sql_id
),
ranked
AS
(
SELECT
RANK () OVER (
ORDER
BY
ABS
(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago))
DESC
) rank_num,
       
t.dbid,
       
t.sql_id,
       
CASE
WHEN
REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0
THEN
'IMPROVING'
ELSE
'REGRESSING'
END
change,
       
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3) slope,
       
ROUND(
AVG
(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
       
ROUND(
AVG
(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
       
ROUND(
AVG
(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
       
ROUND(
MIN
(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
       
ROUND(
MAX
(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
  
FROM
time_over_median t
 
GROUP
BY
       
t.dbid,
       
t.sql_id
HAVING
ABS
(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT
LPAD(ROWNUM, 2) row_n,
       
r.sql_id,
       
r.change,
       
TO_CHAR(r.slope,
'990.000MI'
) slope,
       
TO_CHAR(r.med_secs_per_exec,
'999,990.000'
) med_secs_per_exec,
       
TO_CHAR(r.std_secs_per_exec,
'999,990.000'
) std_secs_per_exec,
       
TO_CHAR(r.avg_secs_per_exec,
'999,990.000'
) avg_secs_per_exec,
       
TO_CHAR(r.min_secs_per_exec,
'999,990.000'
) min_secs_per_exec,
       
TO_CHAR(r.max_secs_per_exec,
'999,990.000'
) max_secs_per_exec,
       
(
SELECT
COUNT
(
DISTINCT
p.plan_hash_value)
FROM
dba_hist_sql_plan p
WHERE
p.dbid = r.dbid
AND
p.sql_id = r.sql_id) plans,
       
REPLACE
((
SELECT
DBMS_LOB.SUBSTR(s.sql_text, 80)
FROM
dba_hist_sqltext s
WHERE
s.dbid = r.dbid
AND
s.sql_id = r.sql_id), CHR(10)) sql_text_80
  
FROM
ranked r
 
WHERE
r.rank_num <= &&max_num_rows.
 
ORDER
BY
       
r.rank_num
/
 
SPO
OFF
;

Once you get the output of this script above, you can use the one below to actually list the time series for one of the SQL statements of interest:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
----------------------------------------------------------------------------------------
--
-- File name:   one_sql_time_series.sql
--
-- Purpose:     Performance History for one SQL
--
-- Author:      Carlos Sierra
--
-- Version:     2014/10/31
--
-- Usage:       Script sql_performance_changed.sql lists SQL Statements with performance
--              improvement or regressed over some History.
--              This script one_sql_time_series.sql lists the Performance Time Series for
--              one SQL.
--
-- Parameters:  SQL_ID
--
-- Example:     @one_sql_time_series.sql
--
-- Notes:       Developed and tested on 11.2.0.3.
--
--              Requires an Oracle Diagnostics Pack License since AWR data is accessed.
--
--              To further investigate poorly performing SQL use sqltxplain.sql or sqlhc
--              (or planx.sql or sqlmon.sql or sqlash.sql).
--            
---------------------------------------------------------------------------------------
--
SPO one_sql_time_series.txt;
SET
lin 200 ver
OFF
;
 
COL instance_number
FOR
9999 HEA
'Inst'
;
COL end_time HEA
'End Time'
;
COL plan_hash_value HEA
'Plan|Hash Value'
;
COL executions_total
FOR
999,999 HEA
'Execs|Total'
;
COL rows_per_exec HEA
'Rows Per Exec'
;
COL et_secs_per_exec HEA
'Elap Secs|Per Exec'
;
COL cpu_secs_per_exec HEA
'CPU Secs|Per Exec'
;
COL io_secs_per_exec HEA
'IO Secs|Per Exec'
;
COL cl_secs_per_exec HEA
'Clus Secs|Per Exec'
;
COL ap_secs_per_exec HEA
'App Secs|Per Exec'
;
COL cc_secs_per_exec HEA
'Conc Secs|Per Exec'
;
COL pl_secs_per_exec HEA
'PLSQL Secs|Per Exec'
;
COL ja_secs_per_exec HEA
'Java Secs|Per Exec'
;
 
SELECT
h.instance_number,
       
TO_CHAR(
CAST
(s.end_interval_time
AS
DATE
),
'YYYY-MM-DD HH24:MI'
) end_time,
       
h.plan_hash_value,
       
h.executions_total,
       
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total),
'999,999,999,999'
) rows_per_exec,
       
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3),
'999,990.000'
) et_secs_per_exec,
       
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3),
'999,990.000'
) cpu_secs_per_exec,
       
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3),
'999,990.000'
) io_secs_per_exec,
       
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3),
'999,990.000'
) cl_secs_per_exec,
       
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3),
'999,990.000'
) ap_secs_per_exec,
       
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3),
'999,990.000'
) cc_secs_per_exec,
       
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3),
'999,990.000'
) pl_secs_per_exec,
       
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3),
'999,990.000'
) ja_secs_per_exec
  
FROM
dba_hist_sqlstat h,
       
dba_hist_snapshot s
 
WHERE
h.sql_id =
'&sql_id.'
   
AND
h.executions_total > 0
   
AND
s.snap_id = h.snap_id
   
AND
s.dbid = h.dbid
   
AND
s.instance_number = h.instance_number
 
ORDER
BY
       
h.sql_id,
       
h.instance_number,
       
s.end_interval_time,
       
h.plan_hash_value
/
 
SPO
OFF
;
 
 
########2
 
https://www.cnblogs.com/pangblog/p/3268586.html
1、问题
       通过调用dbms_xplan包中DISPLAY_AWR函数(DBMS_XPLAN.DISPLAY_AWR)可以从AWR数据中查看到SQL语句的历史执行计划,但是,DISPLAY_AWR函数的可传入参数只有四种,分别为:sql_id、plan_hash_value、db_id、format,缺少与时间范围相关的参数、也没有instance_number相关参数。
       使用dbms_xplan.display_awr的简单方式,一般为:
         SQL>select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410'));
 
        假如一套RAC环境,在8月5日的9:00—09:30时,2节点发生了CPU消耗非常高的情况,如果要分析是不是因为SQL_ID为bj75p9188y410 的语句的执行计划走错所致,这时,如果想用dbms_xplan.display_awr的简单查询方式来得到当时的执行计划,是无法实现的,那应该怎样查出该语句8月5日的9:00—09:30时第2节点上SQL_ID为bj75p9188y410的语句的执行计划是怎样子的呢?

 

2、分析
        如果通过DBMS_XPLAN.DISPLAY_AWR查看SQL语句的执行计划,将是从整个AWR数据库中查找,例如从AWR报告中查询SQL_ID为bj75p9188y410 的执行计划:
          SQL>select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410'))

 

        为了以简短的篇幅展示出从AWR中总共查到了几种执行计划,我将语句改写如下:
          SQL> select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410')) where      
               plan_table_output  like ('Planhash value%');
  结果如下:
  
1
  
Plan hash value: 6178145
2
Plan hash value: 2354817963
3
Plan hash value: 3990363694
        从此结果中看出,SQL_ID为bj75p9188y410 的语句在当前保留的AWR数据中存在三种执行计划。其中Plan hash value为3990363694的执行计划为错误的执行计划
实际生产环境中,在8月5日的9:00—09:30时,2节点发生了CPU消耗非常高的情况。现在就是要确认在此时间,该SQL_ID为bj75p9188y410的语句到底是使用哪个执行计划呢?

 

3、解决方法
3.1 、查到8月5日9:00—09:30的 snap_id
SQL>select dbid,snap_id,instance_number,begin_interval_time,end_interval_time
     fromdba_hist_snapshot
    wherebegin_interval_time >=to_date('2013-08-0509:00:00', 'yyyy-mm-dd hh24:mi:ss')
      andend_interval_time <=to_date('2013-08-0509:31:00', 'yyyy-mm-dd hh24:mi:ss')
结果为:
  
dbid
  
snap_id
instance_number
begin_interval_time
end_interval_time
19948XXXX2
33676
1
05-8月 -13 09.00.09.903
05-8月 -13 09.30.10.113
19948XXXX2
33676
2
05-8月 -13 09.00.09.786
05-8月 -13 09.30.10.502

 

3.2 、通过 dbms_xplan.display_awr   与包含snap_id、instance_number信息的视图关联得到8月5日9:00—09:30时SQL_ID为 bj75p9188y410   的执行计划:
  SQL>select a.* from (select distinct dbid,sql_id, plan_hash_value from dba_hist_sqlstat
          wheresql_id = 'bj75p9188y410'
            andsnap_id =   
33676
            andinstance_number = 2) b,          table ( dbms_xplan.display_awr ( db_id =>   19948XXXX2 , sql_id =>  b.sql_id , plan_hash_value => b.plan_hash_value ))  a;
结果如下:
SQL_ID bj75p9188y410
--------------------
select * from ( select distinct b.XXXX_id as
……   (为了信息脱敏,真实语句在此省略)
,'NLS_SORT=SCHINESE_XXXX'),b.XXXX_name  ) where rownum <= :1
 
Plan hash value: 3990363694
---------------------------------------------------------------------------------------------------------------------
| Id  |  Operation                                              | Name                                    | Rows     | Bytes | Cost (%CPU)| Time       |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT  STATEMENT                             |                                             |            |           |      315 (100)|                 |
|   1 |  COUNT STOPKEY                                  |                                             |            |           |                     |                 |
|   2 |   VIEW                                                   |                                                |         1 |    180 |        315   (2)| 00:00:29 |
|   3 |    SORT ORDER BY STOPKEY                 |                                           |         1 |    151 |        315   (2)| 00:00:29 |
|   4 |     HASH UNIQUE                                    |                                              |         1 |    151 |        314   (1)| 00:00:29 |
|   5 |      FILTER                                              |                                              |            |           |                      |                |
|   6 |       NESTED LOOPS OUTER                   |                                           |         1 |    151 |         313   (1)| 00:00:29 |
|   7 |        NESTED LOOPS                              |                                            |         1 |      86 |           35   (0)| 00:00:04 |
|   8 |         TABLE ACCESS BY INDEX ROWID | LB_T_XXXX_PROVIDER            |        1 |      61 |           34    (0)| 00:00:04 |
|   9 |          INDEX RANGE SCAN                     | IDX_LB_T_XXXX_PROVIDER_003 | 183 |         |             3    (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID | LA_XXXX                                 |         1 |      25 |            1    (0)| 00:00:01 |
|  11 |          INDEX UNIQUE SCAN                   | PK_LA_XXXX                           |         1 |           |            0    (0)|                 |
|  12 |        VIEW PUSHED PREDICATE            | LB_T_XXXX                             |         1 |      65 |         278   (1)| 00:00:26 |
|  13 |         MERGE JOIN OUTER                     |                                                |         1 |      64 |         278   (1)| 00:00:26 |
|  14 |          TABLE ACCESS BY INDEX ROWID| XXXX_SUPPLIER                     |         1 |      45 |        146    (0)| 00:00:14 |
|  15 |           INDEX FULL SCAN                       | PK_XXX_SUPPLIER                 |         1 |           |        145    (0)| 00:00:14 |
|  16 |          SORT JOIN                                  |                                                | 17998 |   333K|         132   (2)| 00:00:12 |
|  17 |           VIEW                                          |                                               | 17998 |   333K|         131   (1)| 00:00:12 |
|  18 |            SORT GROUP BY                       |                                                | 17998 |   544K|         131   (1)| 00:00:12 |
|  19 |             TABLE ACCESS FULL                | XXXX_SUPPLIER_CONTACT     | 30058 |   909K|         130   (0)| 00:00:12 |
---------------------------------------------------------------------------------------------------------------------
    此执行计划发生了严重的估算错误

 

###########3

没啥帮助

 

http://www.oracle-wiki.net/startscriptsplanmonitor

 

A Script to Monitor Plan Changes

Description

The following script can be used monitor and alert on plan changes. Details of its use can be found in the headers of the script.

Plan_Change_Alert.ksh

#!/bin/ksh -x############################################################################## Author      : Mark Ramsay## History       Date          Name          Reason#               ----          ----          ------#               18 May 2011   Mark Ramsay   Version 1.## Description## This script generates a report that shows if a SQL Plan has changed# for a given SQL ID.  It is useful for tracking plans for stubborn pieces# of SQL that may have a few good plans and the occasional bad plan.## The range of dates can be changed by setting SDS_range.  However,# this script would normally be scheduled each day the range will therefore# be 1.  i.e. Changes in the last 24hrs## The user should set the variable SDS_sqlid to the SQLID that is being# monitored.  The variable SDS_hash_values should be set to the# plan_hash_values that are acceptable for the given SQLID.## If a new plan_hash_value is generated for the given SQLID, then# the script will highlight this in the report.## The report can then be mailed out to individuals to look into the plan# change.############################################################################### Define Variables#export ORACLE_SID=MYSIDexport ORACLE_HOME=$(grep ^$ORACLE_SID: /var/opt/oracle/oratab |awk -F\: '{print $2}')export ORACLE_BASE=/u01/app/oracleexport PATH=.:/usr/local/bin:/bin:/usr/sbin:/usr/bin:$ORACLE_HOME/binSDS_date=`/bin/date '+%e_%B_%Y'|sed -e 's/ //'`SDS_sqlid="'SQLID1','SQLID2'"SDS_hash_values="HASH1,HASH2"SDS_mail_addr=myemail@mydomain.comSDS_range=1SDS_output=`$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <

转载于:https://www.cnblogs.com/feiyun8616/p/11065379.html

你可能感兴趣的文章
puppet连载六:创建测试模块test
查看>>
光宇游戏CTO沈崴:《问道》“2019”年度数字大服为何选择阿里云!
查看>>
Java 工程师成神之路 | 2019正式版
查看>>
用条码标签打印软件批量打印物料标签
查看>>
资本寒冬下的android面经
查看>>
ASP.NET CORE 中用单元测试测试控制器
查看>>
.NET中使用APlayer组件自制播放器
查看>>
Nginx 1.15.10 主线版发布,高性能 Web 服务器
查看>>
android 记一次解决键盘遮挡问题
查看>>
Dubbo 源码分析 - 集群容错之 Directory
查看>>
微服务设计指南
查看>>
搞定JVM垃圾回收就是这么简单
查看>>
Android开发之ViewPager简单使用
查看>>
使用rekit脚手架创建react项目
查看>>
LiveVideoStackCon讲师热身分享 ( 十三 ) —— Intel QSV技术在FFmpeg中的实现与使用
查看>>
July 算法习题 - 字符串2 + Leetcode 8,9
查看>>
他爱你就一定会来找你
查看>>
Java在手,天下我有!
查看>>
fastquery 1.0.66 发布,增加反 996 许可证
查看>>
如何在服务器上跑python程序
查看>>