OCP课程55：管理II之管理数据库性能-CSDN博客

课程目标：

性能监控
数据库重放

1、调优活动

性能调优包括性能计划、实例调优和SQL调优三个方面。

性能计划是建立环境的过程：硬件、软件、操作系统、网络基础设施等。
实例调整是数据库参数和操作系统参数的实际调整，以获得更好的数据库性能。
SQL调优涉及使你的应用程序提交有效的SQL语句。SQL调优可以对应用程序作为一个整体进行，也可以对单个语句进行。

关于性能调优的更多信息，请参阅Oracle Database Performance Tuning Guide。

2、性能计划

性能计划涉及到很多方面。计划必须包括在性能（速度），成本和可靠性之间的平衡。必须考虑系统架构的成本：满足需求的硬件和软件基础设施。需要对给定的环境，应用程序和性能要求进行分析。例如，硬盘和控制器的数量对数据访问速度的影响。

应用程序的扩展能力也是很重要的。这意味着能够处理越来越多的用户、客户端、会话或交易，而不会对整个系统的性能产生巨大的影响。不满足可扩展性最明显例子是用户操作排队进行。如果所有用户依次通过一个单一的路径进行访问，随着用户数量的增多，肯定会对性能产生不利影响。这是因为越来越多的用户排队通过这条路径。写得不好的SQL也影响可扩展性，它需要用户等待低效的SQL语句完成执行。

应用程序设计原则可以极大地影响性能。设计简洁，使用视图和索引以及数据建模是非常重要的。

应用程序必须在有代表性的工作负载下进行测试。这就要求估计数据库的大小和负载，并生成测试数据和系统负载。

改变基础环境时对现有应用进行测试是必要的，同时也是困难的。例如升级数据库到新版本，更改操作系统或服务器。但有时需要在应用部署到新环境之前，需要提前知道对其的影响并进行额外的调整。

3、实例调优

在任何调优活动开始时，都要有明确的目标。相比用户提出“让系统跑的越快越好”这种要求，诸如“每分钟处理500个销售事务”这种目标更容易达到。

必须适当地分配数据库内存以获得最佳性能。由于内存有限，如果给某些组件分配很少的内存，不去分析的话，会很难发现后台进程效率低下。

磁盘I/O也是数据库常见的瓶颈，因此，在数据库开始运行就需要密切关注。

操作系统的配置也会影响数据库的性能。更多信息，可以参考特定平台的数据库安装指南。

4、性能调优方法论

Oracle多年经验总结的调优方法论，基本步骤是：

（1）调优实例之前，检查操作系统的统计数据和机器的健康状况，确保是数据库的问题。可以使用ME的主页查看。

（2）从应用程序设计开始，到程序编码，到实例，按此顺序进行调优。例如，在通过调整磁盘上表空间的位置来消除全表扫描造成I/O争用之前，建议先确认该I/O争用是否是由于应用程序代码导致的。

（3）定位最大的瓶颈和并进行调整。很多优化工具可以识别SQL语句，资源争用或消耗最多时间的服务。数据库提供了一个时间模型和指标来自动识别瓶颈。

（4）当达到调优目标时停止调优。

5、性能监控

如果知道性能已经改变，就需要对性能的改变作出反应。Oracle数据库11g提供监控数据库实例当前性能的几种方法。EM主页可以快速检查实例和服务器的监控状况，以图表显示CPU使用率、活动会话和SQL的响应时间。该主页还显示任何已触发的警报。

EM的性能页面从几个角度显示性能指标图。可以看到CPU，平均活动会议，吞吐量，I/O及其他性能指标。从性能页面，可以获取详细信息链接：包括会话和单个SQL语句。

EM显示的信息基于数据库中的性能视图。可以直接用SQL*Plus访问这些视图。

6、性能调优数据

数据库服务器软件捕捉到它自己的操作的信息。收集三种主要类型的数据：累积统计，度量和抽样统计。

累积统计是数据库服务器中发生的各种事件的计数和时间信息。有些是相当重要的，如缓冲区忙等待（buffer busy waits）。有些对调优没有什么用，如索引块拆分（index block split）。对于调优最重要的事件通常是那些显示出最大累积时间值的事件。在Oracle数据库11g中的统计数据与时间模型相关联。时间模型的统计数据基于DB time，DB time提供了一个通用的比较基准。

度量是每单位统计计数。该单位可能是时间（如秒），事务或会话。度量提供了一个主动监测性能的基础。可以设定一个度量的阈值以便生成告警。例如可以设置阈值为之前每毫秒读取的最大值或者归档日志区使用率的95%。

