OCP Oracle 10g Admin II - Ch 8: Understanding Automatic Database Management

Chapter 8: Understanding Automatic Database Management
Review Questions
.
1. Which of the following is not a valid ADDM finding type?
A. Error
B. Problem
C. Information
D. Symptom
E. All are valid ADDM finding types.
-----
Ans: 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.


2. What is the default setting for the STATISTICS_LEVEL initialization parameter?
A. BASIC
B. ALL
C. STANDARD
D. TYPICAL
E. None of the above
----
Ans: 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.

3. Which of the following would be used to set or change the value for DBIO_EXPECTED?
A. The DBIO_EXPECTED initialization parameter
B. The ALTER SYSTEM statement
C. The ALTER SESSION statement
D. All of the above will work.
E. None of the above will work.
----
Ans: 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.

4. Which of the following are types of problems that the ADDM will consider?
(Choose all that apply.)
A. Database configuration issues
B. Concurrency issues
C. CPU bottlenecks
D. Suboptimal use of Oracle by an application
E. All of the above
---
Ans: E.
ADDM considers all of these problem areas and more when performing its analysis.

5. The statistical data needed for ADDM to accurately diagnose problems is
stored in which of the following areas?
A. Automatic Workload Repository (AWR)
B. Data dictionary
C. ADDM repository
D. PERFSTAT tables
E. None of the above
---
Ans: 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.

6. Which of the following is not a valid DBMS_ADVISOR procedure or function?
A. CREATE_TASK_SCRIPT
B. RESTART_TASK
C. INTERRUPT_TASK
D. RESUME_TASK
E. None of the above
---
ANs: B.
There is no RESTART_TASK procedure or function. Choices A, C, and D all
represent valid procedures. E is obviously wrong.

7. In an RAC environment, which element(s) always uniquely identifies a snapshot?
A. INSTANCE_ID
B. CLUSTER_ID
C. SNAP_ID
D. Both A and C
E. A, B, and C
---
Ans: 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.

8. If the ASH buffer is filled in less than 30 minutes, which process is responsible for flushing it?
A. SMON
B. MMON
C. MMNL
D. PMON
E. AMON
---
Ans: 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.

9. Which are valid SCOPE settings for the SQL Tuning Advisor? (Choose all that apply.)
A. Full
B. Comprehensive
C. Detailed
D. Basic
E. Limited
---
Ans: 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.

10. If a metrics threshold is defined with an OBSERVATION_PERIOD setting of 5 and
a CONSECUTIVE_OCCURRENCES setting of 2, an alert would be generated after
how many minutes of consecutive violations?
A. 10
B. 15
C. 20
D. 5
E. None of the above
---
ANs: 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.

11. Which of the following statements regarding the Automatic Tuning Optimizer (ATO) is incorrect?
A. The ATO generates more efficient execution plans than the query optimizer in normal mode.
B. The ATO is nothing more than the query optimizer in tuning mode.
C. The ATO can take a long time to process a SQL statement, especially when performing a comprehensive analysis.
D. The ATO does generate a SQL Profile when performing a limited analysis.
E. All the above statements are correct.
---
Ans: 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.

12. The AWR resides in which tablespace?
A. SYSTEM
B. TOOLS
C. SYSAUX
D. AWR
E. None of the above
---
Ans: C.
The AWR is the primary occupant of the SYSAUX tablespace.

13. Which DBMS_STATS function is used to allow Oracle to dynamically choose an
appropriate parallel degree based on the size of the object and the values of
certain initialization parameters?
A. AUTO
B. CHOOSE
C. DETECT
D. AUTO_DEGREE
E. BEST_DEGREE
----
Ans: D.
The AUTO_DEGREE function chooses the most appropriate degree of parallelism
based on factors such as object size and init.ora settings.

14. Which Oracle process updates the data dictionary with DML activity
information gathered by the Automatic DML Monitoring feature?
A. SMON
B. PMON
C. MMNL
D. DMON
E. MMON
---
Ans: A.
The SMON process updates the data dictionary with DML activity information
approximately every three hours, as well as at instance shutdown.

15. Which of the following views can be queried to display advisor findings?
A. V$ADVISOR_FINDINGS
B. DBA_ADV_FINDINGS
C. DBA_ADV_RECOMMENDATIONS
D. DBA_ADVISOR_FINDINGS
E. Findings can be viewed only through Enterprise Manager.
----
Ans: 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.

16. Which view shows the current AWR settings?
A. DBA_AWR_SETTINGS
B. DBA_AWR_CONFIG
C. DBA_HIST_WR_CONTROL
D. DBA_HIST_WR_SETTINGS
E. DBA_REPOSITORY
---
Ans: C.
The DBA_HIST_WR_CONTROL view shows information about the AWR, including
the current settings.

17. On an eight-CPU system with a Shared Pool size of 1GB, what size would the
ASH buffer be set to?
A. 8MB
B. 16MB
C. 50MB
D. 32MB
E. 32KB
---
Ans: 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.

18. _______________ is a hash value that uniquely identifies a SQL statement in
the database.
A. SQL_HASH
B. SQL_HASH_ID
C. SQL_ID
D. SID
E. SPID
---
Ans: C.
SQL_ID, new in Oracle 10g, is a hash value that uniquely identifies a single SQL
statement within a database.

19. Which package(s) can be used to interface with the SQL Tuning Advisor?
(Choose all that apply.)
A. DBMS_ADVISOR
B. DBMS_SQL_TUNE
C. DBMS_SQLTUNE
D. DBMS_SQL_ADVISOR
E. DBMS_SQL_TUNE_ADV
---
Ans: 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.

20. On the Edit Thresholds screen in Oracle Enterprise Manager, what does it
mean if the Select button is disabled for a specific metric?
A. The metric is disabled on the current instance.
B. The metric is stale.
C. The metric does not support multiple thresholds.
D. The threshold is locked and cannot be modified.
E. None of the above.
---
Ans: 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.