Statistics_Level

Prompted by a recent question on the MOSC community forum (link needs support account) I thought I’d dust off this little script (that I wrote nearly 20 years ago for Oracle 9.2) so see if it still worked, needed any new columns, or added extra rows in 21c.

The script is just a simple report of v$statistics_level, which reports the various real-time statistics collections and advisors that could be enabled, the view that holds associated results (where relevant) and the “activation_level” – in effect telling you whether or not it is necessary to set the statistics_level to “all” before you can enable a particular collection.

The results are not as helpful as you might hope, however, and may result in a mild panic attack if you are prone to getting worried about licensing requirements.

rem
rem     Script:         statistics_level.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2002
rem     Purpose:        Simple script to pick up 9.2 statistics activation details
rem

column statistics_name          format a40
column statistics_view_name     format a24
column description              format a64 word_wrapped

column system_status            heading "Sys"
column session_Status           heading "Ses"
column session_settable         heading "Set"

break on activation_level skip 1

set linesize 160
set pagesize  90
set trimspool on

spool statistics_level

select
        activation_level,
        statistics_name,
        statistics_view_name,
        system_status,
        session_status,
        session_settable,
        description
from
        v$statistics_level
order by
        activation_level,
--      length(description) desc,
        statistics_name
;

clear breaks

spool off

By default the script has to be run by the SYS user, and you’ll note that I’ve omitted the con_id column, which always seemed to be zero whether I query from the root or from a pluggable database on my 21.3 instance.

Here are the results I got from the root container with the statistics_level set to typical:

ACTIVAT STATISTICS_NAME                          STATISTICS_VIEW_NAME     Sys      Ses      Set DESCRIPTION
------- ---------------------------------------- ------------------------ -------- -------- --- ----------------------------------------------------------------
ALL     Plan Execution Statistics                V$SQL_PLAN_STATISTICS    DISABLED DISABLED YES Enables collection of plan execution statistics
        Timed OS Statistics                                               DISABLED DISABLED YES Enables gathering of timed operating system statistics

TYPICAL Active Session History                   V$ACTIVE_SESSION_HISTORY ENABLED  ENABLED  NO  Monitors active session activity using MMNL
        Adaptive Thresholds Enabled                                       ENABLED  ENABLED  NO  Controls if Adaptive Thresholds should be enabled
        Automated Maintenance Tasks                                       ENABLED  ENABLED  NO  Controls if Automated Maintenance should be enabled
        Automatic DBOP Monitoring                V$SQL_MONITOR            ENABLED  ENABLED  YES Controls if automatic DBOP Monitoring should be enabled
        Bind Data Capture                        V$SQL_BIND_CAPTURE       ENABLED  ENABLED  NO  Enables capture of bind values used by SQL statements
        Buffer Cache Advice                      V$DB_CACHE_ADVICE        ENABLED  ENABLED  NO  Predicts the impact of different cache sizes on number of
                                                                                                physical reads

        Column Tracking Level                                             ENABLED  ENABLED  YES Sets Up Column Tracking Level
        Global Cache Statistics                                           ENABLED  ENABLED  NO  RAC Buffer Cache statistics
        Longops Statistics                       V$SESSION_LONGOPS        ENABLED  ENABLED  NO  Enables Longops Statistics
        MTTR Advice                              V$MTTR_TARGET_ADVICE     ENABLED  ENABLED  NO  Predicts the impact of different MTTR settings on number of
                                                                                                physical I/Os

        Modification Monitoring                                           ENABLED  ENABLED  NO  Enables modification monitoring
        OLAP row load time precision                                      ENABLED  ENABLED  YES Sets precision of olap row load time statistics
        Object Activity Tracking                                          ENABLED  ENABLED  YES Sets Up Object Activity Tracking (OATS)
        PGA Advice                               V$PGA_TARGET_ADVICE      ENABLED  ENABLED  NO  Predicts the impact of different values of pga_aggregate_target
                                                                                                on the performance of memory intensive SQL operators

        Plan Execution Sampling                  V$ACTIVE_SESSION_HISTORY ENABLED  ENABLED  YES Enables plan lines sampling
        SQL Monitoring                           V$SQL_MONITORING         ENABLED  ENABLED  YES Controls if SQL Monitoring should be enabled
        Segment Level Statistics                 V$SEGSTAT                ENABLED  ENABLED  NO  Enables gathering of segment access statistics
        Shared Pool Advice                       V$SHARED_POOL_ADVICE     ENABLED  ENABLED  NO  Predicts the impact of different values of shared_pool_size on
                                                                                                elapsed parse time saved

        Streams Pool Advice                      V$STREAMS_POOL_ADVICE    ENABLED  ENABLED  NO  Predicts impact on Streams perfomance of different  Streams pool
                                                                                                sizes

        Threshold-based Alerts                                            ENABLED  ENABLED  NO  Controls if Threshold-based Alerts should be enabled
        Time Model Events                        V$SESS_TIME_MODEL        ENABLED  ENABLED  YES Enables Statics collection for time events
        Timed Statistics                                                  ENABLED  ENABLED  YES Enables gathering of timed statistics
        Ultrafast Latch Statistics                                        ENABLED  ENABLED  NO  Maintains statistics for ultrafast latches in the fast path
        Undo Advisor, Alerts and Fast Ramp up    V$UNDOSTAT               ENABLED  ENABLED  NO  Transaction layer manageability features
        V$IOSTAT_* statistics                                             ENABLED  ENABLED  NO  Controls if I/O stats in v$iostat_ should be enabled


