RE: READ wait events when inserting data into a CLOB

  • From: "Patterson, Joel" <Joel.Patterson@xxxxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "saibabu_d@xxxxxxxxx" <saibabu_d@xxxxxxxxx>, free <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 Dec 2012 07:18:52 -0500

I had an 'enq: HW on my table' show up in an unrelated issue a week ago, and 
confirmed that this enqueue is normally associated with Large objects, in my 
case a CLOB.  I did not investigate exactly what it protects... But this 
particular table only has inserts and selects, no updates or deletes -- leading 
me to think that this is related to concurrent writes and that HW means High 
Water mark...  'buyer beware however'.

Just and FYI if wishing to look into it.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jonathan Lewis
Sent: Thursday, December 06, 2012 6:49 AM
To: saibabu_d@xxxxxxxxx; free
Subject: Re: READ wait events when inserting data into a CLOB

From: "Saibabu Devabhaktuni" <saibabu_d@xxxxxxxxx>
To: "free" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, December 05, 2012 9:02 PM
Subject: Re: READ wait events when inserting data into a CLOB

| Martin,
| You'd typically see "direct path" reads and writes for LOB insert
operations if the lob is stored out of line or if the lob size exceed 4K. 
You may be getting "db file sequential read" waitevent for your indexes on the 
table. You'd also see "db file sequential read" waitevent for lobindex 
| Another possibility is flashback feature requiring blocks to be read 
| from
the disk prior to performing block new operation.
| I see below trace data very interesting, why same block is being read
with "db file sequentially read" and "direct path read".
| WAIT #139690535902720: nam='db file sequential read' ela= 321 file#=5
| block#956449 blocks=1 obj#425 tim54644922208727 WAIT #139690535902720: 
| nam='direct path read' ela= 216 file number=5
| dba956449 block cnt=1 obj#425 tim54644922208998


I think you've already answered your own question.

db file sequential read - for flashback logging db file direct path read - 
because it's about to be overwritten (but see
db file direct path write because it has been overwritten

Another reason for db file sequential read would be for the LOB segment bitmap 
space management blocks.

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.


Jonathan Lewis

Author: Oracle Core (Apress 2011)



Other related posts: