Oracle DB — when a single index changes everything

Last month a client contacted us with a problem every DBA knows: the application is slow. Specifically — searching for orders in their ERP system was taking 45 seconds. A year ago it was 2 seconds. Database: Oracle 11g R2, orders table: 12 million rows.

Diagnosis: EXPLAIN PLAN¶

First instinct: we need more RAM or faster disks. But before you throw hardware at a problem, look at the execution plan. In 90% of cases the problem is in the SQL or missing indexes. Oracle performed a FULL TABLE SCAN on the ORDERS table followed by a NESTED LOOPS join with the CUSTOMERS table. No index on the ORDER_DATE column, which was used for searching.

Solution¶

A composite index on the columns from the WHERE clause. After creating the index and updating statistics, the execution plan changed dramatically. Cost dropped from 47,832 to 234. Query time from 45 seconds to 0.3 seconds.

Histograms — the hidden hero¶

The STATUS column had a highly uneven value distribution — 95% of rows were ACTIVE. Without a histogram Oracle estimated a 50/50 split, leading to incorrect execution plans. Solution: DBMS_STATS.GATHER_TABLE_STATS with a histogram on the STATUS column.

Partitioning¶

For the 12-million-row table we recommended range partitioning by ORDER_DATE. Monthly partitions = partition pruning = further speedup. Bonus: archiving old data becomes trivial.

AWR monitoring¶

We set up weekly AWR reports with an automatic alert when a top SQL statement changes its execution plan. Prevention is better than firefighting.

Rules for SQL optimization¶

1. Always EXPLAIN PLAN — don’t guess, measure. 2. Composite indexes. 3. Update statistics regularly. 4. Histograms for skewed columns. 5. Partitioning for large tables.