About STATISTICS_LEVEL parameter in Oracle

•The parameter STATISTICS_LEVEL is a string type parameter and it can take any of three values- TYPICAL, ALL or BASIC.

•It specifies the level of collection for database and operating system statistics.

•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.

•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.

•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).

•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL

Or,

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,

SQL> COL STATISTICS_NAME FORMAT A50
SQL>  SELECT STATISTICS_NAME,SESSION_STATUS,SYSTEM_STATUS,ACTIVATION_LEVEL,
SESSION_SETTABLE FROM V$STATISTICS_LEVEL;

STATISTICS_NAME                                    SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice                                ENABLED  ENABLED  TYPICAL NO
MTTR Advice                                        ENABLED  ENABLED  TYPICAL NO
Timed Statistics                                   ENABLED  ENABLED  TYPICAL YES
Timed OS Statistics                                DISABLED DISABLED ALL     YES
Segment Level Statistics                           ENABLED  ENABLED  TYPICAL NO
PGA Advice                                         ENABLED  ENABLED  TYPICAL NO
Plan Execution Statistics                          DISABLED DISABLED ALL     YES
Shared Pool Advice                                 ENABLED  ENABLED  TYPICAL NO
Modification Monitoring                            ENABLED  ENABLED  TYPICAL NO
Longops Statistics                                 ENABLED  ENABLED  TYPICAL NO
Bind Data Capture                                  ENABLED  ENABLED  TYPICAL NO

STATISTICS_NAME                                    SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics                         ENABLED  ENABLED  TYPICAL NO
Threshold-based Alerts                             ENABLED  ENABLED  TYPICAL NO
Global Cache Statistics                            ENABLED  ENABLED  TYPICAL NO
Active Session History                             ENABLED  ENABLED  TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up              ENABLED  ENABLED  TYPICAL NO

16 rows selected.

As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).