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
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