抽样统计是当参数STATISTICS_LEVEL设置为TYPICAL或者ALL时自动产生的统计数据。可以查看在过去收集的会话和系统统计数据。

7、优化器统计数据收集

优化程序统计数据是数据库对象的特定信息的集合。这些统计数据对于查询优化程序为每个SQL语句选择最佳执行计划是必不可少的。定期收集统计数据，收集间隔期间统计数据不会改变。

收集优化程序统计数据的推荐方法是允许数据库自动收集统计信息。自动维护任务可以在数据库创建时自动创建，并由调度程序管理。它收集数据库中的所有对象的统计数据。可以通过自动维护任务页面更改默认配置。

系统统计数据描述了用于查询优化程序的系统硬件特性，如I/O和CPU的性能和利用率。当选择执行计划时，该优化程序估计每个查询所需的I/O和CPU资源。系统统计数据使查询优化程序能够更准确地估计I/O和CPU的成本，从而选择一个更好的执行计划。系统采用dbms_stats.gather_system_stats程序收集统计数据。当数据库收集系统统计信息时，在一个特定的时间内分析系统活动。系统统计数据不会自动收集。Oracle建议使用dbms_stats包收集系统统计数据。

如果选择不使用自动统计数据收集，则必须手动收集所有模式中的统计数据，包括system模式。如果数据库中的数据经常发生变化，也需要定期收集统计数据以确保统计数据准确地代表数据库对象的特性。手动收集统计，使用dbms_stats包。这个PL/SQL包也可以用来修改，查看，导出，导入和删除统计数据。

还可以通过数据库初始化参数管理优化程序和系统统计数据收集。例如：

optimizer_dynamic_sampling参数控制优化器执行的动态采样级别。当统计数据不存在或者过期，可以使用动态采样来估计表和相关索引的统计数据。当收集到的统计数据不可用或有可能导致严重错误估计时，可以使用动态采样估计单表谓词的选择性。

statistics_level参数控制所有主要的统计数据收集或建议以及设置统计数据收集级别。此参数的值可以是BASIC，TPYICAL和ALL。可以查询V$statistics_level视图来确定哪些参数是受statistical_level参数影响。注意：不推荐设置statistics_level为BASIC，会禁用许多自动功能。

SQL> show parameter statistics_level;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

SQL> select statistics_name,activation_level,statistics_view_name from v$statistics_level;

STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME

---------------------------------------- ------- ------------------------------

Buffer Cache Advice TYPICAL V$DB_CACHE_ADVICE

MTTR Advice TYPICAL V$MTTR_TARGET_ADVICE

Timed Statistics TYPICAL

Timed OS Statistics ALL

Segment Level Statistics TYPICAL V$SEGSTAT

PGA Advice TYPICAL V$PGA_TARGET_ADVICE

Plan Execution Statistics ALL V$SQL_PLAN_STATISTICS

Shared Pool Advice TYPICAL V$SHARED_POOL_ADVICE

Modification Monitoring TYPICAL

Longops Statistics TYPICAL V$SESSION_LONGOPS

Bind Data Capture TYPICAL V$SQL_BIND_CAPTURE

STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME

---------------------------------------- ------- ------------------------------

Ultrafast Latch Statistics TYPICAL

Threshold-based Alerts TYPICAL

Global Cache Statistics TYPICAL

Global Cache CPU Statistics ALL

Active Session History TYPICAL V$ACTIVE_SESSION_HISTORY

Undo Advisor, Alerts and Fast Ramp up TYPICAL V$UNDOSTAT

Streams Pool Advice TYPICAL V$STREAMS_POOL_ADVICE

Time Model Events TYPICAL V$SESS_TIME_MODEL

Plan Execution Sampling TYPICAL V$ACTIVE_SESSION_HISTORY

Automated Maintenance Tasks TYPICAL

SQL Monitoring TYPICAL V$SQL_MONITORING

STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME

---------------------------------------- ------- ------------------------------

Adaptive Thresholds Enabled TYPICAL

V$IOSTAT_* statistics TYPICAL

24 rows selected.

8、统计数据首选项：概览

在Oracle 10gR1引入了自动统计数据收集功能以减轻维护优化程序统计数据的负担。然而有某些情况下必须禁用它而运行自己的脚本。一个原因是缺少对象级控制。当发现对象一个很小的子集的默认收集统计数据选项不起作用，则必须锁定统计数据，使用自己的选项进行单独分析。例如对于值变化很大的字段，自动确定适当的采样大小（estimate_percent = auto_sample_size）就不合适，唯一的方式就是使用自己的脚本手动指定采样大小。

注意：可以使用dba_tab_stat_prefs视图查看所有相关表的所有有效统计数据首选项设置。

