在数据库运维中，用户抱怨他们的应用程序速度较慢。他们没有提供具体细节，但他们“感觉”它运行得更慢了。听起来很熟悉？
每隔一段时间，我就会收到这样的请求：“我如何才能发现我的数据库中的某些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,