Oracle 10g New Features - Ch 3 Automating Management

Review Questions
1. To enable the Automatic Workload Repository performance statistic collection at a minimal scale, the STATISTICS_LEVEL parameter must be set to what?
A. TYPICAL
B. NONE
C. ALL
D. BASIC

----
Ans: A.

The valid values for STATISTICS_LEVEL parameter are BASIC, TYPICAL, and ALL. BASIC disables the AWR and other statistics collection, TYPICAL collects statistics needed for day-to-day monitoring, and ALL collects statistics for manual diagnosis.


2. Which process is responsible for analyzing the AWR information for the ADDM?
A. PMON
B. MMON
C. ADDM
D. SNPn

---
Ans: B.
The new process MMON is responsible for writing the ASH information to the Automatic Workload Repository and also analyzing the statistics each time an AWR snapshot is taken.

3. Which data dictionary view would you query to find out the stateless (event-based or nonthreshold) server alerts?
A. DBA_ALERT_LOG
B. DBA_OUTSTANDING_ALERTS
C. DBA_ALERT_HISTORY
D. DBA_ADVISOR_FINDINGS

---
Ans: C.
The stateless alerts are always written to DBA_ALERT_HISTORY. DBA_OUTSTANDING_ALERTS will have the alerts for threshold or stateful alerts. When the status of such alerts is CLEARED, they will be moved to DBA_ALERT_HISTORY.

4. Which of the following is not an out-of-the-box server-generated alert?
A. "Tablespace space usage"
B. "Recovery area low on free space"
C. "Resumable session suspended"
D. "Tables missing optimizer statistics"
E. "Snapshot too old"

---
Ans: D.
In Oracle 10g, the optimizer statistics for tables, including the dictionary, are collected automatically unless the STATISTICS_LEVEL parameter is set to BASIC. The tables have the MONITORING feature enabled by default. So, no need exists for such an alert to look for missing optimizer statistics.

5. Which parameters enable the Automatic Shared Memory Management feature? (Choose two.)
A. SGA_SIZE
B. SGA_TARGET
C. AUTO_SGA
D. STATISTICS_LEVEL

---
Ans: B, D.
Setting the SGA_TARGET and leaving the STATISTICS_LEVEL to its default (TYPICAL) enables ASMM. SGA_TARGET specifies the total size of the SGA, including the manually configured areas. SGA_TARGET cannot be higher than SGA_MAX_SIZE.

6. Which component of the SGA is not automatically configured when Automatic Shared Memory Management is enabled?
A. Java pool
B. Buffer cache
C. Log buffer
D. Large pool

---
ANs: C.
The log buffer should be configured using the LOG_BUFFER parameter. Setting SGA_TARGET automatically manages SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, and BUFFER_CACHE.
C.

7. Which process is responsible for allocating the various components of the SGA when Automatic Shared Memory Management is used?
A. PMON
B. SMON
C. MMON
D. MMAN

---
Ans: D.
The memory manager process (MMAN) is responsible for managing the components of SGA when the Automatic Shared Memory Management feature is used. It serves as a memory broker, coordinates the sizing of the memory components, and keeps track of the component's sizes.

8. How is automatic undo retention tuning enabled?
A. Set the UNDO_RETENTION parameter to zero.
B. Set the UNDO_MANAGEMENT parameter to AUTO.
C. Configure it using the DBMS_ADVISOR package.
D. Set the UNDO_RETENTION parameter to nonzero value.

---
Ans: B. By keeping UNDO_MANAGEMENT set to AUTO (this is the default when creating a new database), the Automatic Undo Retention Tuning feature is enabled in Oracle 10g. If you set a value for the UNDO_RETENTION parameter, Oracle 10g uses that value as the minimum. If no value or zero is set for UNDO_RETENTION, Oracle 10g uses 900 seconds as the default minimum.

9. Identify the statement that is not true regarding the Automatic Optimizer Statistics Collection feature in Oracle 10g.
A. After creating an Oracle 10g database, the DBA does not have to perform any special activity to keep the optimizer statistics current.
B. After upgrading an Oracle database to Oracle 10g, the DBA does not have to perform any special activity to keep the optimizer statistics current.
C. The statistics are kept current by periodically (automatic) executing the DBMS_STATS.GATHER_DATABASE_STATS procedure with the GATHER AUTO option.
D. For the automatic statistic collection to work properly, the STATISTICS_LEVEL must be set to TYPICAL or ALL.

