1z0-043 lesson 9 Automatic Performance Management

1z0-043 lesson 9

网页链接多了就给当成了spam blog, 太蠢了!
我不得不说谷歌也够垃圾的

完成
基本上都是瞎猜的

是否显示我的答案
是否显示书上的答案和我的注释


1.
1. You observe that a database performance has degraded over a period of time. While investigating the reason, you find that the size of the database buffer cache is not large enough to cache all the needed data blocks. Which advisory component would you refer to, in order to determine that required size of the database buffer cache?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

A
做过

A
上一章做过

2.
51. You want to use the SQL Tuning Advisor to generate recommendations for badly written SQL statements in your development environment. Which three sources can you select for the advisor to analyze? (Choose three.)
A. Top SQL
B. snapshots
C. SQL Tuning sets
D. index access path
E. optimizer statistics
F. materialized view logs

ABE
不知道

ABC
教材9-29

3.
54. You find that the execution time of reports in your datawarehouse application is significantly high. You suspect the lack of indexes to be the reason for the degradation in performance. Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisor
B. Segment Advisor
C. SQL Access Advisor
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)

C
?

C
教材9-30
SQL Access Advisor是建议创建索引,物化视图的

4.
107. In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)

C

C
教材9-21

5.
131. Which three methods can you use to run an Automatic Database Diagnostic Monitor (ADDM) analysis over a specific time period? (Choose three.)
A. Enterprise Manager GUI
B. DBMS_TRACE package APIs
C. DBMS_ADVISOR package APIs
D. DBMS_MONITOR package APIs
E. $ORACLE_HOME/rdbms/admin/addmrpt.sql script

ACE

ACE

见
6.2.4 Diagnosing Database Performance Issues with ADDM(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#sthref333)
While the simplest way to run an ADDM analysis over a specific time period is with the Oracle Enterprise Manager GUI, ADDM can also be run manually using the $ORACLE_HOME/rdbms/admin/addmrpt.sql script and DBMS_ADVISOR package APIs. The SQL script and APIs can be run by any user who has been granted the ADVISOR privilege.

参考:
12 DBMS_ADVISOR(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advis.htm)
17.1 Overview of the SQL Access Advisor in the DBMS_ADVISOR Package(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/advisor.htm#CHDEEBAI)
Running the Segment Advisor Manually with PL/SQL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2113)
The Undo Advisor PL/SQL Interface(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1492)

=====


6.
30. You have created a new table. How long do you have to wait to be sure that Oracle has automatically gathered optimizer statistics for the table and loaded them into the AWR?
A. Not more than 30 minutes.
B. Up to seven days.
C. Until the next scheduled automated statistics collection job is run.
D. Optimizer statistics are not stored in the AWR.
E. You must gather the statistics manually when a new table is created. Oracle will collect them automatically after that.

C
建索引是立即统计的,好像

30. D. The AWR does not store optimizer statistics. It stores dynamic performance statistics. Optimizer statistics are stored in the data dictionary. See Chapter 8 for more information.

破题,文字游戏

7.
31. You are concerned about the operating system performance as well as SQL execution plan statistics. Which STATISTICS_LEVEL parameter would be the minimum required to ensure that Oracle will collect these types of statistics?
A. BASIC
B. TYPICAL
C. ADVANCED
D. ALL
E. None of these settings will achieve the desired result.

B
忘了

31. D. The STATISTICS_LEVEL parameter must be set to ALL in order to instruct Oracle to automatically collect operating system and SQL execution plan statistics. See Chapter 8 for more information.

教材9-9
Timed system statistics are automatically collected for the database if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC, then you must set TIMED_STATISTICS to TRUE to enable the collection of timed statistics. Note that setting STATISTICS_LEVEL to BASIC disables many automatic features and is not recommended.

见STATISTICS_LEVEL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref640)
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

=====


8.
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.

D

1. 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.

