statistics_level parameter options

Unsetting the default statistics_level=basic will disable AWR and the advisory utilities.
statistics_level=typical activates the advisory statistics collections, but it does not activate “Timed OS statistics” and “Plan Execution Statistics”.  To activate these statistics collection, you must set statistics_level=all.

SQL> ALTER SYSTEM SET statistics_level=basic;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO

MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO

PGA Advice                     DISABLED   DISABLED   TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO

Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               DISABLED   DISABLED   TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=typical;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=all;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      ENABLED    ENABLED    ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            ENABLED    ENABLED    ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES