查找占用latch严重的session
|
如果在v$latch_children中看到cache buffers chains SLEEP非常严重，如何找到时哪个SESSION造成的？ |
select ’Segment Header’ class,
a.segment_type,
a.segment_name,
a.partition_name
from dba_segments a, v$session_wait b
where a.header_file = b.p1
and a.header_block = b.p2
and b.event = ’buffer busy waits’
union
select ’Freelist Groups’ class,
a.segment_type,
a.segment_name,
a.partition_name
from dba_segments a, v$session_wait b
where b.p2 between a.header_block + 1 and
(a.header_block + a.freelist_groups)
and a.header_file = b.p1
and a.freelist_groups > 1and b.event = ’buffer busy waits’
union
select a.segment_type || ’ block’ class,
a.segment_type,
a.segment_name,
a.partition_name
from dba_extents a, v$session_wait b
where b.p2 between a.block_id and a.block_id + a.blocks - 1
and a.file_id = b.p1and b.event = ’buffer busy waits’and
not exists (select 1
from dba_segments
where header_file = b.p1
and header_block = b.p2);
a.segment_type,
a.segment_name,
a.partition_name
from dba_segments a, v$session_wait b
where a.header_file = b.p1
and a.header_block = b.p2
and b.event = ’buffer busy waits’
union
select ’Freelist Groups’ class,
a.segment_type,
a.segment_name,
a.partition_name
from dba_segments a, v$session_wait b
where b.p2 between a.header_block + 1 and
(a.header_block + a.freelist_groups)
and a.header_file = b.p1
and a.freelist_groups > 1and b.event = ’buffer busy waits’
union
select a.segment_type || ’ block’ class,
a.segment_type,
a.segment_name,
a.partition_name
from dba_extents a, v$session_wait b
where b.p2 between a.block_id and a.block_id + a.blocks - 1
and a.file_id = b.p1and b.event = ’buffer busy waits’and
not exists (select 1
from dba_segments
where header_file = b.p1
and header_block = b.p2);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20542911/viewspace-615697/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
