如何查看系统中IO资源消耗最高的SQL

我来答

匿名用户

如何查看系统中IO资源消耗最高的SQL

oracle

如何查看系统中IO资源消耗最高的SQL

我来答

添加附件

问题补充

4条回答

默认

最新

代野Tank

Oracle 的话直接看 AWR 更直观。

有用 0

猫瞳映月

使用以下SQL也可以查出来

select *
  from (select a.parsing_schema_name schema,
               a.sql_id,
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) "sql",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(Min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "total Time(Min)",
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G"
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
          -- and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24 ---开始时间
          -- and END_INTERVAL_TIME <= sysdate ---结束时间
         group by parsing_schema_name, a.sql_id
         order by 7 desc)
where rownum <= 50;

有用 1

愤怒的蜗牛

2022-11-23

yBmZlQzJ

2022-11-23

szrsu

2022-11-23

展开全部评论(2条)

virvle

IO高的会话查询 select ses.username, ses.sid, ses.status, max(decode(sta.name, 'session logical reads', sest.value)) as "LOG IO", max(decode(sta.name, 'physical reads', sest.value)) as "PHY IO", round(max(decode(sta.name, 'session logical reads', sest.value)) / (3600 * 24 * (sysdate - ses.logon_time)), 2) as "LOG IO/S", round(max(decode(sta.name, 'physical reads', sest.value)) / (3600 * 24 * (sysdate - ses.logon_time)), 2) as "PHY IO/S", trunc(60 * 24 * (sysdate - ses.logon_time)) as "Minutes" from v$session ses, v$sesstat sest, v$statname sta where ses.sid = sest.sid and sest.statistic# = sta.statistic# and sta.name in ('session logical reads', 'physical reads') and ses.username is not null and ses.status = 'ACTIVE' and username = '' --and ses.sid in (1159, 585, 584) group by ses.username, ses.sid, ses.status, ses.logon_time order by 7 desc

有用 0