Re: Proving or Debunking the need for rebuilding

Richard Foote wrote :

> Now my suspicious mind is cast towards this night job that "YES,
> definitely yes" improved after index rebuilds.

Please do, Richard :-)

I didnt intend to be dragged into a long discussion, but this one is becoming very interesting. You are doing quite a bit of the work that I would have to perform anyway sooner or later, and you are doing it much better than I could.

Thanks. Will do my best to answer.

> It's also puzzling why with such incredible results, one lets the jobs get
> so bad as to take 2 1/2 times longer than usual before rebuilding the
> indexes again ?

We dont let it go so bad, of course.

We have been rebuilding indexes daily for eight years, after tests under Oracle 7 (!!!). Now, it was more than time to challenge this decision.

Therefore, I tested the behavior of the system without rebuild. The dramatic impact on the night job made it clear that we could not stop rebuilding indexes without further study, which is likely to take time and is not planned as of today.

> Again with so little information, it's totally impossible to know either
> way but again my money is on these dramatic improvements having
> nothing to do (directly) with index rebuilds.

That's the only conclusion I can draw. The system was on steady state on the time scale of 24 hours, to the exception of one list which was growing. I could see day after day the the time taken by the night job increase, till 5 hours. The night after the rebuilt was reinstalled, the time needed by the job dropped to 2 hours.

> Again though I make the important point that if such a reduction in timings,
> an impressive *60%* was based purely and simply on these index rebuilds,
> then it somewhat suggests that index block reads comprise well over 60%
> (70%, 80%, 90%+ ?) of overall reads.

I dont know, Richard.

> If your use the RBO as you mentioned earlier, then it's unlikely that the
> execution plan would have changed after the rebuilds.

Mmmm... That's a *very* good point.

When there is a tie between indexes, rule uses the last created one. I realize that I dont know which is the "last" index after a rebuild.

There may be a very remote possibility that plans changed. Very remote.

> This is an extremely puzzling scenario as index reads usually comprise only
> a small fraction of overall reads, even for usually inefficient indexes.
> This suggests in this case that you have a ratio of more than one index
> block for each and every index entry the SQL accesses. This in turn
> suggestions these night jobs are accessing large numbers of empty index
> blocks as part of the index scans being performed.

I am trying to follow you, but while I can sort of see that (mostly ?) empty index blocks is a possible cause, I dont understand why it is the only or even most likely one...

(I'm not saying that you are wrong. Far from it)

>
> It's really unfortunate you don't keep before and after statistics of these
> index rebuilds as the results would be most illuminating.

Yes, it is unfortunate, I'm deeply sorry, but then I didn't intend nor even expected to be subject to such a scrutinity in a public forum :-)

> execution results of the bad/good night jobs. It would also be interesting
> to know what kind of DML is performed on these indexes/tables

The night job has a handful of phases. The phase that increased noticeably
is performing select and updates. No insert nor delete.

> if there is significant delete activity (and I mean really really significant delete
> activity without subsequent inserts)

A later phase does a massive amount of select and deletes.

I cannot think of an insert in any phase.

> if the indexes values are monotonically increasing

Many of them are. Primary key are given by sequences. Some indexes use dates;

Not all of them are monotonic, of course.

> or whether any "unusual" activity is performed on these
> indexes/tables.

Well, what do you call "usual ?" ;-)

Mmmm... Deletes are not monotonic.

Stuff is deleted when expired. Most stuff has a somewhat constant expiry delay. But perhaps one stuff in ten must be kept much longer than that usual delay.

Trying to quantify a bit... somewhat constant means a constant + (1 to 3 days). Much longer may be the constant + 6 months. One in ten is an order of magnitude. May be one in 5 or one in 20. I would need to (deeply) check the customer setup I used.

> It would also be interesting to know what the SQL does

Well.. many many things :-)

> whether full index scans are performed

No, with very high probability.

> whether it processes particular "areas" of data
> (such as the older historical stuff)

Definitely yes, for the phase with the deletes hinted above, which deletes historical stuff.

I am less sure for the phase that showed the so noticeable slowdown, however. As I recall, that phase does access historical stuff by the primary key, but the driving index is not what I would call historical - that is it does not index the stuff described above.

That index keys are updated, though.

> It would also be interesting to know just how well tuned these night jobs
> are.

As far as we know, they use the best possible plan for the algorithm as written.

The algorithm itself processes one thing at a time, however. There are no massive update or delete in a single statement.

>
> I'm sorry Raoul if I sound somewhat sceptical
>

Again, please do, Richard. You are most welcome.

Cheers

• Raoul