找出性能变化较大的SQL:
SELECT st2.sql_id,
st2.plan_hash_value,
st_long.plan_hash_value l_plan_hash_value,
st2.cpu_mins,
st_long.cpu_mins l_cpu_mins,
st2.ela_mins,
st_long.ela_mins l_ela_mins,
st2.executions,
st_long.executions l_executions,
st2.crows,
st_long.crows l_crows,
st2.cpu_mins_per_row,
st_long.cpu_mins_per_row l_cpu_mins_per_row
FROM (SELECT st.sql_id,
st.plan_hash_value,
SUM(st.executions_delta) executions,
SUM(st.rows_processed_delta) crows,
trunc(SUM(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
decode(SUM(st.rows_processed_delta),
0,
0,
(SUM(st.cpu_time_delta) / 1000000 / 60) /
SUM(st.rows_processed_delta)) cpu_mins_per_row,
trunc(SUM(st.elapsed_time_delta) / 1000000 / 60) ela_mins
FROM dba_hist_sqlstat st
WHERE 1 = 1
AND (st.cpu_time_delta != 0 OR st.rows_processed_delta != 0)
GROUP BY st.sql_id, st.plan_hash_value) st2,
(SELECT st.sql_id,
st.plan_hash_value,
SUM(st.executions_delta) executions,
SUM(st.rows_processed_delta) crows,
trunc(SUM(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
decode(SUM(st.rows_processed_delta),
0,
0,
(SUM(st.cpu_time_delta) / 1000000 / 60) /
SUM(st.rows_processed_delta)) cpu_mins_per_row,
trunc(SUM(st.elapsed_time_delta) / 1000000 / 60) ela_mins
FROM dba_hist_sqlstat st
WHERE 1 = 1
AND (st.cpu_time_delta != 0 OR st.rows_processed_delta != 0)
HAVING trunc(SUM(st.cpu_time_delta) / 1000000 / 60) > 10
GROUP BY st.sql_id, st.plan_hash_value) st_long
WHERE 1 = 1
AND st2.sql_id = st_long.sql_id
AND st_long.cpu_mins_per_row /
decode(st2.cpu_mins_per_row, 0, 1, st2.cpu_mins_per_row) > 2
ORDER BY l_cpu_mins DESC,
st2.sql_id,
st_long.cpu_mins DESC,
st2.plan_hash_value;
找出消耗CPU/IO高的SQL:
SELECT *
FROM (SELECT s.sql_id,
SUM(s.cpu_time_delta),
SUM(s.disk_reads_delta),
COUNT(*)
FROM dba_hist_sqlstat s, dba_hist_snapshot p, dba_hist_sqltext t
WHERE 1 = 1
AND s.snap_id = p.snap_id
AND s.sql_id = t.sql_id
AND extract(hour FROM p.end_interval_time) BETWEEN 8 AND 16
AND t.command_type != 47 -- Exclude pl / SQL blocks from output
AND p.end_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
GROUP BY s.sql_id
ORDER BY SUM(s.cpu_time_delta) DESC)
WHERE rownum < 11;
/
SELECT st2.sql_id,
st2.plan_hash_value,
st_long.plan_hash_value l_plan_hash_value,
st2.cpu_mins,
st_long.cpu_mins l_cpu_mins,
st2.ela_mins,
st_long.ela_mins l_ela_mins,
st2.executions,
st_long.executions l_executions,
st2.crows,
st_long.crows l_crows,
st2.cpu_mins_per_row,
st_long.cpu_mins_per_row l_cpu_mins_per_row
FROM (SELECT st.sql_id,
st.plan_hash_value,
SUM(st.executions_delta) executions,
SUM(st.rows_processed_delta) crows,
trunc(SUM(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
decode(SUM(st.rows_processed_delta),
0,
0,
(SUM(st.cpu_time_delta) / 1000000 / 60) /
SUM(st.rows_processed_delta)) cpu_mins_per_row,
trunc(SUM(st.elapsed_time_delta) / 1000000 / 60) ela_mins
FROM dba_hist_sqlstat st
WHERE 1 = 1
AND (st.cpu_time_delta != 0 OR st.rows_processed_delta != 0)
GROUP BY st.sql_id, st.plan_hash_value) st2,
(SELECT st.sql_id,
st.plan_hash_value,
SUM(st.executions_delta) executions,
SUM(st.rows_processed_delta) crows,
trunc(SUM(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
decode(SUM(st.rows_processed_delta),
0,
0,
(SUM(st.cpu_time_delta) / 1000000 / 60) /
SUM(st.rows_processed_delta)) cpu_mins_per_row,
trunc(SUM(st.elapsed_time_delta) / 1000000 / 60) ela_mins
FROM dba_hist_sqlstat st
WHERE 1 = 1
AND (st.cpu_time_delta != 0 OR st.rows_processed_delta != 0)
HAVING trunc(SUM(st.cpu_time_delta) / 1000000 / 60) > 10
GROUP BY st.sql_id, st.plan_hash_value) st_long
WHERE 1 = 1
AND st2.sql_id = st_long.sql_id
AND st_long.cpu_mins_per_row /
decode(st2.cpu_mins_per_row, 0, 1, st2.cpu_mins_per_row) > 2
ORDER BY l_cpu_mins DESC,
st2.sql_id,
st_long.cpu_mins DESC,
st2.plan_hash_value;
找出消耗CPU/IO高的SQL:
SELECT *
FROM (SELECT s.sql_id,
SUM(s.cpu_time_delta),
SUM(s.disk_reads_delta),
COUNT(*)
FROM dba_hist_sqlstat s, dba_hist_snapshot p, dba_hist_sqltext t
WHERE 1 = 1
AND s.snap_id = p.snap_id
AND s.sql_id = t.sql_id
AND extract(hour FROM p.end_interval_time) BETWEEN 8 AND 16
AND t.command_type != 47 -- Exclude pl / SQL blocks from output
AND p.end_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
GROUP BY s.sql_id
ORDER BY SUM(s.cpu_time_delta) DESC)
WHERE rownum < 11;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22818880/viewspace-2077075/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22818880/viewspace-2077075/