9、使用统计数据首选项

此功能允许覆盖GATHER_*_STATS存储过程及在对象或者模式级别进行自动优化程序统计数据收集的默认行为。可以使用dbms_stats包管理收集统计数据选项。

可以在表，模式，数据库和全局级别进行设置、获取、删除、导出、导入这些首选项。全局首选项用于没有设置首选项的表，而数据库首选项用于对所有表设置首选项。

在Oracle 11gR1引入以下选项：

PUBLISH用来决定是否将统计数据发布到字典还是将它们存储在一个挂起的区域之前。
STALE_PERCENT用来指定阈值，该值是自上次统计数据收集以来修改的百分比。超过设定的值表示就需要重新收集统计数据。上图中的例子表示将默认的10%修改为13%。
CNCREMENTAL用来以增量方式收集分区表的全局统计数据。

例子：设置表的统计数据首选项并查询

SQL> exec dbms_stats.set_table_prefs('HR','EMP','STALE_PERCENT','13');

PL/SQL procedure successfully completed.

SQL> select * from dba_tab_stat_prefs;

OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE

---------- ---------- ------------------------------ ----------

HR EMP STALE_PERCENT 13

10、使用EM设置全局首选项

可以使用EM控制全局首选项设置。在主页选项服务器选项卡，点击管理优化程序统计信息，然后选择全局统计信息收集选项链接。

在全局统计信息收集选项页，在收集优化程序统计信息默认选项区修改全局首选项。完成后，单击“应用”按钮。

注意：在对象级或模式级更改统计数据收集选项，单击“管理优化程序统计信息”页上的“对象级别统计信息收集首选项”链接。

11、Oracle等待事件

有时服务器进程或者线程需要等待一个事件完成才能继续进行处理，由此增加的统计数据称之为等待事件。等待事件数据显示可能会影响性能的问题的各种症状，如闩锁争用，缓冲争用以及I/O争用。注意等待事件只是问题的症状而不是实际的原因。

等待事件被分为很多类。等待事件类包括：管理（Administrative）、应用程序（Application）、集群（Cluster）、提交（Commit）、并发（Concurrency）、配置（Configuration）、空闲（Idle）、网络（Network）、其他（Other）、调度（Scheduler）、系统I/O（System I/O）和用户I/O（User I/O）。

数据库中有800多个等待事件，包括空闲缓冲区等待（free buffer wait），闩锁空闲（latch free），缓冲区忙等待（buffer busy waits），数据库文件顺序读（db file sequential read）和数据库文件分散读（db file scattered read）。

使用EM可以通过打开性能页面查看等待事件，并查看“平均活动会话”图，如上图所示。通过点击一个特定的等待事件类的链接，可以通过使用顶级活动界面下钻到特定的等待事件。在这个例子中，有一个非常小的缓冲区忙等待。

例子：查看当前数据库的等待事件

SQL> SELECT name FROM V$EVENT_NAME ORDER BY name;

12、实例统计数据

为了有效诊断性能问题，必须使用统计数据。Oracle数据库实例为系统、会话和单独的SQL语句生成多种类型的累积统计数据。数据库还可以跟踪段和服务的累积统计数据。当分析某个范围内的性能问题时，通常会查看在某个时间段内统计数据的变化。

注意：实例统计数据是动态的，在实例启动时重置。这些统计数据可以在一个时间点捕获并在数据库中以快照形式进行存储。

等待事件统计数据

在V$EVENT_NAME视图中可以查看所有的等待事件类别。

所有会话的累积统计数据都存储在V$SYSTEM_EVENT视图，显示自实例启动以来某个事件总的等待情况。

当排除故障时，需要知道一个进程是否等待过其他资源。

系统统计数据

从V$STATNAME视图中可以查看所有系统统计数据类别，Oracle 11g有超过400种系统统计数据。

在V$SYSSTAT视图中可以查看所有计算过的系统统计数据。可以查询这个视图查找实例启动以来的累积总数。

例子：

SQL> SELECT name, class, value FROM v$sysstat;

NAME CLASS VALUE

------------------------------- ------ ----------

...

table scans (short tables) 64 135116

table scans (long tables) 64 250

table scans (rowid ranges) 64 0

table scans (cache partitions) 64 3

table scans (direct read) 64 0

table scan rows gotten 64 14789836

table scan blocks gotten 64 558542

...

根据调优和诊断目的对系统统计数据进行分类，分类包括通常实例活动，重做日志缓冲区活动，锁，数据库缓冲区缓存活动等。每一种系统统计信息可以属于多类，所有不能将V$SYSSTATS.CLASS和V$SYSTEM_WAIT_CLASS.WAIT_CLASS#简单的连接起来。

