Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Posts Tagged ‘SQLHC

Oracle Queries are taking different Plans

with 4 comments

I recently saw a post with this title “Oracle Queries are taking different Plans” and since there was a lot of guessing I suggested to use SQLTXPLAIN and find there the root cause. For some reason or another the thread is still open after 20 days, and the guessing continues. Since the problem of “finding the root cause of a change on an execution plan” is old and still common, I decided to talk about it here.

In Oracle, execution plans do change for one SQL over time, that is a fundamental function of the Cost-based Optimizer (CBO). Not only is normal, but expected and desired in most circumstances. What is not desired, is when a SQL that had a good performing plan suddenly changes its plan to one that we consider sub-optimal in terms of performance. This should be the concern and focus. So WHY the CBO suddenly generates such a plan? The possible reasons are many but I enlist here the most common ones I can think of:

  • Schema object statistics changes
  • Small sample sizes when gathering CBO schema statistics (mostly on 10g)
  • Lack of statistics in some columns referenced by predicates
  • Inconsistent values of bind variables referenced by SQL
  • System wide changes like CBO parameters or system statistics
  • Index modifications including new and dropped indexes
  • Invalid or invisible indexes
  • Large SQL text with many predicates where several plans with similar cost are possible
  • Inconsistent ramp-up process of Adaptive Cursor Sharing (ACS)
  • SQL Profiles generated by the SQL Tuning Advisor (STA)
  • An index changing its blevel (for example from blevel 2 to 3)
  • Volatile values in some columns (for example value “Pending” exists in table column for a short time)
  • Asynchronous gathering of stats on volatile data instead of gathering right between load and consumption

The list above is not comprehensive. It only includes what came to my mind while writing it, so I may have forgotten several cases. And of course there are always some corner cases like special conditions activating uncommon heuristics, or simply hitting a CBO bug.

Guessing WHY a plan has changed is very time consuming and requires multiple queries to the metadata on the database. Since most of the metadata we are interested on is dynamic by nature, we base our guess work on an ever changing environment. Of course the root cause become elusive and we may give up for some time, or jump to false conclusions.

This is where SQLTXPLAIN (MOS 215187.1) or its younger and lighter sister SQLHC (MOS 1366133.1) come to the rescue. These free tools, specially SQLTXPLAIN (SQLT), capture the static and dynamic environment around the one SQL we want to analyze.

By having a consistent view of all elements affecting the execution plan, it becomes much easier to find the root cause of a plan change. And since SQLT also includes current and past details about parameters, statistics and bind variable values, we can even correlate those values to prior execution plans. So I encourage you to try SQLTXPLAIN on a SQL next time you are confronted by the old question: “WHY Oracle Queries are taking different Plans?”.

I am aware SQLTXPLAIN is intimidating. Its output is exuberant, but the point is that it includes what we need to find the root cause of most if not all SQL Tuning cases where a plan has changed.

I have presented this SQLT tool in several conferences during the past year or so. I think it is appropriate now to put together a 3 days workshop to master this tool… Just an idea at this time.

Advertisements

SQLTXPLAIN (SQLT) 11.4.4.6 is now available

leave a comment »

So what is new in SQLT 11.4.4.6? 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 ,

3 minutes cool video on SQL Health-Check SQLHC

with 4 comments

SQL Health Check…a small tool for big problems

Sorry, this link only works inside Oracle… Anyways, basically it is a cool short video that explains what SQLHC does. If you ever need a super lite version of SQLT that installs nothing, use SQLHC. Still you may want to install SQLT as time permits. SQLHC is not a replacement for SQLT.

Written by Carlos Sierra

April 24, 2012 at 5:12 pm

Posted in SQL Healh-Check (SQLHC)

Tagged with

SQLHC 11.4.4.4 is now available

leave a comment »

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:

  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