在数据库运维中，用户抱怨他们的应用程序速度较慢。他们没有提供具体细节，但他们“感觉”它运行得更慢了。听起来很熟悉？

每隔一段时间，我就会收到这样的请求：“我如何才能发现我的数据库中的某些SQL是否随着时间的推移而变得更糟？”

很难处理一些模糊的问题，比如“随着时间的推移，找到性能更差或更好的SQL”。如果我们简化该问题，其实就是在数据库中找到记录每一个sql 每次的执行时间，“每次执行的运行时间”，那么我们可以利用下面的sql很容易地生成如下脚本，该脚本返回的SQL语句就是随着时间的推移而经历了退化或改进。它对“每次执行的运行时间”与其历史执行SQL的中位数之间的比率使用线性回归。

然后，如果你怀疑你有一些SQL可能已经退化，需要一个人来识别它们，你可以试试下面的脚本。

----------------------------------------------------------------------------------------

--

-- File name:   sql_performance_changed.sql

--

-- Purpose:    列出随时间SQL语句性能变化的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
;

一旦您获得了上面这个脚本的输出，您就可以使用下面的脚本来实际列出感兴趣的SQL语句之一的时间序列：

----------------------------------------------------------------------------------------

--

-- File name:   one_sql_time_series.sql

--

-- Purpose:     Performance History for one SQL

--

--

-- Parameters:  SQL_ID

--

---------------------------------------------------------------------------------------

--

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
;

ora 4

上一篇【ORA】Oracle：ORA-27125: unable to create shared memory segment 下一篇【ORA】查询指定时间范围内的SQL历史执行计划

查找SQL性能随时间变化的SQL