RE: READ wait events when inserting data into a CLOB

  • From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
  • To: free <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Dec 2012 00:38:32 -0800 (PST)

I couldn't reproduce the scenario of reading the same block with "db file 
sequential read" waitevent and immediately followed by "direct path read" 
waitevent. Martin, can you try reproducing it again or build a test case (also, 
try to repeat the same test case with lob caching turned on)?

You said:

"A thought that I don't think I've considered before - if you do a direct path 
write to write a LOB, how do you ensure that no other session is doing a direct 
path write on the same block ? Is there an enqueue that protects the LOB from 
concurrent writes - after all, you can't do a buffer pin when it's a direct 
path operation. This may have something to do with why you have to read the LOB 
block direct before writing it."

It is a great question by itself, may be Oracle is relying on the space 
management (freelists or ASSM) to make sure no more than one process can write 
a given lob segment block at a time. Since space management allocate new block 
for inserting each out of line lob record and the fact that no more than one 
final state lob record can exist in one data block, this can ensure only one 
process is writing a given lob block at a time. Also, since lob segment blocks 
are always clean blocks (i.e. no chance of block cleanout's), as you said with 
the exception of bitmap blocks, these blocks never had to be read into buffer 
cache and hence no possibility of traditional DBWR writes.

If the lob caching is turned on, then all lob segment blocks are read and 
written without using "direct path" waitevents and hence above points are not 
valid for this, Martin you may want to try this option.

Joel, HW enqueue contention you are seeing is due to segment expanding beyond 
HWM, you may want to use bigger extent size.


Other related posts: