Oracle DBA 19c / 23ai Performance Tuning Quiz

1. To Enable automatic statistics Collection and automatic performance features, which of the following value must be set for the initialization Parameter STATISTICS_LEVEL

A. Either TYPICAL or ALL               B. BASIC

C. NONE                                D. AUTO

Answer: A

Note: To enable automatic statistics collection and automatic performance features, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. 

2. Which of the following index type is suitable for columns with low number of distinct values.

A. B-Tree Index                        B. Reverse Key Index

C. Bitmap Index                        D. Partitioned Index

Answer: C

3. By Default, What is the frequency that Oracle follows to automatically generates AWR snapshots of the performance data ?

A. Once every 6 Hour                   B. Once every 24 Hour

C. Once every 4 Hour                   D. Once every 1 Hour

Answer: D

Note: By default, Oracle Database generates snapshots once every hour, and retains the statistics in the workload repository for 8 days. 

4. By Default, for how many days, Oracle retains the AWR performance statistics data ?

A. 1 Day                               B. 8 Days

C. 7 Days                              D. 30 Days

Answer: B

Note: By default, Oracle Database generates snapshots once every hour, and retains the statistics in the workload repository for 8 days. 

5. Which of the following database package is used to manage AWR snapshots (Such as Create, Drop, modify settings of the AWR Snapshots) in the command-line interface?

A. DBMS_AWR                                      B. DBMS_WORKLOAD_REPOSITORY

C. DBMS_SNAPSHOTS                        D. DBMS_SNAP

Answer: B

Note: The primary interface for managing snapshots is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, you should manage snapshots using Cloud Control.
If Cloud Control is unavailable, then manage snapshots using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.