脚本:监控数据库中的活跃用户及其运行的SQL

最新推荐文章于 2025-03-11 10:04:01 发布

sunnyraty

最新推荐文章于 2025-03-11 10:04:01 发布

阅读量585

点赞数

本文介绍了一种使用 Oracle SQL 查询来监控和分析活动会话及其 SQL 执行情况的方法，通过展示如何获取有关 OS 用户、Oracle 用户、进程 ID、会话 ID 和序列号等信息，帮助 DBA 或开发者快速定位性能瓶颈。

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

set linesize 120 pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
col event  for a30
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid     b1,
       b.osuser   c1,
       b.username c2,
       b.sid      b2,
       b.serial#  b3,
       b.event,
       a.sql_text
  from v$sqltext a, v$session b, v$process c
 where a.address = b.sql_address
      --   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
      --                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid, a.hash_value, a.piece
/      		  

REM TOP SESSION

with sessions as
 (select /*+ materialize */
   sess.inst_id,
   sess.sid,
   sess.serial#,
   sess.username,
   sess.module,
   sess.program,
   stat.value cpu_used_by_this_session,
   i.physical_reads,
   i.block_gets,
   sess.command,
   sess.status,
   sess.lockwait,
   decode(sess.sql_hash_value, 0, sess.prev_hash_value, sess.sql_hash_value) sql_hash_value,
   decode(sess.sql_address, '00', sess.prev_sql_addr, sess.sql_address) sql_address
    from gv$sesstat stat, gv$session sess, gv$sess_io i
   where stat.statistic# =
         (select statistic#
            from v$statname
           where name = 'CPU used by this session')
     and stat.sid = sess.sid
     and stat.inst_id = sess.inst_id
     and (stat.value > 100 or i.physical_reads > 100 or i.block_gets > 100)
     and sess.username is not null
     and i.sid = sess.sid
     and i.inst_id = sess.inst_id),
sqlarea as
 (select inst_id, sql_fulltext sql_text, hash_value, address from gv$sqlarea)
select *
  from sessions, sqlarea
 where sessions.inst_id = sqlarea.inst_id and sessions.sql_hash_value = sqlarea.hash_value and sessions.sql_address = sqlarea.address
 order by cpu_used_by_this_session desc
/