Analyze index validate structure - the dark side

Recently, a co-worker encountered a significant performance hurdle after a database upgrade. The plan included verifying object integrity using analyze table <tablename> validate structure cascade. While most tables processed quickly, one specific table appeared to hang.

After killing the job and analyzing objects individually, they found the culprit: a single index.

The 3-Day Wait: Why Index Validation Fails

When asked how long the process had been running, the answer was shocking: three days. A 10046 trace revealed the process was consumed by db file sequential read events on the table.

The root cause? Clustering Factor.

The analyze index process verifies every row. If the table is well-ordered (clustered) according to the index, the number of blocks read will be close to the actual block count of the table. However, if the table is poorly ordered, the system may read the same blocks from disk millions of times as it "walks" the index.

Illustrating Poor Clustering

Consider rows with IDs 1 through 5.

• Good Clustering: IDs 1, 2, 3, 4, and 5 are in the same block. One read fetches all five.
• Poor Clustering: Each ID is in a different block. Five separate reads are required.

In this case 5 separate blocks must be read to retrieve these rows. In the course of walking the index, some minutes later these rows must also be read:

Later, when the index reaches IDs 1048576 through 1048580, it may need those same blocks again. By then, they've likely been aged out of the cache, forcing yet another physical disk read.

Testing the Theory: 10 Million Rows

To see how bad this can get, we ran a test with $10^7$ (10 million) rows.

-- 1e3 x 1e4 = 1e7 rows def level_1=1e3 def level_2=1e4  create table validate_me pctfree 0 as  select    -- floor(dbms_random.value(1,1e6)) id, -- For bad clustering factor   rownum id,                            -- For good clustering factor   substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mod(rownum,10),15) search_data,   rpad('x',100,'x') padded_data from (select null from dual connect by level <= &level_1) a,      (select null from dual connect by level <= &level_2) b;  create index validate_me_idx1 on validate_me(id, search_data); 

Measuring the Impact

Using a custom script to check the clustering factor, we see that for a poorly ordered table, the factor is nearly equal to the number of rows:

TABLE NAME    TABLE ROWS    INDEX NAME        LEAF BLOCKS    CLUSTERING FACTOR -----------   -----------   ---------------   -----------    ----------------- VALIDATE_ME   10,000,000    VALIDATE_ME_IDX1  45,346         10,160,089

$> tail -n +64 trace.trc | grep "db file sequential read" | awk '{ print $10 }' | sort | uniq -c | sort -n | tail 1 743000 1 743001 1 743002 

$> tail -n +51 trace.trc | grep "db file sequential read" | awk '{ print $10 }' | sort | uniq -c | sort -n | tail -5 54 635330 55 523616 55 530064 55 626066 55 680250 

RMAN> backup check logical validate database; 

SQL> alter index validate_me_idx1 rebuild online; -- Elapsed: 00:00:59.88