---
Ans: C. The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure collects the missing statistics and update statistics in the database. This procedure prioritizes the database objects that require statistics so that the objects that need the statistics most are processed first, before the maintenance window closes.

10. Identify two statements that are true regarding the procedures of DBMS_STATS.
A. Every time you collect statistics on a table, schema, or database using DBMS_STATS, the previous version of statistics is stored in the AWR.
B. The retention period of optimizer statistics stored in the AWR is determined by the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (RETENTION=> nn) procedure.
C. When you lock statistics on a table, the schema level statistics collection skips the table.
D. Restoring statistics from the AWR is based on the time stamp when the statistics were collected.

---
Ans: A, C.
The retention period of the statistics history kept in the AWR is 31 days by default, which can be modified by using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. The statistics from the AWR are restored using the time stamp as an argument to restore statistics as of that time stamp (not when the statistics are collected).

11. The following are a few of the steps involved in getting the tuning advice from AWR snapshots using the DBMS_ADVISOR PL/SQL API.
1. Use the DBMS_ADVISOR.SET_TASK_PARAMETERS procedure.
2. Use the DBMS_ADVISOR.CREATE_TASK procedure.
3. Use the DBMS_ADVISOR.GET_TASK_REPORT procedure.
4. Use the DBMS_ADVISOR.EXECUTE_TASK procedure.
In which order should be these steps executed?
A. 1, 3, 4, 2
B. 1, 2, 3, 4
C. 2, 1, 4, 3
D. 2, 1, 3, 4

---
Ans: C.
To get tuning advice using the advisory framework DBMS_ADVISOR package, you should create the task first, set appropriate parameters for the task (specify the start snapshot and end snapshot), perform analysis, and optionally get a report or query from the DBA_ADVISOR_FINDINGS view.

12. Choose the most appropriate statement regarding collecting optimizer statistics:
A. The DBMS_STATS package and the ANALYZE statement have same behavior.
B. When statistics are collected using the ANALYZE statement, previous version of statistics is saved in the Workload Repository.
C. When statistics are collected using the DBMS_STATISTICS package, the previous version of statistics is saved in the Workload Repository.
D. When gathering statistics using the DBMS_STATS package, you must specify the SAVE_VERSION parameter to save the old statistics to the Workload Repository.

--
Ans: C.
Oracle10g saves the statistics to the workload repository before setting new statistics values using the DBMS_STATS package. Statistics are not preserved when using the ANALYZE statement.

13. Which parameter of the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure ensures that at the end of the switched resource group call, the session is returned back to its original resource group?
A. SWITCH_TIME_IN_CALL
B. SWITCH_TIME
C. SWITCH_GROUP
D. SWITCH_BACK_AT_CALL_END

---
Ans: A.
The SWITCH_TIME and SWITCH_TIME_IN_CALL parameters are mutually exclusive, and they specify the time in seconds that a session can execute before an action is taken. The default for both parameters is NULL. The SWITCH_TIME_IN_CALL parameter specifies that the session should be returned to the original consumer group at the end of the call, where as when using the SWITCH_TIME, the session remains in the switched consumer group. Whether you use SWITCH_TIME or SWITCH_TIME_IN_CALL, the SWITCH_GROUP parameter specifies the resource group to which the session will be switched if the switch criteria are met.

14. In the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure, the MAX_IDLE_TIME parameter is used to define what?
A. The maximum amount of time a session is inactive and is blocking another session. The session will be switched to switch group defined when the amount of time is met.
B. The total amount of time a session is inactive or idle cumulative since the session startup. The session will be terminated when the criteria is met.
C. The maximum amount of time a session is idle. The session will be switched to switch group defined when the amount of time is met.
D. The maximum amount of time a session is idle. The session will be terminated when the criteria is met.

---
Ans: D.
The MAX_IDLE_TIME parameter defines the maximum amount of time in seconds a session can be inactive or idle. When the session exceeds the limit, the PMON process will terminate the session and clean up its state. The MAX_IDLE_BLOCKER_TIME parameter defines the maximum amount of time in seconds a session can be idle and block the acquisition of resources for another user.

