Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

19 September 2011 11 Comments Written by Christian Antognini

The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the STATISTICS_LEVEL, also the V$STATISTICS_LEVEL view provides a list of the ones it controls.

SQL> SELECT statistics_name, description, activation_level
  2  FROM v$statistics_level
  3  ORDER BY 3 DESC, 1;

STATISTICS_NAME                        DESCRIPTION                                                  ACTIVATION_LEVEL
-------------------------------------- ------------------------------------------------------------ ----------------
Active Session History                 Monitors active session activity using MMNL                  TYPICAL
Adaptive Thresholds Enabled            Controls if Adaptive Thresholds should be enabled            TYPICAL
Automated Maintenance Tasks            Controls if Automated Maintenance should be enabled          TYPICAL
Bind Data Capture                      Enables capture of bind values used by SQL statements        TYPICAL
Buffer Cache Advice                    Predicts the impact of different cache sizes on number of    TYPICAL
                                       physical reads
Global Cache Statistics                RAC Buffer Cache statistics                                  TYPICAL
Longops Statistics                     Enables Longops Statistics                                   TYPICAL
MTTR Advice                            Predicts the impact of different MTTR settings on number of  TYPICAL
                                       physical I/Os
Modification Monitoring                Enables modification monitoring                              TYPICAL
PGA Advice                             Predicts the impact of different values of pga_aggregate_tar TYPICAL
                                       get on the performance of memory intensive SQL operators
Plan Execution Sampling                Enables plan lines sampling                                  TYPICAL
SQL Monitoring                         Controls if SQL Monitoring should be enabled                 TYPICAL
Segment Level Statistics               Enables gathering of segment access statistics               TYPICAL
Shared Pool Advice                     Predicts the impact of different values of shared_pool_size  TYPICAL
                                       on elapsed parse time saved
Streams Pool Advice                    Predicts impact on Streams perfomance of different  Streams  TYPICAL
                                       pool sizes
Threshold-based Alerts                 Controls if Threshold-based Alerts should be enabled         TYPICAL
Time Model Events                      Enables Statics collection for time events                   TYPICAL
Timed Statistics                       Enables gathering of timed statistics                        TYPICAL
Ultrafast Latch Statistics             Maintains statistics for ultrafast latches in the fast path  TYPICAL
Undo Advisor, Alerts and Fast Ramp up  Transaction layer manageability features                     TYPICAL
V$IOSTAT_* statistics                  Controls if I/O stats in v$iostat_ should be enabled         TYPICAL
Plan Execution Statistics              Enables collection of plan execution statistics              ALL
Timed OS Statistics                    Enables gathering of timed operating system statistics       ALL

Something that I learned only recently is that STATISTICS_LEVEL also controls cardinality feedback and adaptive cursor sharing. This fact, according to me, is neither (clearly) documented nor pointed out by the information provided by V$STATISTICS_LEVEL. In any case, when STATISTICS_LEVEL is set to BASIC at the system level both features are disabled. Interestingly, an ALTER SESSION SET STATISTICS_LEVEL = TYPICAL it is not enough to enable them… For adaptive cursor sharing it is possible to use the BIND_AWARE hint, though.

Note that I never advise to set STATISTICS_LEVEL at the system level to a value that is different from the default (TYPICAL). Probably for this reason I didn’t notice its impact for such a long time…

In any case I find it a bit disappointing that this information is not clearly stated somewhere. Or I’m the only one that was not aware?

11gR1, 11gR2, Query Optimizer
optimizer_secure_view_merging and VPD
Ad: Der Oracle DBA – Handbuch für die Administration der Oracle Database 11gR2

11 Comments

