A. All ADDM analysis results are categorized as Problem, Symptom, or Information findings. Problem represents the root problem identified, Symptom identifies a symptom resulting from the root problem, and Information reports on non-problem areas. ADDM does not classify its findings as Errors.

D. The default setting for STATISTICS_LEVEL is TYPICAL, which ensures that adequate statistics are gathered to support all of the automatic database management features.

E. The value for DBIO_EXPECTED is set by using the DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER procedure. It is not an initialization parameter value, so answers A, B, and C cannot be correct, because all of them deal with initialization parameters.

E. ADDM considers all of these problem areas and more when performing its analysis.

A. ADDM, along with all the other advisors, utilize the AWR. The data dictionary stores optimizer statistics, whereas ADDM deals with performance statistics, so choice B is wrong. There is no such thing as an ADDM repository; therefore, choice C is wrong. And PERFSTAT tables are a part of Statspack, which is no longer used in Oracle 10g.

B. There is no RESTART_TASK procedure or function. Choices A, C, and D all represent valid procedures. E is obviously wrong.

D. Both A and C are required to uniquely identify snapshots across Real Application Clusters (RACs). In a stand-alone environment, the SNAP_ID is guaranteed to be unique. However, with RAC, a snapshot shares the same SNAP_ID across all instances, so the INSTANCE_ID is used to differentiate between them.

C. While MMON is normally tasked with flushing the ASH buffer every 30 minutes, the MMNL process performs the task if the buffer fills before that time. SMON and PMON don't have any interaction with the ASH flush, and AMON is not a valid Oracle process name.

B, E. SQL Tuning Advisor can perform either Comprehensive or Limited analysis, based on the SCOPE setting. Answers A, C, and D are all invalid settings for the SCOPE parameter.

B. The CONSECUTIVE_OCCURRENCES parameter defines the number of allowable violations without an alert. This number must be exceeded before an alert is generated. Because the values are checked every five minutes (as defined by the OBSERVATION_PERIOD parameter), an alert would be generated after 15 minutes.

A. The Automatic Tuning Optimizer (ATO) does not generate execution plans at all. Instead, it will return recommendations for optimization, along with their expected benefit and rationale.

C. The AWR is the primary occupant of the SYSAUX tablespace.

D. The AUTO_DEGREE function chooses the most appropriate degree of parallelism based on factors such as object size and init.ora settings.

A. The SMON process updates the data dictionary with DML activity information approximately every three hours, as well as at instance shutdown.

D. The DBA_ADVISOR_FINDINGS view shows the findings from any advisor execution. Though they can also be viewed through Enterprise Manager, answer E is incorrect.

C. The DBA_HIST_WR_CONTROL view shows information about the AWR, including the current settings.

B. The ASH buffer will be set to the lesser of: (Total number of CPUs × 2MB)

or

(Shared Pool size × 5%)

8 CPUs × 2MB = 16MB

1GB × 5% = 50MB

Therefore, the ASH will be sized at 16MB.

C. SQL_ID, new in Oracle 10g, is a hash value that uniquely identifies a single SQL statement within a database.

A, C. DBMS_SQLTUNE is designed specifically as the interface to the SQL Tuning Advisor. However, the DBMS_ADVISOR package can be used to interface with all advisors, including the SQL Tuning Advisor.

C. Multiple thresholds can be set only on aggregated metrics with underlying granularity. For example, Blocking Session Count aggregates all underlying blocking sessions. Therefore, you can set thresholds for individual sessions as well as for the aggregate metric.