27 rows selected.

The key thing to note from this output is that there are only two statistics collections that are enabled by setting statistics_level to all, the “Plan Execution Statistics” and the “Timed OS Statistics”. Make the change and you’ll see these two statistics reported as ENABLED. If you look behind the scenes you’ll also find that the parameter “timed_os_statistics” has changed from 0 to 60 (and v$sysstat now starts reporting values for the statistics with names like ‘%OS%’) and the hidden parameter “_rowsource_execution_statistics” has changed from false to true (and when you execute new queries and use the option format=>’allstats [last]’ in calls to dbms_xplan.display_cursor you get execution stats appearing in every line of the resulting execution plan).

Guideline: do not set statistics_level to all at the system level, the overheads can be significant. You might want to set it occasionally for a single session for a brief interval to investigate a performance problem – bearing in mind that setting the parameter might actually introduce a whole new performance problem.

User-friendly: NOT

The question on the forum that prompted this note was asking if there were licensing implications of setting the statistics_level, in particular whether there was any requirement to license the diagnostic and performance packs. I didn’t reply to the question – someone else took it on – but the answer is no.

If you look at the output above, though you’ll see that it reports both “Active Session History” and “Plan Execution Sampling” as ENABLED – when I know that I’ve set the parameter control_management_pack_access to none. And when I query v$active_session_history the rowcount is always zero – so it’s not enabled.

Without going through the manuals, checking the dynamic performance views in the output above, and looking for related parameters (e.g. view V$MTTR_TARGET_ADVICE and parameter fast_start_mttr_target) I can’t really be sure what it means to say that an entry in the output is “Enabled”.

I wonder if my query to check whether v$active_session_history was populated will have set the feature audit to say that I’ve used the active session history!

Footnote

There have been a couple of changes over time in this view. In 12.2, for example, I noted a statistic called “Global Cache CPU Statistics” which wasn’t present in 21.3; conversely in 21.3 I noted a statistic “Object Activity Tracking” that wasn’t present in 12.2

You could disable most of the stats collections and advisors at the system level by setting the statistics level to “basic” – but (a) you might find that the attempt to do so raised Oracle errors if you haven’t previously disabled some of the default mechanism e.g:

ORA-32017: failure in updating SPFILE
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

and (b) you will probably find one day that 20/20 hindsight tells you that really could have used some of the advisors to find out why your production system is misbehaving.