3 Pings/Trackbacks

  1. Bryan Grenn Bryan Grenn
    19 September 2011    

    I don’t think you are the only one. There is a lot of “black magic” around cardinality feedback, that is not documented.

    Reply
  2. Sigrid Keydana Sigrid Keydana
    20 September 2011    

    Hi Chris,

    that’s very interesting (and curious!), How did you find out (possibly by just experimenting)? What’s not clear to me from your post, is it just BASIC that disables those two, or do you need to set ALL to get them (I’d not worry about case 1 too much as we don’t have BASIC set on any dbs anyway)?
    Also, I’d always thought about ALL as something you have to set to get the actual rows etc., but now I’ve stumbled about the timed os statistics – a quick search on the web seems to indicate that these, too, are not too well documented (what they are, how they are collected, what’s the impact etc) – or am I wrong here?

    Best greetings
    Sigrid

    Reply
    • Christian Antognini Christian Antognini
      20 September 2011    

      Hi Sigrid

      > How did you find out (possibly by just experimenting)?

      Somebody asked me during a class. I didn’t know the answer and, therefore, I did a test.

      > is it just BASIC that disables those two

      Yes, it’s just BASIC. By default (TYPICAL) the two features are enabled.

      > or am I wrong here?

      You are right. FWIW, I personally never used those statistics.

      Cheers,
      Chris

      Reply
  3. Book Review: Oracle Database 11g Performance Tuning Recipes (Part 2) « Charles Hooper's Oracle Notes Book Review: Oracle Database 11g Performance Tuning Recipes (Part 2) « Charles Hooper's Oracle Notes
    6 October 2011    

    […] disable it.”  Technically, this is not a true statement.  Although not recommended, setting the STATISTICS_LEVEL parameter to BASIC will disable adaptive cursor sharing.  Per Metalink (MOS) Doc ID 11657468.8, adaptive cursor […]

  4. Yasir Hashmi Yasir Hashmi
    12 October 2011    

    Dear Sir,
    When are you planning to release the 11g updated version of TOP?

    Reply
    • Christian Antognini Christian Antognini
      12 October 2011    

      Hi

      TOP covers 11.1. Since there are not sooooo many differences with 11.2, I would say that most of the content is relevant for 11.2 as well. That said, I am thinking about working on a second edition. But, if I do it, I will cover 12c as well. Hence, do not expect it before Q1 2013.

      Cheers,
      Chris

      Reply
  5. The elements of Bind-Aware Cursor Sharing and Cardinality Feedback in Oracle 11g « Oracle mechanics The elements of Bind-Aware Cursor Sharing and Cardinality Feedback in Oracle 11g « Oracle mechanics
    12 November 2011    

    […] OPT_ESTIMATE hint arguments come from other sources, which depend on STATISTICS_LEVEL: setting  STATISTICS_LEVEL=BASIC on system level disables ACS and CF (as well as sql plan monitoring feature) – Christian Antognini Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing […]

  6. Bob Bob
    13 September 2013    

    We just had an incident where having statistics_level=all was affecting rowsets. In other words, having statistics_level=all might result in queries returning wrong results (omitting rows). This is incredible to me. Anyway, this is bug 13004894 and it hasn’t been fixed yet in 11g r2.

    Why did we have it set to ALL? Because when using TYPICAL the execution plans dont show actual number of rows which makes identifying incorrect cardinality estimations very difficult.

    Reply
    • Christian Antognini Christian Antognini
      15 September 2013    

      Hi Bob

      This is a critical bug! I was not aware of it. Thank you very much for pointing it out.

      Best,
      Chris

      Reply
  7. Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback | Oracle mechanics Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback | Oracle mechanics
    7 March 2015    

    […] Аргументы OPT_ESTIMATE при этом берутся из отдельных источников (доступных в отсутствие данных в V$SQL_PLAN_MONITOR), которые также, как и данные SQL Plan Monitoring зависят от значения парамера STATISTICS_LEVEL на уровне инстанса: STATISTICS_LEVEL=BASIC отключает BACS и CF — см. Christian Antognini Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing […]

  8. Jared Still Jared Still
    6 November 2020    

    Hi Chris,

    I found this blog when I started wondering why the optimizer does not use the info from statistics_level =ALL to supply cardinality feedback.

    Apparently it does. :)

    Though this blog is 9 years old, I still cannot find any substantive information about this

    Reply
  1. Book Review: Oracle Database 11g Performance Tuning Recipes (Part 2) « Charles Hooper's Oracle Notes on 6 October 2011 at 07:07
  2. The elements of Bind-Aware Cursor Sharing and Cardinality Feedback in Oracle 11g « Oracle mechanics on 12 November 2011 at 21:48
  3. Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback | Oracle mechanics on 7 March 2015 at 19:23

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.