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 a number of statistics and advisories that are enabled. These listed below are for 10g and above
- The TYPICAL level is recommended for most production databases to enable the optimizer to generate better execution plans.
- The ALL level incurs a higher overhead for statistics collection and can consume more disk space due to the increased number of statistics stored. It is typically used for advanced performance-tuning scenarios or when fine-grained optimization control is required.
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 set
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
In conclusion, the STATISTICS_LEVEL parameter in Oracle plays a crucial role in determining the extent of statistics collection and retention. It directly affects the accuracy of the optimizer’s execution plans and, consequently, the performance of SQL statements. Choosing the appropriate level is a balancing act between the overhead of statistics collection and the benefits gained in query optimization and overall database performance.
Related Articles
How to gather Statistics with DBMS_STATS Procedures
Histograms in Oracle
How to check gather stats on a table
