How to Turn a 28‑Second Oracle Query into Milliseconds: A Real‑World SQL Tuning Case Study

During routine DBA monitoring, an OLTP database was found to run a particular SQL statement 278 times in half an hour, each execution averaging 28 seconds and consuming 56% of DB resources.

Initial Execution‑Plan Review

The plan showed a low cost (≈2) and an INDEX RANGE SCAN, but the consistent gets were high, indicating inefficient data access. The query filtered on columns cn and c_date, both indexed, yet the optimizer chose the c_date index only.

Why the cn Index Was Ignored

By examining predicate selectivity, the cn condition returned only three rows while c_date returned about 760,000 rows. The optimizer’s statistics caused it to favor the latter.

Applying an Index Hint

Adding a hint to force the cn index ( /*+ INDEX(table_name IDX_REC_LOG_CN) */) changed the execution time to the millisecond range, confirming that the index choice was the bottleneck.

Stale Statistics Problem

Further investigation revealed that the table’s statistics were last gathered in May, over three months old, leading to inaccurate cardinality estimates. The default GATHER_STATS_JOB in Oracle 10g had not been enabled.

Collecting Fresh Statistics

Enabling the default GATHER_STATS_JOB and manually gathering statistics for the table reduced the consistent gets from 19,409 to 7 and the execution plan switched to use the IDX_REC_LOG_CN index, bringing the runtime down to milliseconds.

Regression After Statistics Refresh

When the query slowed again, the plan reverted to the IDX_C_LOG_DATE index, and consistent gets rose to 10,404. Although the daily GATHER_STATS_JOB was running successfully, the statistics had become inaccurate again.

Defining a Dedicated Statistics Job

To prevent future regressions, a single‑table statistics collection job was created, ensuring that large tables receive up‑to‑date statistics independently of the generic job.

Creating a Composite Index

Finally, a combined index on cn and c_date was built. After its creation, the execution plan consistently used the new composite index, and the performance issue disappeared permanently.

Key takeaways: always correlate execution‑plan analysis with table structure and data distribution, keep statistics fresh, use hints for diagnosis, and consider composite indexes when single‑column indexes are insufficient.