见6.2.1 ADDM Analysis Results(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#sthref318)
Each ADDM finding can belong to one of three types:
* Problem: Findings that describe the root cause of a database performance issue.
* Symptom: Findings that contain information that often lead to one or more problem findings.
* Information: Findings that are used for reporting non-problem areas of the system.

9.
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

D

2. 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.

教材9-9没讲

见STATISTICS_LEVEL
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.

10.
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.

C
怎么会有这种题?

3. 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.

见Setting the DBIO_EXPECTED parameter(http://download.oracle.com/docs/cd/B19306_01/server.102/b28051/tdppt_auto.htm#TDPPT024)
6.2.3 Setting Up ADDM(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#CHDHAEJD)

11.
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

E

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

见6.2 Automatic Database Diagnostic Monitor(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#PFGRF02602)
The types of problems that ADDM considers include the following:
* CPU bottlenecks - Is the system CPU bound by Oracle or some other application?
* Undersized Memory Structures - Are the Oracle memory structures, such as the SGA, PGA, and buffer cache, adequately sized?
* I/O capacity issues - Is the I/O subsystem performing as expected?
* High load SQL statements - Are there any SQL statements which are consuming excessive system resources?
* High load PL/SQL execution and compilation, as well as high load Java usage
* RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?
* Sub-optimal use of Oracle by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?
* Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?
* Concurrency issues - Are there buffer busy problems?
* Hot objects and top SQL for various problem areas

12.
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

A

5. 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.

教材9-21

13.
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

E

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

见12 DBMS_ADVISOR(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advis.htm#sthref66)
文档上竟然没有RESUME_TASK.但其实有

PROCEDURE RESUME_TASK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN

14.
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

D

7. 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.

常识

15.
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

B

8. 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.

见Other Background Processes(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1626)
MMON performs various manageability-related background tasks, for example:
* Issuing alerts whenever a given metrics violates its threshold value
* Taking snapshots by spawning additional process (MMON slaves)
* Capturing statistics value for SQL objects which have been recently modified
MMNL performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
MMAN is used for internal database tasks.

16.
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

AD

9. 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.

教材9-33
12.2.2 Tuning Options(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#CHDFHCIF)
SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.
* If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.
* If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.

17.
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

A

10. 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.

见DBA_THRESHOLDS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4159.htm#sthref1916)
OBSERVATION_PERIOD NUMBER Observation period length (in minutes)
CONSECUTIVE_OCCURRENCES NUMBER Number of occurrences before an alert is issued

GET_THRESHOLD Procedure(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm#sthref5781)
observation_period The period at which the metric values are computed and verified against the threshold setting.
consecutive_occurrences The number of observation periods the metric value should violate the threshold value before the alert is issued.

18.
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.

E

11. 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.

见Application and SQL Tuning(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2212)

12.1 Automatic SQL Tuning Overview(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i36217)
In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

这里说生成执行计划了呀, 答案A也对?

19.
12. The AWR resides in which tablespace?
A. SYSTEM
B. TOOLS
C. SYSAUX
D. AWR
E. None of the above

C

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

教材9-21
The AWR resides in the SYSAUX tablespace.

20.
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

A

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

见14.3.1.2 Parallel Statistics Gathering(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i42217)
The statistics-gathering operations can run either serially or in parallel. The degree of parallelism can be specified with the DEGREE argument to the DBMS_STATS gathering procedures. Parallel statistics gathering can be used in conjunction with sampling. Oracle Corporation recommends setting the DEGREE parameter to DBMS_STATS.AUTO_DEGREE. This setting allows Oracle to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters.

21.
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

A

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

见Automatically Collecting Statistics on Tables(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2270)
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.

22.
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.

D

15. 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.

没有ABC
见DBA_ADVISOR_FINDINGS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3007.htm)
DBA_ADVISOR_FINDINGS displays the findings discovered by all advisors in the database.

12.5 SQL Tuning Information Views(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i35947)
This section summarizes the views that you can display to review information that has been gathered for tuning the SQL statements. You need DBA privileges to access these views.
* Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE views.
* SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
* SQL Tuning Set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
* Information on captured execution plans for statements in SQL Tuning Sets are displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS views.
* SQL Profile information is displayed in the DBA_SQL_PROFILES view.
* Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.
* Dynamic views containing information relevant to the SQL tuning, such as V$SQL, V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.

23.
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

A

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

见DBA_HIST_WR_CONTROL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3202.htm)
DBA_HIST_WR_CONTROL displays the control information for the Workload Repository.

5.3.4 Using Automatic Workload Repository Views(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i35169)
Typically, you would view the AWR data through Oracle Enterprise Manager or AWR reports. However, you can also view the statistics with the following views:
* V$ACTIVE_SESSION_HISTORY
This view displays active database session activity, sampled once every second. See "Active Session History (ASH)".
* V$ metric views provide metric data to track the performance of the system
The metric views are organized into various groups, such as event, event class, system, session, service, file, and tablespace metrics. These groups are identified in the V$METRICGROUP view.
* DBA_HIST views
The DBA_HIST views contain historical data stored in the database. This group of views includes:
o DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity.
o DBA_HIST_BASELINE displays information about the baselines captured on the system
o DBA_HIST_DATABASE_INSTANCE displays information about the database environment
o DBA_HIST_SNAPSHOT displays information on snapshots in the system
o DBA_HIST_SQL_PLAN displays the SQL execution plans
o DBA_HIST_WR_CONTROL displays the settings for controlling AWR

24.
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

B

17. B. The ASH buffer will be set to the lesser of:
(Total number of CPUs × 2MB)
8 CPUs × 2MB = 16MB
or
(Shared Pool size × 5%)
1GB × 5% = 50MB
Therefore, the ASH will be sized at 16MB.

见metalink doc. 243132.1
10g and above Active Session History (Ash) And Analysis Of Ash Online And Offline(https://metalink.oracle.com/CSP/main/showdoc?db=not&id=243132.1)
ASH Memory Size
==============
Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]

25.
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

C

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

没找着

26.
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

AC

19. 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.

见101 DBMS_SQLTUNE(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm)
The DBMS_SQLTUNE package provides the interface to tune SQL statements.

27.
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.

D

20. 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.

见Setting Metric Thresholds(http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune001.htm#sthref738)
For more comprehensive management of threshold settings for the different alerts, click the radio button in the Select column for that metric, then click Specify Multiple Thresholds.
The Specify Multiple Thresholds: metric_name page appears. This page enables you to specify metric thresholds for each object.
...

Specifying Multiple Thresholds(http://download.oracle.com/docs/cd/B19306_01/em.102/b25986/howto.htm#sthref22)
The Specifying Multiple Thresholds functionality allows you to define various subsets of data that can have different thresholds. By specifying multiple thresholds, you can refine the data used to trigger alerts, which are one of the key benefits of using Enterprise Manager.The key in specifying multiple thresholds is to determine how the comparison relates to the metric threshold as a whole. What benefit will be realized by defining a more stringent or lax threshold for that particular device, mount point, and so on?For example, using the Average Disk I/O Service Time metric, you can define warning and critical thresholds to be applied to all disks (sd0 and sd1), or you can define different warning and critical thresholds for a specific disk (sd0). This allows you to adjust the thresholds for sd0 to be more stringent or lax for that particular disk.

=====



-fin-