热点块分析脚本

1. 脚本一

select distinct a.owner, a.segment_name
from dba_extents a,
(select dbarfil, dbablk
from x\$bh
from (select addr from v\$latch_children order by sleeps desc)
where rownum < 11)) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk;

2.脚本二

select object_name
from dba_objects
where data_object_id in
(select obj
from x\$bh
from (select addr from v\$latch_children order by sleeps desc)
where rownum < 11));

select distinct a.owner, a.segment_name, a.segment_type
from dba_extents a,
(select dbarfil, dbablk
from (select dbarfil, dbablk from x\$bh order by tch desc)
where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk;

3. 脚本三

select object_name
from dba_objects
where data_object_id in (select obj
from (select obj from x\$bh order by tch desc)
where rownum < 11);

4. 脚本四

select sql_text
from v\$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type
from dba_extents a,
(select dbarfil, dbablk
from (select dbarfil, dbablk from x\$bh order by tch desc)
where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE'
order by a.hash_value, a.address, a.piece;

5. 脚本五

select sql_text
from stats\$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type
from dba_extents a,
(select dbarfil, dbablk
from (select dbarfil, dbablk from x\$bh order by tch desc)
where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE'
order by a.hash_value, a.address, a.piece;

6. 脚本六

select /*+ rule*/
owner, object_name
from dba_objects
where data_object_id in (select obj
from (select obj from x\$bh order by tch desc)
where rownum < 11);

7. 脚本七

select sid,
event,
p1 as file_id,
p2 as "block_id/latch",
p3 as blocks,
l.name
from v\$session_wait sw, v\$latch l
where event not like '%SQL%'
and event not like '%rdbms%'
and event not like '%mon%'
and sw.p2 = l.latch#(+);

8. 脚本八

select sw.sid, event, l.name, de.segment_name
from v\$session_wait sw, v\$latch l, dba_extents de
where event not like '%SQL%'
and event not like '%rdbms%'
and event not like '%mon%'
and sw.p2 = l.latch#(+)
and sw.p1 = de.file_id(+)
and p2 between de.block_id and de.block_id + de.blocks - 1;