It is possible for Queries' execution plans to change without any modification in statistics or optimizer environment.
Usually it is interpreted as the plans changed "out of the blue".
The reason for the change is that density is being reevaluated as 1/ndv,
instead of taking the statistic stored in the data dictionary when the table
is reloaded to the row cache for whatever reason, like a shared pool flush.
It is not easy to catch in the act but can be seen on a 10053 trace file, when the query is hardparsed before and after the table is reloaded to the row cache.
Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X NDV: 1344 NULLS: 0 DENS: 1.5152e-02 <------ From Dict. NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X NDV: 1344 NULLS: 0 DENS: 7.4405e-04 <------ 1/1344 NO HISTOGRAM: #BKT: 1 #VAL: 2
This bug affects all 9i versions and 10g, except 10.2.0.4, installed in any platform.
Oracle's recommendation for a workaround solution, if upgrade is not possible, is:
ALTER SYSTEM SET EVENT='10139 trace name context forever' SCOPE=SPFILE;