Posts Tagged ‘Health-Check’
Table contains N column(s) referenced in predicates with mutating number of histogram endpoints count.
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:
- Your SQL includes predicates on column(s) with histograms; and
- 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.
SQLTXPLAIN (SQLT) 11.4.4.6 is now available
So what is new in SQLT 11.4.4.6? Honestly, not much: one fix and 6 enhancements:
- BUG: SQLT TC “restore” was allowing a column to be renamed from “a” to “b” then from “b” to “c”, so it was ending up with duplicate rows in histogram among other issues.
- ENH: New tool parameter to control the automatic generation of a script with a Custom SQL Profile for best performing plan. Custom SQL Profiles based on DBMS_SQLTUNE.IMPORT_SQL_PROFILE are now inactive by default.
- ENH: New HC detecting corrupt histograms as per bugs 8543770, 10267075, 12819221 and 12876988.
- ENH: New HC reporting Tables where there are columns referenced in predicates and for which the number of distinct values has changed more than 10% from one statistics version to the next. These abrupt changes can cause an execution plan to also change.
- ENH: New HC about CBO not using partition level histograms if stats were gathered with “INCREMENTAL” and fix for bug 13583722 has been applied.
- ENH: Scripts roxtract.sql and roxecute.sql include now System Parameters with non-default or with modified values. They also report on all initialization paramaters for the connecting instance.
- ENH: All SQLT main methods ask now for the SQLTXPLAIN password in-line instead of using an ACCEPT command.
What is new, is that 3 satellite tools got also a new version. They can be downloaded from their own MOS note. They are also packaged within SQLT under directory sqlt/utl:
SQL Health-Check (SQLHC) 3-minutes video: “…a small tool for big problems!”
A new short video about the SQL Health-Check (SQLHC) script is now available to the entire Oracle community. My special thanks to Robert Story and Steve Dixon for making this possible. See video at MOS Doc ID 1455583.1.
SQLHC 11.4.4.4 is now available
SQL Health-Check version 11.4.4.4 was made available on April 2, 2012. Find this tool under MOS 1366133.1.
It include several of the health-checks incorporated into SQLT 11.4.4.4, plus 4 new sections on CBO statistics:
- Tables
- Table Columns
- Indexes
- Index Columns
These new sections had been requested for current users of SQLT.
SQL Health-Check 101
SQLTXPLAIN (SQLT) is a tool widely used to analyze SQL statements in terms of performance. It has a small drawback although: It requires to create a new schema name SQLTXPLAIN, then over a hundred schema objects on it. Some Production environments have small and un-frequent maintenance windows that make it hard to plan on an expedite installation of SQLT. In such cases, this other tool SQL Health-Check (SQLHC) provides a quick workaround.
SQLHC is a script available in My Oracle Support (MOS) under Document Id: 1366133.1. This script does not install any objects into the database, and still provides a very valuable set of Observations which are the result of over 100 health-checks around one SQL statement. It also includes some extra information such as Tables, Indexes and Columns statistics, Execution Plans, SQL performance metrics, etc.
SQLHC does not compete with SQLTXPLAIN in terms of extension, but when installing SQLTXPLAIN is not a short-term option, then SQLHC is a second best.
Carlos Sierra's Tools and Tips 