RE: READ wait events when inserting data into a CLOB

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 7 Dec 2012 00:38:32 -0800 (PST)
Message-ID: <1354869512.61123.YahooMailNeo_at_web161303.mail.bf1.yahoo.com>



Jonathan,
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.

Thanks,
Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 07 2012 - 09:38:32 CET

Original text of this message