RE: enq: HW on CLOB.
Date: Fri, 7 Dec 2012 08:14:47 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C76721070_at_JAXMSG01.crowley.com>
Thanks Sai, (Switched subject line since this response looks like in may now be a tangent, and I don't wish to distract the thread).
I appreciate the definition. The alert was noticed because of a huge amount of orphaned processes due to the application running out of connections (connection pooling), and ultimately rebooting the app without takeing care of the processes it left in the database. Once the database was rebooted, it did not come back, so that is the only time. Not sure what explains this situation, but it was an unusual event that is not likely to be repeated. I also believe if the field grew larger than 4k or so, it would simply become an out of line CLOB, (not in table, but on disk)... so I am not fully grasping your suggestion.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saibabu Devabhaktuni
Sent: Friday, December 07, 2012 3:39 AM
To: free
Subject: RE: READ wait events when inserting data into a CLOB
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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 07 2012 - 14:14:47 CET