Tuesday, March 3, 2009

Execution Plan changes with no apparent reason

From Metalink (Bug 4567767):
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.

Before:
  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

  After:
  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;

and bounce.

No comments:

Post a Comment