查看Oracle 数据库中的长事务

最新推荐文章于 2022-02-15 18:07:42 发布

转载最新推荐文章于 2022-02-15 18:07:42 发布 · 1w 阅读

文章标签：

#detect #long transaction

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文深入探讨了如何通过SQL查询优化技术来解决长运行事务问题，提供了具体示例和执行效果分析。

本文是转载，原文链接：

http://www.hvr-software.com.cn/about/blog/5-long-running-transactions

set linesize 200
set pagesize 5000
col transaction_duration format a45

with transaction_details as
( select inst_id
  , ses_addr
  , sysdate - start_date as diff
  from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
             || ' days, '
             || to_char(trunc(mod(t.diff * 24,24)))
             || ' hours, '
             || to_char(trunc(mod(t.diff * 24 * 60,24)))
             || ' minutes, '
             || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
             || ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
/

如上语句执行之后的效果如下：