Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Posts Tagged ‘Health-Check

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

SQLTXPLAIN (SQLT) is now available

leave a comment »

So what is new in SQLT Honestly, not much: one fix and 6 enhancements:

  1. 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.
  2. 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.
  3. ENH: New HC detecting corrupt histograms as per bugs 8543770, 10267075, 12819221 and 12876988.
  4. 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.
  5. ENH: New HC about CBO not using partition level histograms if stats were gathered with “INCREMENTAL” and fix for bug 13583722 has been applied.
  6. 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.
  7. 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:

  1. Script: sqlhc.sql – SQL Health-Chech (1366133.1).
  2. Script: pxhcdr.sql – Parallel Execution Health-Check and Diagnostics Reports (1460440.1). NEW!
  3. Script: profiler.sql – HTML report out of PL/SQL Profiler DBMS_PROFILER data (243755.1).

SQL Health-Check (SQLHC) 3-minutes video: “…a small tool for big problems!”

with 16 comments

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.

Written by Carlos Sierra

May 4, 2012 at 3:04 pm

Posted in SQL Healh-Check (SQLHC)

Tagged with ,

SQLHC is now available

leave a comment »

SQL Health-Check version was made available on April 2, 2012. Find this tool under MOS 1366133.1.

It include several of the health-checks incorporated into SQLT, plus 4 new sections on CBO statistics:

  1. Tables
  2. Table Columns
  3. Indexes
  4. Index Columns

These new sections had been requested for current users of SQLT.

Written by Carlos Sierra

April 2, 2012 at 2:45 pm

Posted in SQL Healh-Check (SQLHC)

Tagged with ,

SQL Health-Check 101

leave a comment »

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.

Written by Carlos Sierra

April 1, 2012 at 1:57 pm