Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Should I delete my column Histograms to improve plan stability?

with 4 comments

When we combine the use of bind variables in a SQL statement with column Histograms we usually end-up having multiple execution plans over time. This plan instability is due to the lack of control we have over the selectivity of the predicates at the time the SQL is hard parsed while using bind peeking. On 11g this behavior is mitigated with the use of Adaptive Cursor Sharing (ACS), and it can be further improved when using ACS together with SQL Plan Management (SPM).

As we generate multiple execution plans for one SQL statement where the reason is the combination of binds and Histograms, some of these plans may perform better than others over a large number of executions. Thus it is natural to consider some of these execution plans as “bad plans”. If the use of ACS and SPM is not an option, then we may need to consider fixing the plan instability issue with the suppression of either bind peeking or Histograms. If we are in such case, it is better to consider the removal of Histograms first, and leave bind peeking active. The reason is that bind peeking even with no Histograms still uses CBO column statistics and among other things it uses column low and high values when computing selectivity.

Back to the question: Should I delete my column Histograms to improve plan stability?

If you are on 10g, or if on 11g but ACS is not an option, then deleting column Histograms may be your best option to improve your plan stability. Like any other change, you rather test first on a non-production environment. Even in such test environment, you may want to restrict your test to only those tables accessed by your SQL. On 11g, DBMS_STATS.DELETE_COLUMN_STATS provides an option to delete just the Histograms while preserving all other column statistics. This is by using parameter COL_STAT_TYPE with value HISTOGRAM. If you want to do the same on 10g, you may want to use the SQLT HGRM module. In any case you can always restore column statistics using DBMS_STATS.RESTORE_TABLE_STATS.

If you are considering deleting Histograms to test the effect on an execution plan and the performance of your SQL, you may want to test first asking the CBO to simply ignore them. If patch for bugĀ 9550277 has been applied in your system then you can command below.

ALTER SESSION SET "_FIX_CONTROL"='9550277:1';

Written by Carlos Sierra

August 13, 2012 at 8:33 am

4 Responses

Subscribe to comments with RSS.

  1. Hi,
    looks like MOS knows nothing about 9550277 is that internal or something ?:)
    Regards
    GregG

    goryszewskig

    August 13, 2012 at 11:55 am

    • 9550277 was created to extend some functionality discussed on 9495669. Basically it provides a fix control to disable the use of Histograms with a hidden parameter, allowing to test the effect of not having Histograms without deleting them.

      Carlos Sierra

      August 13, 2012 at 3:13 pm

  2. Carlos, looks like it is a separate patch, we need to install (not included on 11.2.0.3) and it is also no where in MOS.

    Can you shed some light on this ? Will it be public at any point in time or is this for you internal tests ?

    coskan

    August 14, 2012 at 4:41 am

    • Very good point. Since no customer has requested a backport for 9550277 it is not available externally. Feel free to open a ticket with Support and request a backport for this bug. As of today, there is a new bug 14494284 requesting the inclusion of this 9550277 on 11.2.0.4.

      Carlos Sierra

      August 14, 2012 at 9:53 am


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,388 other followers

%d bloggers like this: