Re: READ wait events when inserting data into a CLOB

• Original Message ----- From: "Saibabu Devabhaktuni" <saibabu_d_at_yahoo.com>

| 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
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l