遇到buffer busy chains 如何处理
SQLWKS> select * from v$session_wait
2> where event not like 'rdbms ipc message' and event not like 'SQL*Net message from client';
SIDSEQ# EVENT
P1TEXT
P1 P1RAWP2TEXT
P2 P2RAWP3TEXT
P3 P3RAWWAIT_TIMESECONDS_IN STATE
latch free
The process waits for a latch that is currently busy (held by another process).
Parameter Description
address The address of the latch for which the process is waiting
number The latch number that indexes in the V$LATCHNAME view.To find more information on the latch, use the following SQL statement:
select *
from v$latchname
where latch# = number;
tries A count of the number of times the process tried to get the latch (slow with spinning) and the process has to sleep
查了查metalink
Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.
Cache Buffers Chains Latch waits 由多个会话等待去读相同的块而引起
Typical solutions are:-
o Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary.
查看那些访问疑问块的SQL语句并判断重复读是否是必须的
o Check for suboptimal SQL (this is the most common cause of the events) - look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimise the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block
检查那些不理想的语句(这些是大多数通常该事件的原因),查看正在运行的SQL语句的执行计划并减少每次执行的gets，这将会减少(minimise)被访问的块的数量，因此减少多个会话争用相同块的机会。
Note 34405.1 WAITEVENT: "buffer busy waits" Reference Note
Note 42152.1 LATCH: CACHE BUFFERS CHAINS
Note 155971.1 Ext/Pub Resolving Intense and "Random" Buffer Busy Wait Performance Problems:
Note 163424.1 Ext/Pub H
Subject:LATCH: CACHE BUFFERS CHAINS
Doc ID:42152.1 Type:REFERENCE
Modified Date :20-OCT-2005 Status:PUBLISHED
Eg: Given ADDR from V$LATCH_CHILDREN for a heavily contended
child latch:
select dbafil, dbablk, class, state
from X$BH where HLADDR='address of latch';
请问找出 dbafil,dbablk然后可以怎么样呢
class,state 有什么解释吗？
谢谢
