Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Proving or Debunking the need for rebuilding

Re: Proving or Debunking the need for rebuilding

From: <>
Date: 29 Nov 2006 11:55:10 -0800
Message-ID: <>

joel garry wrote :

> hpuxrac wrote:
> > 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 ?
> You previously said a later phase has massive deletes.

Imagine orders and the associated order lines.

Among other things, the night job deletes thousands of orders. That's the massive delete phase.

However, each order - and the associated order lines - is deleted in its own transaction. That's the bunch of small transactions.

Of course, my description is simplified. We must be deleting from ten or so inter-related child and sub-child tables.

> The thing about
> delayed block cleanout is... it's delayed. You see the effect in a
> later access to the blocks, like a select, then don't see it after
> that. If the complaint is everything slows down always until an index
> is rebuilt, that would be something else.


> Maybe you need to reiterate what you do when, and what slows down, I'm
> confused.

> Now to get way more speculative: the fast cleanout happens when no one
> else is using the block - is it possible you are hitting the same block
> in different small transactions concurrrently, and consecutively
> hitting blocks, such that a bunch of small transactions overlap into a
> large bunch of blocks?

Well, the night job is (essentially) running alone.

You know, I think Richard nailed it down, at least partially. Our insert
and delete pattern is probably such that the leaf blocks of some important indexes are 90 or 95% empty. Now, quite a few critical selects have been optimized to do index range scan only. They get their data from the index and don't even access the underlying table.

I suspect that almost empty leaf blocks are bound to have interesting effects on these critical selects.

But I would need to rerun the test or write a simulation to be sure. I intend to do it, sooner or later.

Take care, Joel.

Received on Wed Nov 29 2006 - 13:55:10 CST

Original text of this message