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