可以通过V$SYSTEM_WAIT_CLASS查看特定等待类的所有等待事件。示例如下：

SQL> SELECT * FROM V$SYSTEM_WAIT_CLASS

2 WHERE wait_class LIKE '%I/O%';

CLASS_ID CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED

---------- ------ ------------ ----------- -----------

1740759767 8 User I/O 1119152 39038

4108307767 9 System I/O 296959 27929

SGA全局统计数据

从V$SGASTAT视图查看所有计算过的内存统计数据，找到自实例启动后详细的SGA使用情况的累积总量。实例如下：

SQL> SELECT * FROM v$sgastat;

POOL NAME BYTES

------ ------------------------- ----------

fixed_sga 7780360

buffer_cache 25165824

log_buffer 262144

shared pool sessions 1284644

shared pool sql area 22376876

当STATISTICS_LEVEL参数设置为BASIC时，TIMED_STATISTICS参数的值默认为FALSE。则不会为等待事件收集时间信息，也会禁用大部分性能监控。显式设置TIMED_STATISTICS以覆盖从STATISTICS_LEVEL获取的值。

SQL> show parameter statistics_level;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

SQL> show parameter timed_statistics

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_statistics boolean TRUE

SQL> alter system set statistics_level=basic;

System altered.

SQL> show parameter statistics_level;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string BASIC

SQL> show parameter timed_statistics

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_statistics boolean FALSE

13、监控会话性能

可以通过EM查看会话的等待事件。在性能页面，点击顶级活动查看所有会话的情况。左下方是顶级会话列表，点击会话标识查看会话详细信息。

顶级活动页面和会话详细信息页面数据都是基于数据库的性能视图。

14、显示会话相关统计数据

通过V$SESSION可以查询登录用户的当前会话信息，例如使用V$SESSION确认一个会话是用户会话还是数据库服务器进程创建的会话。

通过V$SESSION或者V$SESSION_WAIT查询活动会话等待的资源或者事件。

通过V$SESSAT和V$SYSSTAT等动态性能视图可以查看实例统计数据的累计值。注意实例关闭时性能视图中的累计值会重置。

通过V$MYSTAT可以查看当前会话的统计信息。

通过V$SESSMETRIC查看所有活动会话的性能度量值。例如CPU使用情况，物理读数量，硬解析梳理和逻辑读比例。

15、显示服务相关统计数据

在多层环境中，应用程序通过连接池连接到数据库，此时单个会话可能不会提供分析性能的信息，通过将会话分组为服务可以提供更精确的性能信息。

V$SERVICE_WAIT_CLASS可以查看每个服务的统计信息。

V$SERVICE_EVENT显示的信息与V$SERVICE_WAIT_CLASS一样。

可以通过EM查看服务信息。

可以使用DBMS_SERVICE包定义服务。

例子：通过EM查看服务信息

16、问题解决及调优视图

通过以上视图可以确定性能问题的原因或者分析数据库当前状态。

17、数据字典视图

当需要查看指定数据库对象的优化统计数据，使用DBMS_STATS包收集统计数据并查询DBA_XXX视图相关字段。

DBA_XXX视图有：

表及字段信息：
- DBA_TABLES
- DBA_TAB_COLUMNS
族表信息：
- DBA_CLUSTERS
索引信息：
- DBA_INDEXES
字段数据分布：
- DBA_TAB_HISTOGRAMS

执行ANALYZE INDEX ... VALIDATE STRUCTURE命令收集索引统计数据到INDEX_STATS和INDEX_HISTOGRAM。

18、自动负载知识库

自动工作负载知识库（AWR）是对系统性能统计数据的收集。AWR位于SYSAUX表空间内。

快照是在某一个时间点捕获的性能统计数据，并存储在AWR中。每个快照都有一个唯一的快照序列号。默认情况下，每60分钟生成一次快照，可以通过改变快照INTERVAL参数来调整这个频率。由于数据库顾问依赖这些快照数据，调整时间间隔设置会影响诊断精度。例如，如果INTERVAL设置为4小时，则可能会错过在60分钟的时间间隔内发生的事件。

可以使用dbms_workload_repository.modify_snapshot_settings存储过程或EM改变控制快照收集的设置。在EM，在服务器标签页的统计管理区域单击“自动工作负载知识库”。然后单击“编辑”进行更改。相比EM，存储过程在定义INTERVAL值时更灵活。

可以使用EM或dbms_workload_repository.create_snapshot存储过程进行手动快照。手动快照可以与自动快照联合使用，当需要在特定的时间点捕获系统行为时，就需要使用手动快照。

