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
Leave a Reply