RE: is this block cleanout ?

MetaLink document 40689.1 contains a very nice description of delayed block cleanout, and walks the reader through an example.

Jared

"Deshpande, Kirti" <kirti.deshpande_at_verizon.com> Sent by: root_at_fatcity.com
 10/19/2002 08:18 AM
 Please respond to ORACLE-L  

        To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        RE: is this block cleanout ?

Stephane,
 The db version in question is 7.3.2. And as of 7.3 the init parameter, delayed_logging_block_cleanouts, defaults to TRUE (for OPS and non-OPS).  From my understanding of the delayed block cleanout, when oracle commits a
transaction the blocks that it changed are not immediately marked with the commit time. The change to blocks can be due to insert, update or delete. However, if those blocks were still in the buffer cache, the cleanout will take place immediately and there won't be any disk i/o for the cleanout. If
select count(*) is causing an FTS, then the changed blocks that are not in the buffer cache may be getting cleaned. If there is no FTS, then there is
something else going on.... And that's why I said 'sounds about right.' Hopefully tracing the session may reveal what's going on...

Regards,
- Kirti

-----Original Message-----
Sent: Saturday, October 19, 2002 8:44 AM To: Multiple recipients of list ORACLE-L

"Deshpande, Kirti" wrote:
>
> That's sounds about right...
>
> - Kirti
>
> -----Original Message-----
> Sent: Saturday, October 19, 2002 3:48 AM
> To: Multiple recipients of list ORACLE-L
>
> when i do a count(*) an a 1.2GB table, just after an app finishes
inserting
> 20 million records
> i can see from iostat that, that disk is being read as well as written
to
> !!!
>
> the only reason i can think of WRITES being performed while being READ
is
> the block cleanout is being performed by the count(*) !!
>
> i would appreciate if anyone could explain further !
>
> regards
>
> PS: 7.3.2 on AIX, the file is raw and async_io is true
>

Kirti,

  This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ?

Regards,

Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).