You determine the level of statistics collection on the database by setting the value of the STATISTICS_LEVEL parameter. The values for this parameter are:
• BASIC: No advisory or other statistical data is collected. You can manually set other statistic collection parameters such as TIMED_STATISTICS and DB_CACHE_ADVICE.Many of the statistics required for a performance baseline are not collected. Oracle strongly recommends that you do not disable statistic gathering.
• TYPICAL: This is the default value. Data is collected for segment-level statistics, timed statistics, and all advisories. The value of other statistic collection parameters is overridden.
• ALL: Collection is made of all the TYPICAL level data, the timed operating system statistics, and the row source execution statistics. The value of other statistic collection parameters is overridden.
SQL>select statistics_name,activation_level from v$statistics_level order by 2;
The following statistic parameters can also be set individually TIMED_STATISTICS: Set to TRUE to collect statistics related to time.
• DB_CACHE_ADVICE: Accepts the following values:
– OFF: No statistics collected and no memory used : No statistics collected and no memory used
– READY: No statistics collected, but memory is allocated. Setting
DB_CACHE_ADVICE to READY before setting it to ON prevents memory errors when collecting statistics on buffer cache utilization.
– ON: Statistics collected and memory allocated. Changing the status of
DB_CACHE_ADVICE from OFF to ON can raise an error if the required memory is not available.
• TIMED OS STATISTICS TIMED_OS_STATISTICS: Specifies the interval (in seconds) at which an Oracle : Specifies the interval (in seconds) at which an Oracle
instance collects operating system statistics when a request is made from the client to the server or when a request completes
When STATISTICS_LEVEL is modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system wide state is not changed.
• Timed statistics
• Timed OS statistics
• Plan execution statistics