Slow INDEX FULL SCAN (MIN/MAX) after rollback

Last week i was called by multiple users signaling a total slowdown of the the “system”. After a few check i decided to trace one of the affected user action.There was only a single SQL statement consuming mostly of the time and it was as simple as this :

select max(date_insert) from big_table

The execution plan for this statement seemed good it was using an indexed access path on the projected column but it was issuing a huge number of consistent gets and that was causing a huge CPU consumption and slowing down all the application on the server caused by the increase of the load on server.So it’s time to check the health of the index.I begin by issuing this query :

ANALYZE INDEX BIG_TABLE_IDX  VALIDATE STRUCTURE;

SELECT
NAME,
BTREE_SPACE,
DEL_LF_ROWS,
(DEL_LF_ROWS_LEN / BTREE_SPACE) * 100    PCT_USED_DEL_ROWS,
PCT_USED
FROM INDEX_STATS

Capture 2

I asked on how the table was filled and i was told that a batch job was running every night inserting a lot of data on it and that for some reason the jobs failed last night.78% pct of space used for a monotonically increasing indexes with no deletion. Emmhh ! let’s take a dump of the index structure.

alter session set events 'immediate trace name treedump level 792332';

Capture 3

All become clear now ! Oracle will navigate to the right most left block for finding the maximum value however this block is empty due to the rollback so it will walk down the other empty left block until finding the max value causing the huge increase of the consistent gets and the load on the server.This is how this simple SQL statement caused the slowdown of the “system”.

How to fix ? A simple Coalesce will do it.