例子：更改快照时间间隔

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- -------------------- -------------------- ----------

3001485737 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT

SQL> exec dbms_workload_repository.modify_snapshot_settings(11520,60);

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- -------------------- -------------------- ----------

3001485737 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

例子：手动创建快照

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select snap_id,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot order by 1 desc;

SNAP_ID STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME

---------- ------------------------- ------------------------- -------------------------

806 03-FEB-16 04.47.14.000 PM 13-FEB-16 02.38.00.012 PM 13-FEB-16 03.58.29.278 PM

19、使用自动负载知识库视图

DBA_HIST_DB_CACHE_ADVICE：

DBA_HIST_DISPATCHER：

DBA_HIST_DYN_REMASTER_STATS：

DBA_HIST_IOSTAT_DETAIL：

DBA_HIST_SHARED_SERVER_SYMMARY：

20、真正应用测试概览：数据库回放

当面对硬件升级，操作系统升级，数据库版本升级，要确认在升级后不影响应用正常运行并确保性能不低于升级之前，则需要创建负载测试环境，对应用进行测试。

那么应该如何选取负载呢，使用Database Replay可以捕获生产环境的负载并在测试环境进行重放。

21、整体情况

使用Database Replay可以对新的服务器配置进行性能测试，比如从单实例迁移到RAC，在某个有代表性的时间段对单实例数据库捕获负载并在RAC系统进行重放，监控是否有性能的提升。

Database Replay也可以用于调试（debug）。

相关习题：

（1）View the Exhibit to examine the metrics with a threshold. Which statement is true regarding the Number of Transactions (per second) metric?

此主题相关图片如下：

A.Oracle uses statistical relevance to determine when an adaptive threshold has been breached for the metric.

B.The statistics for the metric values observed over the baseline time period are not examined to determine threshold values.

C.Oracle determines when an adaptive threshold has been breached based on the maximum value captured by the baseline.

D.The total concurrent number of threshold violations, which must occur before an alert is raised for the metric, has been set to zero.

答案：A

（2）View the Exhibit that sets the threshold for the Current Open Cursors Count metric. Why is the Significance Level threshold type not available in the threshold setting?

此主题相关图片如下：

A.because AWR baseline is not enabled

B.because Current Open Cursors Count is not a basic metric

C.because the STATISTICS_LEVEL parameter is set to BASIC

D.because the AWR baseline is a system-defined moving window baseline

答案：B

（3）The INV_HISTORY table is created using the command:

SQL>CREATE T ABLE INV_HISTORY (inv_no NUMBER(3), inv_date DA TE, inv_amt NUMBER(10,2))

partition by range (inv_date) interval (numtoyminterval(1,'month'))

(partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')),

partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy')));

The following data has been inserted into the INV_HISTORY table :

INV_NO INV_DATE INV_AMT

1 30-dec-2004 1000

2 30-dec-2005 2000

3 1-feb-2006 3000

4 1-mar-2006 4000

5 1-apr-2006 5000

You would like to store the data belonging to the year 2006 in a single partition and issue the command:

SQL> AL TER T ABLE inv_history MERGE PARTITIONS

FOR(TO_DA TE('15-feb-2006','dd-mon-yyyy')),

FOR(TO_DA TE('15-apr-2006')) INTO P ARTITION sys_py;

What would be the outcome of this command?

A. It executes successfully , and the transition point is set to '1-apr-2006'.

B. It executes successfully , and the transition point is set to '15-apr-2006'.

C. It produces an error because the partitions specified for merging are not adjacent.

D. It produces an error because the date values specified in the merge do not match the date values stored in the table.

答案：C

（4）An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:
SQL>CREATE INDEX ord_custname_ix ON orders(custname);
The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause. You want to check the impact on the performance of the queries if the index is not available. You do not want the index to be dropped or rebuilt to perform this test.
Which is the most efficient method of performing this task?
A. disabling the index
B. making the index invisible
C. making the index unusable
D. using the MONITORING USAGE clause for the index

答案：B

（5）You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter.

The partition related to the current quarter is modified frequently and other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command in regular intervals:

SQL> EXECUTE

DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'GLOBAL');

You need statistics to be collected more quickly. What can you do to achieve this?

A.Set DYNAMIC_SAMPLING to level 4.

B.Set the STATISTICS_LEVEL parameter to BASIC.

C.Set the INCREMENTAL value to TRUE for the partition table.

D.Increase the value of STALE_PERCENT for the partition table.

答案：C

（6）You have applications that have frequently executed queries, and produce small and static result sets.

