Carlos Sierra's Tools and Tips

What is the meaning of this observation on a SQLTXPLAIN (SQLT) main report?

“Table contains N column(s) referenced in predicates with mutating number of histogram endpoints count.”

This observation is part of the health-checks performed by SQLT on a given SQL statement. It means the following:

1. Your SQL includes predicates on column(s) with histograms; and
2. Number of buckets in histograms for these column(s) has changed more than 10% between two consecutive versions of the statistics

For example, your SQL has a predicate like “c1 = :b1” and c1 has a histogram. Currently, the number of buckets was 5, but on your prior version of the statistics for this column, its histogram had 4 buckets.

How does it affect you?

It all depends. Maybe it does not. But maybe, the new value is what your SQL passes during bind peeking. Then it may impact your execution plan…

Keep in mind that “Observations” in SQLT are merely some kind of “heads-up” that something may affect your plan. Other cases of “mutating” values include:

• Indexes with mutating BLEVEL
• Tables with mutating NUM_ROWS
• Columns with mutating NDV

And the same applies to partitions and sub-partitions.

Typical case:

What happens if your SQL was using index X on its execution plan and suddenly it switches to index Y? Same join order, just one index access swapped.

If you see that X or Y is mutating its BLEVEL, you can suspect that might be the culprit. Of course you will have to review 10053 to be sure, but at least the “observation” creates some awareness of this possibility.