OCP 1Z0 052 43

43. In your database instance, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is 
the impact of this setting? 
A.Optimizer statistics are collected automatically. 
B.Only timed operating system (OS) statistics and plan execution statistics are collected. 
C.The snapshots for the Automatic Workload Repository (AWR) are not generated automatically. 
D.Snapshots cannot be collected manually by using the DBMS_WORKLOAD_REPOSITORY package. 
E.The  Oracle  server  dynamically  generates  the  necessary  statistics  on  tables  as  part  of  query 
optimization. 
Answer: C

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 TYPICALsetting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the 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