OCP-043 STATISTICS_LEVEL initialization parameter

STATISTICS_LEVEL

Property	Description
Parameter type	String
Syntax	STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
Default value	TYPICAL
Modifiable	ALTER SESSION, ALTER SYSTEM

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disablesthe collection of many of the important statistics required by Oracle Database features and functionality, including:

• Automatic Workload Repository (AWR) Snapshots

• Automatic Database Diagnostic Monitor (ADDM)

• All server-generated alerts

• Automatic SGA Memory Management

• Automatic optimizer statistics collection

• Object level statistics

• End to End Application Tracing (V$CLIENT_STATS)

• Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

• Service level statistics

• Buffer cache advisory

• MTTR advisory

• Shared pool sizing advisory

• Segment level statistics

• PGA Target advisory

• Timed statistics

• Monitoring of statistics

Note:

Oracle strongly recommends that you do not disable these important features and functionality.

When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value ofSTATISTICS_LEVEL. When 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

The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter

74. In your running instance, some of the initialization parameters are set as shown below:
SGA_MAX_SIZE = 14GB
DB_CACHE_SIZE = 1GB
SHARED_POOL_SIZE = 3GB
STATISTICS_LEVEL = BASIC

PGA_AGGREGATE_TARGET = 0

You plan to enable Automatic Shared Memory Management but you are not able to set SGA_TARGET to
a nonzero value. What could be the reason?

A.The STATISTICS_LEVEL initialization parameter is set to BASIC.
B.The PGA_AGGREGATE_TARGET initialization parameter is set to zero.
C.The SGA_MAX_SIZE initialization parameter is set to less than 20 GB.
D.The DB_CACHE_SIZE initialization parameter is set to less than 5 GB.
E.The SHARED_POOL_SIZE initialization parameter is set to a nonzero value.

Answer: A