15. Identify two statements that are not true regarding AWR.
A. The snapshot data is not purged from the AWR for the snapshots that are part of a baseline.
B. The automatic capturing of AWR snapshots is disabled by dropping or disabling the corresponding job using DBMS_SCHEDULER.
C. To get AWR report in HTML format, you must use the EM Database Control.
D. No data migration from STATSPACK to AWR is supported.

-----
Ans: B, C.
You can disable the automatic capturing of AWR snapshots by setting the INTERVAL parameter to zero in the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. You can produce AWR reports in HTML format using SQL*Plus by specifying the report type as HTML or text. The snapshots that are older than the retention period defined are not purged when they are part of a baseline. Though STATSPACK is still available in Oracle 10g, no integration exists between the data collected by STATSPACK and AWR.


16. The alerts generated by the database server are delivered to the DBA using which method?
A. Writing the alert to the database alert log file
B. Sending e-mail to the DBA using the e-mail address specified when creating the database
C. Using a trigger on the DBA_OUTSTANDING_ALERTS view
D. Using DBMS_AQ procedures

---
Ans: D.
The alerts generated by the server are queued to the AQ mechanism of the database, a predefined persistent queue ALERT_QUE. You may write your own subscribing programs. You can use the EM Database Control to enable paging and e-mail to the DBA. You may also query DBA_OUTSTANDING_ALERTS to view the alerts that are not resolved. A message about the alert is written to the alert log file only if the alert cannot be written to the ALERT_QUE.

17. When is the ADDM analysis performed?
A. Every time an AWR snapshot is taken
B. Whenever an AWR snapshot is taken automatically by the MMON process
C. Whenever an AWR snapshot is taken using the CREATE_SNAPSHOT procedure
D. Every hour or at specified interval irrespective on AWR snapshots

---
Ans: A.
ADDM is run by the MMON process every time an AWR snapshot is performed—automatic or manual. Each time a snapshot is taken, ADDM is triggered to do an analysis of the period corresponding to the last two snapshots. You may run the addmrpti.sql script to analyze the data between two different snapshot IDs.
D.

18. Identify a true statement from the following regarding Automatic Shared Memory Management.
A. ASMM is enabled by default.
B. ASMM is disabled by setting SGA_TARGET to zero.
C. SGA_TARGET cannot be altered dynamically using ALTER SYSTEM.
D. When SGA_TARGET is specified as a nonzero value, all the SGA parameters related to the autotuned components must be set to zero.
E. The MMON process is responsible for coordinating the sizing of the memory components.

---
Ans: B.
The default value for SGA_TARGET is zero, which means ASMM is not enabled by default. SGA_TARGET can be increased or decreased dynamically within the limits of the SGA_MAX_SIZE parameter. When ASMM is used and a value for the automatically tuned component is also specified, the value would be considered as the minimum required size for the component. The memory manager process (MMAN) is the SGA memory broker and is responsible for the sizing of the SGA components.

19. The FORCE=>TRUE option of the DBMS_STATS.DELETE_TABLE_STATS procedure is used for what?
A. To delete statistics from the table, even if the table is read only.
B. To delete statistics from the table and to clear out all the SQL statements referring to the table from the shared pool.
C. To delete statistics from the table, even if the statistics on the table are locked.
D. The value of FORCE must be TRUE always, as this parameter is reserved for a future enhancement.

----
Ans: C.
You can use the FORCE=>TRUE option with the DELETE_*_STATS, IMPORT_*_STATS, RESTORE_*_STATS, and SET_*_STATS procedures of the DBMS_STATS package to overwrite the statistics even if they are locked.
C.

20. Which of the following is true about Automatic Checkpoint Tuning?
A. You do not need to specify the LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT parameter.
B. You do not need to specify the FAST_START_MTTR_TARGET parameter.
C. You do not need to specify the LOG_BUFFER parameter.
D. All of the above are true.

--
Ans: A.
Automatic Checkpoint Tuning is enabled if you specify a value for the FAST_START_MTTR_TARGET or if you do not specify any value for this parameter. Automatic Checkpoint Tuning is disabled when FAST_START_MTTR_TARGET is set to zero explicitly. You do not need to specify any of the checkpoint-related parameters when the FAST_START_MTTR_TARGET parameter is set.