Re: READ wait events when inserting data into a CLOB

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Sat, 8 Dec 2012 22:41:52 +0200

Unless I misunderstand your thought, I think there should be no chance that
some session tries to read a LOB chunk while another one is writing into

This is because LOB chunks are not updated in place. If you update a LOB
chunk, what you really do is you create a new version of it in a new
location (and the new location will be chosen using freelists or ASSM space
allocation logic). So, when you write a new version of the chunk into a new
location, others won't even try to read it as it doesn't even officially
exist yet - this is guaranteed by the LOB index which adheres to the usual
read consistency mechanism using undo segments. So unless your LOB write is
finished and committed, everyone else will see a previous version if the

Tanel Poder
Blog -
App  -

On Fri, Dec 7, 2012 at 1:19 PM, Jonathan Lewis

> ----- 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"
> waitevent.
> 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
> 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."
> |
> | 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.
> Regards
> Jonathan Lewis
> Author: Oracle Core (Apress 2011)
> --
> //


Other related posts: