How Updating Statistics and Adding a Composite Index Turned a 28‑Second Oracle Query into Milliseconds

Introduction: In an Oracle 10g OLTP environment a critical business query was consuming 56% of database resources, running 278 times in half an hour with an average execution time of 28 seconds.

Initial analysis: The execution plan showed an INDEX RANGE SCAN with low cost but high consistent gets. The table held about 360 million rows, with indexes on columns cn and c_date. However the optimizer chose the IDX_C_LOG_DATE index instead of the expected cn index.

Testing with a hint: Applying a hint to force the cn index ( /*+ INDEX(t IDX_REC_LOG_CN) */) reduced the execution time to the millisecond level and lowered consistent gets from 19 409 to 7.

Root cause – stale statistics: The table’s statistics were last gathered in May, more than three months earlier. The default GATHER_STATS_JOB in Oracle 10g had not been started, so the optimizer relied on outdated information.

Fixing statistics: The GATHER_STATS_JOB was enabled and the table’s statistics were manually collected. After gathering fresh stats, the execution plan switched to use the IDX_REC_LOG_CN index, achieving millisecond execution and dramatically lower resource consumption.

Regression: Later the plan reverted to IDX_C_LOG_DATE, execution rose to ~2 seconds and consistent gets increased to 10 404. Although the daily GATHER_STATS_JOB ran successfully, the statistics again became inaccurate.

Resolution: A dedicated single‑table statistics collection job was defined, and a composite index on ( cn, c_date) was created. Since then the query has remained fast with no similar performance issues.

Additional diagnostics such as Oracle event 10053 were referenced for deeper cost analysis.