Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

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

with one comment

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.

Written by Carlos Sierra

June 5, 2012 at 3:16 pm

One Response

Subscribe to comments with RSS.

  1. Greate

    szbonze

    June 5, 2012 at 10:32 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,689 other followers

%d bloggers like this: