根据等待事件查询后台会话的信息(oem->excel)

最新推荐文章于 2025-07-26 16:22:18 发布

最新推荐文章于 2025-07-26 16:22:18 发布 · 96 阅读

本文深入探讨了SQL查询技术在历史数据中的应用，包括如何从V$ACTIVE_SESSION_HISTORY视图和v$sqlarea视图中筛选出特定类型的活动记录，并通过联合查询实现数据整合。文章详细解释了查询条件的设定，如筛选'Application'类等待事件，以及如何通过SID关联获取更多上下文信息。此技术对于理解数据库活动模式和优化性能至关重要。

select ash.session_id,
ash.session_serial#,
ash.instance,
ash.program,
s.machine,
s.username,
s.osuser,
ash.event,
ash.wait_class,
sql.sql_text
from (select distinct session_id,
session_serial#,
SYS_CONTEXT('USERENV', 'INSTANCE_NAME') instance,
sql_id,
event,
wait_class,
program,
module,
action
from v$active_session_history
where wait_class = 'Application'
and to_char(sample_time, 'hh24mi') =
to_char(sysdate - 1 / 24 / 60, 'hh24mi')) ash,
v$sqlarea sql,
v$session s
where ash.sql_id = sql.sql_id
and ash.session_id = s.sid(+)
union all
select ash.session_id,
ash.session_serial#,
ash.instance,
ash.program,
s.machine,
s.username,
s.osuser,
ash.event,
ash.wait_class,
sql.sql_text
from (select distinct session_id,
session_serial#,
SYS_CONTEXT('USERENV', 'INSTANCE_NAME') instance,
sql_id,
event,
wait_class,
program,
module,
action
from v$active_session_history@dblink
where wait_class = 'Application'
and to_char(sample_time, 'hh24mi') =
to_char(sysdate - 1 / 24 / 60, 'hh24mi')) ash,
v$sqlarea@dblink sql,
v$session@dblink s
where ash.sql_id = sql.sql_id
and ash.session_id = s.sid(+)