系统库按时间生成IO TOP SQL语句(08点-11点)-优快云博客

本文提供了一个针对Oracle数据库在特定时间段内的性能分析查询语句。通过分析从08:00到11:00之间的历史统计数据，展示了如何计算每条SQL语句的磁盘读取次数、执行次数、每次执行的平均读取量、CPU时间和总运行时间等关键指标，并按磁盘读取次数降序排列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select *
from (select sqt.dskr "Physical Reads",
sqt.exec,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) "Reads per Exec",
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'08')
AND E.SNAP_ID =
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'11')
AND B.DBID = 911569205
AND E.DBID = 911569205
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') "%Total",
nvl((sqt.cput / 1000000), to_number(null)) "CPU Time (s)",
nvl((sqt.elap / 1000000), to_number(null)) "Elapsed Time (s)",
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) "SQL Module",
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) "SQL Text"
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 911569205
and instance_number = 1
and snap_id >
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'08')
and snap_id <=
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'11')
group by sql_id)sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 911569205
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'08')
AND E.SNAP_ID =
(select snap_id
from DBA_HIST_SNAPSHOT
where to_char(end_interval_time, 'yyyymmddhh24') =to_char(sysdate,'yyyymmdd')||'11')
AND B.DBID = 911569205
AND E.DBID = 911569205
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads')>0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10 or "%Total" > 1)