Oracle慢sql监控脚本

Oracle慢sql监控脚本

公司有个很老的B2B项目，数据库用的是Oracle。最近因为业务系统与财务系统交互中间层缓冲区发现读取与写入超时。
下面是监控Oracle慢sql监控脚本的两种处理方案。

#慢sql监控脚本方法一
select (s1.username || '@' || s1.machine || ' time: ' ||
       to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || ' ( SID=' || s1.sid || '-' ||
       s1.SERIAL# || ' ) is blocking ' || s2.username || '@' || s2.machine ||
       ' ( SID=' || s2.sid || '-' || s2.SERIAL# || ' ) ' || ' sql:' ||
       q.SQL_TEXT) as sqldesc
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  left join v$sql q
    on s2.sql_address = q.address
 where s1.sid = l1.sid
   and s2.sid = l2.sid
   and l1.BLOCK = 1
   and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2;

#慢sql监控脚本方法二
select to_char(sa.last_active_time, 'hh24:mi:ss') time,
       se.osuser,
       se.username,
       se.sql_id,
       sa.sql_fulltext,
       sa.executions "执行次数",
       round(sa.ELAPSED_TIME / 1000000, 2) || 's' "总执行时间",
       round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) || 's' "平均执行时间"
  from (select s.osuser, s.username, s.sql_id
          from v$session s
         where s.username in ('scott')--根据自己的需要取设置
           and s.sql_id is not null
         group by s.osuser, s.username, s.sql_id) se
  left join v$sqlarea sa
    on se.sql_id = sa.sql_id
 where sa.executions > 0
   and round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) > 0
   and sa.last_active_time > trunc(sysdate);