STATISTICS_LEVEL parameter in Oracle

 

In Oracle as we gather and view statistics though different views or tables can be controlled by instance level parameter STATISTICS_LEVEL
This particular parameter has below three possible settings

• BASIC
• TYPICAL
• ALL

The parameter controls automatic gathering of statistics at two levels 

Instance statistics : statistics accumulate within instance regarding activity

Object statistics : stats accumulated on objects within database

The instance statistics accumulated in memory and flushed to AWR ( automatic workload repository) by MMON (manageability monitor) background process

Object statistics are gathered by DMBS_STATS procedure calls

Now we will discuss about parameter setting 

TYPICAL : The default setting is TYPICAL, this setting will gather all statistics needed by self management and tuning capabilities of the database and will also enable automatic object analysis task that runs daily in maintenance window

BASIC: The BASIC setting disable  AWR statistics and disable daily analysis

ALL : ALL setting gathers all possible statistics, these include operating system statistics and very detailed statistics on SQL statement execution. This shortcoming with this method is it will impact performance adversely.

Suggestion : this parameter should always be kept default and of ALL setting is required should be kept for short term.

This parameter can be altered runtime using simple alter system command

alter system set STATISTICS_LEVEL= BASIC;

alter system set STATISTICS_LEVEL= TYPICAL;

alter system set STATISTICS_LEVEL= ALL;

and same can be viewed using

show parameter  STATISTICS_LEVEL