Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Proving or Debunking the need for rebuilding
joel garry wrote :
> hpuxrac 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 ?
>
> 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.
>
>
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.
![]() |
![]() |