You configure the sqlnet.ora file in the client machine to set a nonzero value for the OCI_RESULT_CACHE_MAX_SIZE parameter.

What is the purpose of this configuration?

A.to avoid round trips to the server by enabling caching of query results in client memory

B.to improve performance by storing a copy of the data from the private SQL area of the PGA

C.to enhance the query performance by creating a cache in the client memory for sorting operations

D.to avoid the storing of query plans and results in the server by creating a cache in the client memory

答案：A

（7）View the Exhibit to examine the error obtained during the I/O calibration process. There are no data files on raw devices. What is the reason for this error?

此主题相关图片如下：

A.The DISK_ASYNCH_IO parameter is set to TRUE.

B.The FILESYSTEMIO_OPTIONS parameter is set to NONE.

C.Another session runs the I/O calibration process concurrently.

D.The pending area has not been created before running the I/O calibration process.

答案：B

（8）Evaluate the following function code:
CREA TE FUNCTION get_dept_avg(dept_id NUMBER) RETURN NUMBER RESUL T_CACHE
RELIES_ON (EMPLOYEES) IS avgsal NUMBER(6);
BEGIN
SELECT A VG(SALARY)INTO avgsal
FROM EMPLOYEES
WHERE DEP ARTMENT_I D = dept_id;
RETURN avgsal;
END get_dept_avg;
Which statement is true regarding the above function?
A. The cached result becomes invalid when any structural change is done to the EMPLOYEES table.
B. If the function execution results in an unhandled exception, the exception result is also stored in the cache.
C. Each time the function is invoked in a different session, the current result in the result cache gets overwritten.
D. If the function is invoked with a different parameter value, the existing result in the result cache gets overwritten by the latest value.

答案：A

（9）Which statement is true when Automatic Workload Repository (AWR) baselines are created using baseline templates?

A.AWR baselines are always created as repeating baselines.

B.AWR baselines can be created on the basis of two time values.

C.AWR baselines are always created with infinite expiration duration.

D.AWR baselines are always created using the Automatic Workload Repository (AWR) retention period as expiration duration.

答案：B

（10）View the Exhibit.

As shown in the diagram, in-memory statistics are transferred to the disk at regular intervals.

Which background process performs this activity?

此主题相关图片如下：

A.CKPT

B.SMON

C.MMON

D.DBWR

答案：C

（11）Which two changes and their effect on the system can be tested by using the Database Replay feature? (Choose two.)

A.multiplexing of the control file

B.adding the redo log member to the database

C.database and operating system upgrades

D.changing the database storage to ASM-managed storage

答案：CD

（12）Your company wants to upgrade the current production database to the RAC environment. To perform testing before migrating to the RAC environment, you performed the workload capture on the production database to record the peak workload. You set up the test RAC database and want to replay the recorded workload on the testmachine. Note the following steps that you may require to replay the database workload:

1) Preprocess the captured workload.

2) Restart the database in RESTRICTED mode.

3) Set up the Replay Clients.

4) Restore the test database to the point when the capture started.

5) Remap connections.

Arrange the steps required in the correct sequence to accomplish this task on the test machine.

A. 1, 4, 5, 3 (2 is not required.)

B. 1, 4, 3, 5 (2 is not required.)

C. 1, 2, 4, 5 (3 is not required.)

D. 2, 1, 5, 3, 4

E. 1, 2, 4, 5, 3

答案：A

（13）Which two prerequisites are needed for performing workload capture and replay? (Choose two.)

A. Close all sessions performing queries using database links.

B. running the database in shared server mode

C. The database on which the workload is replayed has to be a restore of the original database to a specific SCN.

D. setting up the directory to capture the workload

答案：CD

（14）In your database, the RESUL T_CACHE_MODE parameter has been set to MANUAL in the initialization parameter file. Y ou issued the following command:
SQL>SELECT /*+ RESUL T_CACHE */ sale_category , sum(sale_amt)
FROM sales GROUP BY sale_category;
Where would the result of this query be stored?
A. database buffer cache
B. shared pool
C. PGA
D. large pool

答案：B

（15）Which statements are true regarding the Query Result Cache? (Choose all that apply .)
A. It can be set at the system, session, or table level.
B. It is used only across statements in the same session.
C. It can store the results from normal as well as flashback queries.
D. It can store the results of queries based on normal, temporary , and dictionary tables.

答案：AC

（16）You need to create a partitioned table to store historical data and you issued the following command:

CREATE TABLE purchase_interval

PARTITION BY RANGE(time_id) INTERVAL(NUMTOYMINTERVAL(1, 'month'))

STORE IN(tbs1, tbs2, tbs3)

