When the Clustering Factor Strikes: How a “Perfect” Plan Turned into a 9-Second I/O Nightmare

This wasn’t a theoretical exercise—it was a real-world performance issue that left the database paralyzed.
Let’s start with the global stats:

The database wasn’t working—it was waiting.
Over 90% of the query’s elapsed time was spent on I/O waits, meaning the database was stuck reading data from disk.

Step 1: The Execution Plan

At first view, the execution plan looked straightforward. Here’s a simplified excerpt highlighting the critical operations:

---------------------------------------------------------------------------------------------------------------------------
| Id | Operation                               | Name                     | Rows (Estim) | Rows (Actual) | Activity Detail |
---------------------------------------------------------------------------------------------------------------------------
...
|  5 |  UNION-ALL                              |                          |            3 |          9767 |                 |
...
| 91 |   TABLE ACCESS BY INDEX ROWID BATCHED   | TRANSACTIONS_LEDGER      |            5 |          4492 | 27MB Read       |
| 92 |    INDEX RANGE SCAN                     | IDX_TRANSACTIONS_COMPLEX |            2 |          4492 |                 |
...
---------------------------------------------------------------------------------------------------------------------------

Cardinality Misestimate:
The optimizer expected to fetch 5 rows, but actually retrieved 4,492 rows from TRANSACTIONS_LEDGER.

High I/O Activity:
That single operation read 27 MB from disk.

Clearly, the optimizer was making a bad choice—but why?

Step 2: The First Attempt – Gather Statistics

My first instinct was to gather fresh statistics for the index IDX_TRANSACTIONS_COMPLEX.
Unfortunately, that didn’t change a thing. The plan remained the same—and so did the I/O wait.

EXEC DBMS_STATS.GATHER_INDEX_STATS( 'APP_OWNER','IDX_TRANSACTIONS_COMPLEX');

Step 3: Digging Deeper

A quick look into the data dictionary revealed the real cause:

SQL> SELECT NUM_ROWS, CLUSTERING_FACTOR
FROM DBA_INDEXES
WHERE INDEX_NAME = 'IDX_TRANSACTIONS_COMPLEX';

NUM_ROWS     CLUSTERING_FACTOR
-----------  -----------------
463,677,825	 432,799,057

There it was—the Clustering Factor was almost as high as the number of rows.

In a table with 463 million rows, a CLUSTERING_FACTOR of 432 million means the index entries are poorly correlated with the table’s physical data blocks.
A good CLUSTERING_FACTOR should be close to the number of table blocks, not the number of rows.

The result?
For every row the optimizer wanted to fetch, Oracle had to perform random disk I/O—one by one. That’s what caused the 9 seconds of I/O wait.

Step 4: The Root Cause

When I examined the index definition, the problem became clear:
IDX_TRANSACTIONS_COMPLEX was built on 7 columns, while the SQL filter only used 3 of them.

CREATE INDEX "APP_OWNER"."IDX_TRANSACTIONS_COMPLEX" 
ON "APP_OWNER"."TRANSACTIONS_LEDGER" 
   (
      "ACCOUNT_ID",           
      "ACCOUNT_TYPE",         
      "TRANSACTION_TYPE_ID",  
      "NET_AMOUNT",          
      "ACCOUNTING_DATE",      
      "SYSTEM_ENTRY_DATE",    
      "VALUE_DATE" 
   );

So even though an index existed, it was too wide, and its clustering was too poor to be useful.
In fact, a full index scan was nearly as costly as a full table scan.

Step 5: The Fix

The solution was to close the “index gap” by creating efficient indexes optimized for the actual query patterns.

-- Index for Pending Transactions
CREATE INDEX IDX_LEDGER_ACCOUNT_DATE 
ON TRANSACTIONS_LEDGER
(ACCOUNT_ID, ACCOUNT_TYPE, ENTRY_DATE DESC);

These new indexes perfectly matched the query predicates.
The optimizer could now execute the entire operation within the index itself, avoiding expensive table lookups.

Step 6: The Result – From Seconds to Milliseconds

After deploying the new indexes, the query’s elapsed time dropped from 10 seconds to under 200 milliseconds.
No more random I/O storms, no more waiting—just efficient, predictable performance.