| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: latch free - cache buffers chain problem
But the main cause of waits on the cache buffers chains latch comes
from users reading and writing the same block in and inefficient way.
If writers don’t commit their changes immediately, then readers who
are not allow to see uncommitted changes, have to build a reconsistent
copy of the block. A reconsistent copy of the block is made by
copying the current block, ie the one with uncommitted changes, and
then applying rollback to the block to rollback the changes. Both this
new block without the changes and the current copy of the same block
with the changes have the same database block address or DBA. It is
the DBA value (and block class) that is used to determine what hash
bucket to put the block in. All blocks with the same DBA go in the
same block, so if several different reconsitent copies of a block are
made then the hash bucket that contains these blocks can become filled
with many more blocks than the average number of blocks normally found
in a hash bucket. A hash bucket with many blocks will take longer to
scan for the correct block. In order to scan all the blocks in a
buffer a user needs to get the cache buffers chain latch. The more
blocks in the bucket the longer the users might hold the latch. If
this hash bucket is also being accessed by many users because all the
the users are running the same sql and or accessing the same datablock
then the they will have to wait if another user is holding the hash
chains latch.
In order to resolve cache buffer chains latch contention it needs to be determined what sql statements are changing the blocks in the bucket having contention. If the user is making changes or issuing a command like “select for update” without immediately commiting, then that would contribute to the contention. It is also useful to know what statements are reading the blocks out of the bucket showing contention.
Knowing the blocks that show the contention can suggest solutions. If the contention is on a small lookup table then the table can be rebuilt with a higher PCTFREE such that there are one or a few rows per block. If the blocks showing contention come from an object without free lists and the contention always seems to be on blocks at the tail end of the object then recreating the object with free lists might help. If the blocks come from an index based on a sequenced number and the index is being inserted into and the contention comes from the new inserts then reversing the index key, if it is a possibility, might help.
Comments appreciated
Best Wishes
Kyle Hailey
oraperf.sourceforge.net
 |
 |