RE: READ wait events when inserting data into a CLOB

From: Patterson, Joel <Joel.Patterson_at_crowley.com>
Date: Thu, 6 Dec 2012 07:18:52 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C766D1DF9_at_JAXMSG01.crowley.com>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, December 06, 2012 6:49 AM To: saibabu_d_at_yahoo.com; free
Subject: Re: READ wait events when inserting data into a CLOB

From: "Saibabu Devabhaktuni" <saibabu_d_at_yahoo.com> To: "free" <oracle-l_at_freelists.org>
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 operations.
|
| 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
first
| dba956449 block cnt=1 obj#425 tim54644922208998
|

Sai,

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 below)
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.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 06 2012 - 13:18:52 CET

Original text of this message