RE: enq: HW on CLOB.
- From: "Patterson, Joel" <Joel.Patterson@xxxxxxxxxxx>
- To: "saibabu_d@xxxxxxxxx" <saibabu_d@xxxxxxxxx>, free <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Dec 2012 08:14:47 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