(

PARTITION p1 VALUES LESS THAN(TO_DATE('1-1-2005','dd-mm-yyyy')),

PARTITION p2 VALUES LESS THAN(TO_DATE('1-1-2007','dd-mm-yyyy'))

)

AS SELECT * FROM purchases WHERE time_id < TO_DATE('1-1-2007','dd-mm-yyyy');

What is the outcome of the above command?
A. It returns an error because the range partitions P1 and P2 should be of the same range.
B. It creates two range partitions (P1, P2). Within each range partition, it creates monthwise subpartitions.
C. It creates two range partitions of varying range. For data beyond '1-1-2007,' it creates partitions with a width of one month each.
D. It returns an error because the number of tablespaces (TBS1,TBS2,TBS3)specified does not match the number of range partitions (P1,P2) specified.

答案：C

（17）Evaluate the following statements:
CREATE TABLE purchase_orders (po_id NUMBER(4),po_date TIMEST AMP , supplier_id NUMBER(6), po_total NUMBER(8,2), CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date) (PARTITION Q1 VALUES LESS THAN (TO_DA TE(‘1-apr-2007‘d-mon-yyyy’), PARTITION Q2 V ALUES LESS THAN (TO_DATE(‘1-jul-2007’d-mon-yyyy‘), PARTITION Q3 VALUES LESS THAN (TO_DATE(’1-oct - 2007’d-mon-yyyy’), PARTITION Q4 V ALUES LESS THAN (TO_DATE(‘1-jan-2008’d-mon-yyyy‘));

CREATE TABLE purchase_order_items (po_id NUMBER(4) NOT NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)) P ARTITION BY REFERENCE(po_items_fk);
What are the two consequences of the above statements? (Choose two.)
A. Partitions of PURCHASE_ORDER_ITEMS have system-generated names.
B. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each.
C. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of the PURCHASE_ORDERS table.
D. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically duplicating the key columns.
E. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically reflected in the PURCHASE_ORDERS table.

答案：BC

（18）Which statements are true regarding system-partitioned tables? (Choose all that apply .)
A. Only a single partitioning key column can be specified.
B. All DML statements must use partition-extended syntax.
C. The same physical attributes must be specified for each partition.
D. Unique local indexes cannot be created on a system-partitioned table.
E. Traditional partition pruning and partitionwise joins are not supported on these tables.

答案：DE

（18）Which client requests to the database can be captured as a part of the workload capture?

(Choose all that apply.)

A. flashback query

B. distributed transactions

C. logging in and logging out of sessions

D. all DDL statements having bind variables

E. direct path load of data from external files

答案：CD

（19）In which cases is reference partitioning effective in enhancing performance?
A. It is effective only in partition pruning.
B. It is effective only in partitionwise joins provided that the query predicates are different from the partitioning key .
C. It is effective in both partition pruning as well as partitionwise joins provided that the query predicates are identical to the partitioning key .
D. It is effective in both partition pruning as well as partitionwise joins irrespective of whether the query predicates are different from or identical to the partitioning key .

答案：D

（20）View the Exhibit to examine the parameters set for your database instance. You execute the following command to perform I/O calibration after the declaration of bind variables in the session that are used in the command:

SQL> EXECUTE dbms_resource_manager.calibrate_io( num_physical_disks=>1, -

max_latency=>50, -max_iops=>:max_iops, -max_mbps=>:max_mbps, -

actual_latency=>:actual_latency);

此主题相关图片如下：

Which statement describes the consequence?

A. The command produces an error.

B. The calibration process runs successfully and populates all the bind variables.

C. The calibration process runs successfully but the latency time is not computed.

D. The calibration process runs successfully but only the latency time is computed.

答案：A

（21）View the Exhibit to examine the replay settings for replay parameters. What is the implication for setting the values for replay parameters? (Choose all that apply.)

此主题相关图片如下：

A. The COMMIT order in the captured workload is preserved during replay.

B. The value 100 in the THINK_TIME_SCALE parameter attempts to make the replay client shorten the think time between calls.

C. The value 100 in the CONNECT_TIME_SCALE parameter attempts to connect all sessions as captured.

D. The value 100 in the THINK_TIME_SCALE parameter attempts to match the captured user think time while replaying.

E. The value 100 in the CONNECT_TIME_SCALE parameter attempts to connect all sessions immediately as soon as the replay begins.

答案：ACD

（22）Evaluate the following SQL statement used to create the PRODUCTS table:
CREA TE T ABLE products (product_id NUMBER(3) PRIMARY KEY , product_desc V ARCHAR2(25), qty NUMBER(8,2), rate NUMBER(10,2), total_value AS ( qty * rate)) PARTITION BY RANGE (total_value) (PARTITION p1 V ALUES LESS THAN (100000), P ARTITION p2 V ALUES LESS THAN(150000), PARTITION p3 V ALUES LESS THAN (MAXV ALUE)) COMPRESS FOR ALL OPERA TIONS;
Which statement is true regarding this command?
A. It executes successfully but partition pruning cannot happen for this partition key .
B. It produces an error because the TOT AL_VALUE column cannot be used as a partition key .
C. It produces an error because compression cannot be used for the TOT AL_V ALUE partition key .
D. It executes successfully but the values in the TOT AL_V ALUE column would not be physically stored in the partitions.

答案：D

（23）In what view are you likely to see the following output?

SID SERIAL# EVENT SECONDS_IN_WAIT

121 269 RMAN backup & recovery I/O 2

129 415 SQL*Net message from client 63 130 270 SQL*Net message from client

A. V$SESSION_EVENT

B. V$SESSION

C. V$WAITS

D. V$WAITSTAT

E. V$SYSSTAT

答案：B

（24）What view might you use to try to determine how long a particular backup will take?

A. V$SESSION_EVENT

B. V$SESSION

C. V$WAITS

D. V$WAITSTAT

E. V$SESSION_LONGOPS

答案：E

（25）Which of the following represents the correct sequence of events for Database Replay?

A. Capture, analyze, preprocess, replay

B. Capture, preprocess, analyze, replay

C. Capture, preprocess, replay, analyze

D. Analyze, capture, preprocess, replay

E. None of the above

答案：C

（26）Which of these recommendations should be followed before capturing a workload? (Choose all that apply.)

A. Make sure your replay database has the same structure as the capture database, except without data.

B. Make sure the replay and capture databases are similar in data content.

C. Perform a clean shutdown and restart of the capture database before beginning a workload capture.

D. Start the capture database in UNRESTRICTED mode, then start the capture.

E. Define inclusion and exclusion filters.

答案：BC

（27）Which is true concerning Database Replay in an Oracle Real Application Cluster (RAC) database?

A. Workload capture is per instance.

B. You only need to restart one instance to begin workload capture.

C. Specifically in RAC, you shut down all instances, restart them individually, and begin workload capture with the last instance started.

D. RAC does not support workload capture, but it does support workload replay.

E. None of the above.

答案：E

（28）Performance divergence indicated in the Workload Replay report is most likely due to what?

A. DML and SQL statement results that do not match between the capture and replay systems

B. When errors that occur in the capture system dont occur in the replay system

C. Top SQL statements

D. Infrastructure or system-configuration differences

E. Time-of-day differences between capture and replay systems

答案：D

（29）Which two statements about workload capture and replay operations are true? (Choose two.)

A. The clients must be created manually on the test machines to perform more realistic testing.

B. Restart the database in RESTRICTED mode before beginning workload replay to enable a clean state for workload replay.

C. Restart the database in RESTRICTED mode before beginning workload capture to enable a clean state for capturing the workload.

D. The application state of the capture system must be identical to the application state of the replay system when the workload replay begins.

答案：CD

（30）Which three statements about performance analysis by SQL Performance Analyzer are true?

(Choose three.)

A. It detects changes in SQL execution plans.

B. It produces results that can be used to create the SQL plan baseline.

C. The importance of SQL statements is based on the size of the objects accessed.

D. It generates recommendations to run SQ L Tuning Advisor to tune regressed SQLs.

E. It shows only the overall impact on workload and not the net SQL impact on workload.

答案：ABD

（31）In a Database Replay workload capture, what client request information is gathered? (Choose all that apply.)

A. SQL text

B. Shared server requests (Oracle MTS)

C. Bind variable values

D. Information about transactions

E. Remote DESCRIBE and COMMIT operations

答案：ACD

（32）Which two client requests are captured during database replay Capture? (Choose two)

A. Flashback queries

B. Shared server requests

C. Login and logoff activities of sessions

D. Direct path load of data from external files by using utilities such as SQL *loader

E. Data definition language (DDL) and data manipulation language (DML) operations

答案：CE

（33）Your company wants to upgrade the production database to a Real Application Clusters (RAC) environment. You set up the best RAC database and want to replay a recorded workload captured from the production database on the test machine.

The following steps may be used to replay the database workload:

1) Preprocess the captured workload

2) Restart the database in RESTRICTED mode.

3) Set up Replay Clients.

4) Restore the test database to the point when the capture started.

5) Remap connections.

Which is the correct sequence of the required steps?

A.1, 4, 3, 5

B.1, 4, 5, 3

C.2, 1, 5, 3, 4

D.1, 2, 4, 5, 3

答案：B