Carlos Sierra's Tools and Tips

Every so often I get an email from a SQLTXPLAIN user asking the meaning of these “mutating” observation from the health-checks engine on SQLT. On next SQLT release 11.4.5.0 (due on November 21, 2012), these messages will read “fluctuating” instead of “mutating”, but the health checks will remain the same. So, what do they mean?

Take “fluctuating number of histogram endpoints count”. What it means is the following: every time we gather CBO statistics on a Table, we have a new “version” of the statistics. We store the old one automatically (DBMS_STATS does that starting on 10g) and the new version becomes “current”. We store 31 days of history by default. So if we gather statistics on a Table lets say daily, we may have about 30 statistics versions for that Table and its columns (and Indexes). Now, if a column has lets say 5 buckets in its histogram and the next version has 6, there was a recorded change of 20%, which may be relevant for your investigation. If a column during its stored history has a number of buckets that fluctuates more than 10% from one version to the next, then it is flagged as “fluctuating” (use to be mutating in SQLT 11.4.4.8 and older). Changes of less than 10% are ignored.

Same concept applies to other elements like the number of distinct values in a column, or the height of an index, or the number of rows in a Table or Partition. If they fluctuate more than 10% from one version of the CBO statistics to the next, then they are flagged and reported in the Observations section of the main SQLT report.

Are we supposed to do anything with those health-checks? All depends. Lets say your SQL suddenly stop using index X and now uses index Y. If blevel on X has increased recently, you can suspect a cost increased due to the new blevel and investigate accordingly. Keep in mind that “Observations” section of SQLT, which reports outcome of health-checks, is basically a collection of heads-up. Things to investigate and maybe pointing to the root cause of a recent change in an execution plan for the SQL being investigated.