热点块竞争与解决
热点块主要是latch竞争,大量进城等待latch free竞争,我们可以从V$SESSION_WAIT视图中查询得到:
SID SEQ# EVENT
---------- ---------- -----------------------------------------------
4 14378 latch free
43 1854 latch free
176 977 latch free
187 4393 latch free
111 8715 latch free
209 48534 latch free
如果需要具体确定热点对象,可以从V$LATCH_CHILDREN中查询具体的子LATCH信息,可以通过以下SQL查询
SELECT*
FROM(SELECTaddr,
child#,
gets,
misses,
sleeps,
immediate_gets igets,
immediate_misses imiss,
spin_gets sgets
FROMv$latch_children
WHERENAME='cache buffers chains'
ORDERBYsleepsDESC)
WHEREROWNUM<11;
X$BH中还存在另外一个关键字段HLADDR,即hash chain latch address,这个字段可以和v$latch_children.addr进行关联,这样就可以把具体的LATCH竞争和数据块关联起来,在结合dba_extents视图,就可以找到具体的热点竞争对象,找到具体热点竞争对象之后,可以结合v$sqlarea或者v$sqltext,找到频繁操作这些对象��SQL,然后对其进行优化,即可缓解或解决热点块竞争的问题。
以下SQL可以获取当前持有最热点数据库的LATCH及BUFFER信息:
SELECTb.addr,
a.ts#,
a.dbarfil,
a.dbablk,
a.tch,
b.gets,
b.misses,
b.sleeps
FROM(SELECT*
FROM(SELECTaddr, ts#, file#, dbarfil, dbablk, tch, hladdr
FROMx$bh
ORDERBYtchDESC)
WHEREROWNUM<11) a,
(SELECTaddr, gets, misses, sleeps
FROMv$latch_children
WHERENAME='cache buffers chains') b
WHEREa.hladdr = b.addr;
利用前面提到的SQL可以找到这些热点BUFFER的对象信息:
SELECTdistincte.owner, e.segment_name, e.segment_type
FROMdba_extents e,
(SELECT*
FROM(SELECTaddr, ts#, file#, dbarfil, dbablk, tch
FROMx$bh
ORDERBYtchDESC)
WHEREROWNUM<11) b
WHEREe.relative_fno = b.dbarfil
ANDe.block_id <= b.dbablk
ANDe.block_id + e.blocks > b.dbablk;
结合V$sqltext或v$sqlarea,可以找到操作这些对象的相关SQL,继续查询:
SELECT
hash_value, sql_text
FROMv$sqltext
WHERE(hash_value, address)IN
(SELECTa.hash_value, a.address
FROMv$sqltext a,
(SELECTDISTINCTa.owner, a.segment_name, a.segment_type
FROMdba_extents a,
(SELECTdbarfil, dbablk
FROM(SELECTdbarfil, dbablk
FROMx$bh
ORDERBYtchDESC)
WHEREROWNUM<11) b
WHEREa.relative_fno = b.dbarfil
ANDa.block_id <= b.dbablk
ANDa.block_id + a.blocks > b.dbablk) b
WHEREa.sql_textLIKE'%'|| b.segment_name ||'%'
ANDb.segment_type ='TABLE')
ORDERBYhash_value, address, piece;
找到这些SQL之后,剩下的问题就简单了,可以通过优化SQL减少数据的访问,避免货优化某些容易引起争用的操作(如CONNECT BY等操作)来减少热点块竞争。