STATISTICS_LEVEL in Oracle

The STATISTICS_LEVEL parameter was introduced in Oracle version 9 (9.2) to control all
major statistics collections or advisories in the database. The level of the setting affects the
a number of statistics and advisories that are enabled. These listed below are for 10g and above

How to set this parameter

This is a dynamic parameter and does not require any database restart

ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;

There is no impact of setting this parameter to the session level for the values BASIC and TYPICAL. But it can be set to ALL at the session level to gather timed OS statistics and plan execution statistics

We should not STATISTICS_LEVEL to ALL at the instance level, as this will cause some stability issues due to the heavy overhead associated with the collection of timed operating system statistics and row source execution statistics

How to get the current value of the parameter

show parameter statistics_level

How to get the activation level setting for each value

We can use the below query to get the activation level

SELECT statistics_name, session_status, system_status,
activation_level, session_settable
FROM v$statistics_level
ORDER BY statistics_name;

Let’s check for each setting

BASIC

ALTER SYSTEM SET statistics_level=basic;
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

TYPICAL

ALTER SYSTEM SET statistics_level=typical;
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

ALL

ALTER SYSTEM SET statistics_level=all; 
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

We can clearly see from the table, statistics_level=ALL will be valid for session-level also