Why Oracle CBO Chose Index Scan Over Full Table Scan: Uncovering Misleading System Statistics

Problem Overview

Customer reported that all ETL and reporting SQLs in production stopped running; monitoring showed many execution plans changed to use index plus NL, affecting almost all AP‑type queries. The DBA spent weeks troubleshooting without success.

Initial Diagnostic Attempts

Full database statistics collection and histogram updates – no effect.

Binding execution plans for ~10 urgent SQLs using SQLPROFILE – worked for those but not scalable.

Checked optimizer_index_* parameters – all defaults; no abnormal changes.

Reproducing the Issue

Created a test schema and data to mimic the problem:

create user test identified by test;
grant dba to test;
conn test/test;
create table t1 as select * from dba_objects;
create index test.idx1 on test.t1(OBJECT_ID);
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T1', cascade => true, method_opt => 'for all columns size auto');
update test.t1 set OBJECT_ID=1 where rownum <40000;
commit;

Querying the table shows 87,629 rows; a simple SELECT with OBJECT_ID=1 returns ~40,000 rows but uses an index scan instead of a full table scan.

set autot trace;
alter system flush shared_pool;
select * from test.t1 t where OBJECT_ID=1;

Execution plan shows index range scan with cost 746.93 versus full table scan cost 10,050.77.

Deep Analysis

10053 trace and SQLT reports revealed that system statistics had been collected, showing abnormal values for single‑block read (SREADTIM) and multi‑block read (MREADTIM) times. The cost model uses:

Cost = (#SRDs * sreadtim + #MRDs * mreadtim + #CPUCycles / cpuspeed) / sreadtim

With the collected stats, SREADTIM was 1 ms and MREADTIM 1000 ms, causing the optimizer to favor index scans (lower cost) even when a full scan would be faster.

Root Cause

Collecting system statistics introduced inaccurate SREADTIM, MREADTIM, and MBRC values because the collection ran during a short idle period, leading to misleading I/O cost estimates.

Resolution

Removing the collected system statistics restores correct cost calculations:

exec dbms_stats.delete_system_stats;
alter system flush shared_pool;

After flushing the shared pool and re‑executing the query, the optimizer chooses a full table scan and performance returns to normal.

Takeaways & Recommendations

System statistics can distort optimizer cost estimates if I/O timings are inaccurate.

Prefer default system statistics; only collect them deliberately with DBMS_STATS.gather_system_stats when needed.

When troubleshooting performance, examine four key areas: statistics, access paths, SQL formulation, and database version/patch level.

Adjusting SREADTIM, MREADTIM, or MBRC manually without proper collection does not help.