Re: Proving or Debunking the need for rebuilding

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 28 Nov 2006 08:37:48 -0800
Message-ID: <1164731867.850376@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:
> DA Morgan wrote :

>> hasta_l3_at_hotmail.com wrote:
>>>>> Is it possible what the OP is seeing is a delayed block cleanout?
>>>> I think it is entirely possible.  I'm wondering if simply doing a
>>>> count(*) that uses the index in question, before performing each test,
>>>> might shed light one way or the other.  The idea is to visit all blocks
>>>> and clean them.
>>>>
>>> We are deleting in a bunch of small transactions.
>>> We should have (almost) no delayed block cleanouts, right ?
>>>
>>> --- Raoul
>> Depends!

>
> Would you elaborate, Doug ?
> I am not much familiar with this area.
>
> At commit time, blocks will be cleaned up if they are
> still in the cache, right ?
>
> Off hand, I cannot think why (many) dirty blocks would
> be written on disk :
>
> a) We have pretty big redo logs.
> b) Checkpointing happens with low frequency
> c) With high probability, we dont do huge selects
> or table scans that would flush blocks out of the
> cache.
> d) The structure of an index does not massively
> change when - say - ten or twenty rows are deleted,
> right ? Or does it ?
>
> Anything I missed ?
>
> --- Raoul

Tom Kyte's explanation is infinitely better than anything I would attempt:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Nov 28 2006 - 10:37:48 CST