Why Stale Statistics Produce Bad Oracle Execution Plans and How to Fix Them

1 Case Introduction

A simple SQL that aggregates alarm detail data from a partitioned table became unexpectedly slow over the past two months. The query filters on a fixed date column NEALARM_TIME and a non‑selective column RELATED_EMS_CUID, scanning partitions that contain data from January to March.

The HISTORY_ALARM table stores data from 2015‑03‑01 onward, about 1.5 million rows per day, partitioned by day and month. The 41st partition holds data for Jan‑Mar 2016, so the optimizer may choose either a partition full‑table scan or a composite index scan.

Execution Plan

The latest statistics were collected with sampling set to AUTO and no histograms. The plan shows several oddities:

The estimated cardinality for index HIS_ALARM_INDEX1 is much smaller than the parent node’s row‑count. HIS_ALARM_INDEX1 is a composite index on ( NEALARM_TIME, NEEND_TIME) but the query only uses the date column.

Normally, index selectivity should be greater than or equal to the table selectivity, and the number of rows returned after rowid lookup should be less than the rows returned by the index scan. Because the query filters only by date, the index still requires a rowid lookup, and scanning 1/3 of the partition (≈15 million rows) is far more expensive than a full‑table scan, explaining the poor performance.

2 Analyzing with Trace 10053

To investigate, a trace 10053 was collected to see how the CBO used statistics to choose the plan.

1. Compute Table Cardinality

The partition prune result is Part#:40, and statistics come from that partition. The table selectivity (no histogram) is shown, confirming the estimate is reasonably accurate.

2. Access Paths and Cost Calculation

The CBO chose the index range scan on HIS_ALARM_INDEX1 because its estimated cost appeared lower, but detailed cost breakdown reveals:

Single‑column index HISALARM_PART_IDX_0 on NEALARM_TIME has a correct selectivity of 1/3 for partition 40.

Accessing the local partition index costs more (13,537,358) than a full‑table scan (2,982,164); the optimal path should be a partition full‑table scan.

Ideally, the cost of the local single‑column index should be lower than the multi‑column global index HIS_ALARM_INDEX1, but the trace shows the opposite.

The global index HIS_ALARM_INDEX1 (NEALARM_TIME, NEEND_TIME) uses only NEALARM_TIME in the predicate, resulting in an abnormally low selectivity of 0.000041.

This tiny selectivity leads to a severely underestimated cardinality (cost ≈ 5,562.83) for the index path.

Further inspection of dba_tab_col_statistics shows the global statistics for NEALARM_TIME have a minimum value of 91890122 instead of the real 20150301 . A few anomalous rows caused this distortion, making the optimizer think the date range is far narrower and thus the index highly selective.

3 Summary and Solutions

The CBO uses global statistics when evaluating global index access costs. In this case, the table’s row estimate (≈855 K) derived from partition statistics is accurate, while the index’s row estimate (≈5 964) derived from corrupted global statistics is far too low, causing the optimizer to favor the index.

The root cause is incorrect global statistics. The remedy is to correct or discard the faulty global stats.

Solution Steps

Identify and delete the anomalous rows, then re‑gather statistics.

Drop the global index or rebuild it as a local partitioned index.

Make the global index invisible so the optimizer will not consider it (demonstrated to improve the plan).

Collect a histogram on the NEALARM_TIME column to improve selectivity estimates.

After making the index HIS_ALARM_INDEX1 invisible, the optimizer no longer considers it; the cost of the local partition index remains higher than a full‑table scan, and the plan correctly chooses the partition full‑table scan.