Re: READ wait events when inserting data into a CLOB

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <saibabu_d@xxxxxxxxx>, "free" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 Dec 2012 11:49:20 -0000

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: