Re: READ wait events when inserting data into a CLOB

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "free" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Dec 2012 11:19:26 -0000

----- Original Message ----- 
From: "Saibabu Devabhaktuni" <saibabu_d@xxxxxxxxx>

| Jonathan,
| I couldn't reproduce the scenario of reading the same block with "db file 
sequential read" waitevent and immediately followed by "direct path read" 

I thought I did it last night with:

    flashback logging on
    lob segment in ASSM tablespace
    switch log file
    flush buffer cache
    insert lob

But I can't get it to reproduce - so maybe I misread the trace file.

| You said:
| "A thought that I don't think I've considered before - if you do a direct 
| write to write a LOB, how do you ensure that no other session is doing a 
| path write on the same block ? Is there an enqueue that protects the LOB 
| concurrent writes - after all, you can't do a buffer pin when it's a 
| path operation. This may have something to do with why you have to read 
the LOB
| block direct before writing it."
| It is a great question by itself, may be Oracle is relying on the space 
management (freelists or ASSM) to make sure no more than one process can 
write a given lob segment block at a time.

Good analysis - this makes sense to me.  I guess we would see buffer busy 
waits on the 1st level bitmap blocks (for ASSM) or the "bitmap block" or 
"bitmap index block" class (for non-ASSM) as a session acquired an empty 
block that it was going to write into; and on the index leaf blocks 
identifying an older image that was about to be re-used.  In memory buffer 
pins on space management would preclude the need for any other locking.


Jonathan Lewis

Author: Oracle Core (Apress 2011)


Other related posts: