第 10 章：监控和调整数据库

---

 

第 10 章：监控和调整数据库

返回课程列表

目的

本章向您介绍通过企业管理器执行的一些监控和调整操作。

主题

本章讨论了以下内容：

主动监控数据库 诊断和解决性能问题 使用 SQL Tuning Advisor 使用 SQL Access Advisor 使用 Memory Advisor

查看屏幕截图

将鼠标移到这个图标上，显示所有的屏幕截图。您还可以将鼠标移到各个图标上，以只查看与该图标相关的屏幕截图。

主动监控数据库

返回主题列表

警报帮助您主动地监控数据库。大多数警报是在超过特定的量度阈值时发出的通告。您可以为每一个警报设置临界和警告阈值。这些阈值即为边界值，如果超过了这些边界值，则指示系统处于非预期的状态。

在这一部分中，您将执行以下任务：

创建具有指定阈值的表空间和表 触发表空间的空间使用率警报 设置量度阈值 设置通告 创建具有指定阈值的表空间和表

返回主题列表

首先创建包含有一个 20 MB 的数据文件的一个新的表空间。将本地管理这个表空间，并使用自动段空间管理 (ASSM)。然后您在这个新的表空间中创建一个新的表。这个表将把 Enable Row Movement 选项设为 yes，以允许表中的空间回收。执行以下操作：

Off _1. 单击数据库主页上的Administration 链接。

1. Off _2. 单击Tablespaces 链接。
2. Off _3. 单击Create 按钮。
3. Off _4. 输入TBSALERT 作为表空间名称，然后单击Add，为表空间定义一个数据文件。
4. Off _5. 输入tbsalert.dbf 作为数据文件名，并输入20 MB 作为文件大小。单击Continue。
5. Off _6. 单击Thresholds，指定空间使用率警告和临界阈值水平。

单击Specify Thresholds, by percent used。设置数据库范围的默认空间使用率阈值。为 Warning% 输入60，

1. Off ^7. 为 Critical% 输入68。单击OK。
2. Off _8. 单击Database 导览栏，返回Administration 属性页面。
3. Off _9. 单击Tables 链接。
4. Off _10. 单击Create。
5. Off _11. 单击Continue。
6. _12. 在 Name 域中输入employees1。指定SYSTEM 作为模式，TBSALERT 作为表空间。单击 Define Using 下拉列表，选择SQL。在 Create Table As 域中输入select * from hr.employees，然后单击Options。Off
7. Off _13. 为 Enable Row Movement 选择Yes，然后单击OK，完成表的创建。
8. Off _14. 表已创建完成。单击Database 导览栏。

触发表空间空间使用率警报

返回主题列表 您现在将更新表，触发空间利用率警报。执行以下操作：

Off _1. 打开SQL*Plus 会话，然后执行以下命令：

sqlplus system/oracle

Off _2. 将以下 SQL 命令拷贝并粘贴到 SQL*Plus 会话中，以模拟EMPLOYEES1 表上的用户活动：

begin

for i in 1..1000 loop
insert into employees1
select * from hr.employees;

commit;
end loop;
end;
/

_3. 转至Enterprise Manager 窗口。单击Database 导览栏，返回到Administration 页面，然后单击Tablespaces 链接。Off

Off _4. 注意，TBSALERT 表空间使用百分率增加了。

_5. 回切到SQL*Plus 窗口，并将以下命令拷贝并粘贴到 SQL*Plus 会话中，，以模拟EMPLOYEES1 表上的更多用户活动：Off

delete employees1 where department_id = 50;

begin

for i in 1..500 loop
insert into employees1
select * from hr.employees;

commit;
end loop;
end;
/

转至Enterprise Manager 窗口。刷新浏览器（对于 Linux Mozilla，从菜单栏中选择View，然后选择

1. Off ^6. Reload）。注意，TBSALERT 表空间的空间使用百分率增加了。
2. _7. 回切到SQL*Plus 窗口，并将以下命令拷贝并粘贴到 SQL*Plus 会话中，，以模拟EMPLOYEES1 表上的更多用户活动：Off

begin

for i in 1..500 loop
insert into employees1
select * from hr.employees;

commit;
end loop;
end;
/

Off _8. 将以下 SQL 命令拷贝并粘贴到 SQL*Plus 会话中，以模拟EMPLOYEES1 表上的用户活动：

delete employees1 where department_id = 30;
commit;

delete employees1 where department_id = 100;
commit;

delete employees1 where department_id = 50;
commit;

delete employees1 where department_id = 80;
commit;

exit

转至Enterprise Manager 窗口。刷新浏览器（对于 Linux Mozilla，从菜单栏中选择View，然后选择

Off ^9. Reload）。注意，TBSALERT 表空间的空间使用率现在已经超过了临界阈值水平 (68%)。

在您等待空间使用率警报显示在 Enterprise Manager 主页上时，请仔细查看表段统计数据。单击Database 导览

1. Off ^10. 栏，然后单击Tables 链接。
2. Off _{11. Go} 要查_。找SYSTEM.EMPLOYEES1 表，请在Schema 域中输入system，在Object Name 域中输入emp。单击
3. Off _12. 单击EMPLOYEES1 链接。
4. Off _13. 单击Segments。
5. _14. 注意EMPLOYEES1 表中浪费空间的百分率。您可能能够通过回收表中未使用的空间来解决表空间空间使用率警报。Off

在同一页面上，您可以为Space Usage Trend 指定一个日期范围，然后单击Refresh 按钮，以规划EMPLOYEES1 表未来的空间使用率。因为在EMPLOYEES1 表上没有足够的活动历史纪录，所以您在空间使用率分析图中将看不到非常有意义的数据。单击Database 导览栏，然后单击Home 页面标签。

单击Refresh 按钮几次，直到您看到一个红色的x 和数字1 紧邻着Problem Tablespaces 出现。向下滚动至

1. Off ^15. Alerts 表。
2. Off _16. 您将看到一个Tablespaces Full 警报。单击Tablespace [TBSALERT] is [70 percent] full 链接。

设置量度阈值

返回主题列表 Oracle 提供了一组预先定义的量度，其中一些量度一开始就拥有为它们定义的阈值。您将预先为TBSALERT 表空间的表空间使用率定义一个量度。要查看所有的量度，请执行以下操作：

1. Off _1. 单击Related Links 区域中的Manage Metrics。
2. Off _2. 单击Edit Thresholds。
3. Off _3. 向下滚动至Tablespace Space Used (%)，然后选择这个量度。向上滚回到窗口顶部。
4. Off _4. 单击Specify Multiple Thresholds。
5. Off _5. 对于TBSALERT 表空间,将警告阈值修改为70，将临界阈值修改为80。然后单击OK。
6. Off _6. 修改完成。单击OK，将数据保存到数据库中。
7. Off _7. 更新成功。单击路径式导航栏中的Database 导航项。

设置通告

返回主题列表 当出现需要您干预的事件时，您可以选择性地提供通告。默认地，临界状态的警报（如 Database Down、Generic Alert Log Error Stats 和 Tablespace Used）都设置为提供通告。执行以下操作：

1. Off _1. 单击 Database 主页顶部的Setup。
2. Off _2. 单击Notification Methods。

在 Outgoing Mailserver 域中输入<您的邮件服务器>，在 Identify Sender As 域中输入dbaalert，在 Sender's

1. Off ^3. Email 域中输入notify01@oracle.com，然后单击Apply。
2. Off _4. 更新成功。单击页面顶部的Preferences。
3. Off _5. 单击 General 选项中 E-mail Addresses 的Add Another Row。
4. Off _6. 输入notify01@oracle.com 作为电子邮件地址，单击Apply。然后单击Database。

诊断和解决性能问题

返回主题列表

在出现数据库性能问题时，需要您进行诊断和纠正。有时用户抱怨性能缓慢会使您注意到问题。其余的时候，您可能注意到主页上的 Host CPU 图中的性能尖峰。在所有情况下，自动数据库诊断监控器 (ADDM) 都将标记这些问题，它默认每 60 分钟执行一次自上而下的系统分析，并在

Oracle Enterprise Manager 主页上报告它发现的问题。 ADDM 每 60 分钟自动运行一次，和自动工作负载信息库 (AWR) 采集的快照同步发生。它的输出包含了它确认的每个问题的说明和建议操作。

生成性能问题查找结果

利用 ADDM 解决发现的性能问题 生成性能问题查找结果

返回主题列表 为了演示 ADDM 如何工作，您要生成一个性能问题查找结果。在本文中，您将创建一个等待行锁的会话。要执行某些如更新和删除的操作，会话必须锁住该行。执行以下步骤，生成性能问题查找结果：Off _1. 打开终端窗口，执行以下命令：

sqlplus hr/hr
create table emp as select * from employees;
delete emp;

Off _2. 再打开一个终端窗口，并执行以下命令，创建一个行锁冲突：

sqlplus hr/hr
delete emp;

1. Off _3. 单击 Enterprise Manager 窗口中的Performance。
2. Off _4. 您将看到会话等待值非常高。等待大约 10 分钟，然后向下滚动至窗口底部。
3. Off _5. 您现在将创建快照来捕获性能问题。单击Snapshots。
4. Off _6. 单击Create，创建快照。
5. Off _7. 单击Yes，创建人工快照。
6. Off _8. 正在创建快照。
7. Off _9. 一旦快照创建完成，请单击路径式导航栏中的database 导航项，然后单击home。
8. Off _10. 现在通过主页的 Alert 部分的一个警报，检测到了一个性能问题。

利用 ADDM 解决发现的性能问题

返回主题列表 当遇到性能问题时，您可以使用 ADDM 来解决它。执行以下操作：

1. Off _1. 单击警报Database Time Spent Waiting。
2. Off _2. 注意建议的操作是运行 ADDM 来获取更多的关于系统的性能分析。单击Additional Advice。

确保从列表中选择了您创建的快照。注意SQL statements were found waiting for row lock waits 将影响系统

1. Off ^3. 超过 86% 的数据库时间。单击列表中的这个性能问题查找结果。
2. Off _4. 您看到了解决该性能问题需要采取的操作。单击View Rationale。
3. Off _5. 您将看到引起问题的特定 SQL ID。单击Close。
4. Off _6. 要解决性能问题，请单击路径式导航栏中的Database 导航项。
5. Off _7. 单击Performance。
6. Off _8. 向下滚动，然后选择 Additional Monitoring Links 下的Blocking Sessions。
7. Off _9. 确保选择了最高级别的 HR，然后单击Kill Session。
8. Off _10. 单击Yes，终止会话。
9. Off _11. 会话终止。单击路径式导航栏中的Database 导航项，然后单击Home。
10. Off _12. 注意警报已消失。

使用 SQL Tuning Advisor

返回主题列表 数据库管理员或操作员执行以下任务来响应性地监控并诊断数据库性能问题： 查看数据库等待 检查 Database Wait 类的 TOP SQL 利用 SQL 调整顾问工具调整 SQL 语句 查看 SQL 语句的 SQL 执行细节 查看数据库等待 返回列表 您将首先启动几个工作负载会话。然后您将查看数据库工作负载。创建一个名称为$HOME/wkdir 的目录。下载 perflab. tar 文件，并将文件解压缩至$HOME/wkdir 目录中。执行以下步骤：

Off _1. 打开一个命令行窗口，然后运行以下 OS 脚本：

./setup_perflab.sh

注意：这个脚本运行将花费大约 4 分钟。

Off _2. 回切到 Enterprise Manager 窗口。选择Administration 链接。

Off _3. 在标题为Workload 的部分中，单击Automatic Workload Repository 链接。

确定已经为该数据库采集了多少快照。查看Snapshots 下的数量和最后一次 ADDM 快照采集的时间。应该有至

1. Off ^4. 少三个快照。单击路径式导航栏中的Database 导航项。
2. Off _5. 单击Performance 链接。

在 Performance 窗口中查看Sessions: Waiting and Working 图。显示一个表示数据库当前工作负载的图表。

1. Off ^6. （向图表填充数据可能要花一分钟）用 ADDM 快照采集的数据来填充这个图表。图表的边上是图例。每一个图例项目具有不同的颜色。您可以从图中快速确定占用最多时间的项目是黄色的，即用户 I/O。单击User I/O 链接。
2. _7. 在 Active Sessions Waiting: User I/O 图表下有两个饼图。查看左边的饼图Top Waiting SQL。这显示占绝大多数的等待 (53%) 是由一条 SQL 语句造成的。查看右边的饼图Top Waiting Sessions。这个饼图显示当前最活跃的几个会话等待的时间几乎占相同的百分比。Off

检查 Database Wait 类的 TOP SQL

返回列表 如之前的任务所示，有一条 SQL 语句导致了大部分的数据库等待。在这个任务中，您将深入了解根本原因。执行以下操作：

Off _1. 从 Active Sessions Waiting: User I/O 页面中，单击页面中部的Top SQL。

在出现的详细页面上，查看Wait Events for Top SQL 表，该表按活动（%）排序。您可以看到 Top SQL 语句

1. Off ^2. 将其大部分时间花费在了 'db file scattered read' 活动上。单击具有最高活动百分比的 SQL 语句的 SQL ID。
2. Off _3. 显示这条 SQL 语句的执行计划。单击Current Statistics 标签。
3. Off _4. 显示这条 SQL 语句的统计数据。单击Execution History 标签。

显示这条 SQL 语句的统计分析图表。 CPU 和 Elapsed Time 图表显示在一段时间上执行这条 SQL 语句的 CPU

Off ^5. 使用率。选择Seconds Per Execution，单击Go，显示这条 SQL 语句的每次执行使用的时间和资源。

从显示的图表中，可以确定 CPU 资源使用率在增加，执行这条 SQL 语句花费的时间也在增加。单击 Tuning

Off ^6. History 标签。

显示之前为这条 SQL 语句提供的调整建议。当前没有建议。您现在准备好了使用 SQL Tuning Advisor 来调整

Off ^7. SQL 语句。

利用 SQL 调整顾问工具调整 SQL 语句

返回列表 正如在上一个关于响应式调整的部分中确定的那样，目标 SQL 语句需要调整。 SQL 调整顾问工具将为您调整执行计划。执行以下操作：

1. Off _1. 单击Run SQL Tuning Advisor。
2. Off _2. 在 Schedule Advisor 窗口中，确保选择了 Scope 下的Comprehensive，作业将立即安排。单击OK。
3. Off _3. SQL Tuning Advisor 将创建一个任务，以分析 SQL 语句，任务一旦完成，将显示一组调整建议。
4. Off _4. 单击View Recommendations。
5. Off _5. 单击New Explain Plan 按钮，查看建议的修改。
6. Off _6. 正如您所看到的，新的解释计划删除了全部的表扫描。单击浏览器后退按钮，返回上一页。
7. Off _7. 单击Implement 按钮，执行调整建议。
8. Off _8. 出现一条确认页面，指示 SQL Profile 成功创建。单击路径式导航栏中的Database 导航项。

查看 SQL 语句的 SQL 执行细节

返回列表 您已执行了调整建议，现在查看 SQL 语句和其执行细节。执行以下步骤。

1. Off _1. 执行Performance。
2. Off _2. 向下滚动至Sessions: Waiting and Working 图表。等待一分钟，观察用户 I/O 如何减少。

使用 SQL Access Advisor

返回主题列表

SQL Access Advisor 提供了许多过程，可以调用这些过程来帮助确定创建和丢弃哪些实体化化视图和索引。它利用基于您的模式的，假定的工作负载，或从实际的工作负载（可能由用户提供、来自 Oracle Trace 或来自 SQL 高速缓存的内容）中作出决策。

还可以根据不同的标准对工作负载进行过滤，如只使用包含这些表的查询，或在这个范围中有优先权的查询。

准备环境 利用 SQL 高速缓存获取建议 查看和执行建议 准备环境

返回列表

执行以下步骤，为使用 SQL Access Advisor 准备好环境。当 Advisor 在运行时，实体化视图和索引可能存在，但为了举例的目的，把它们删除了，以便您可以看到 Advisor 的建议。您还需要设置高速缓存，以便 SQL Access Advisor 能够生成建议。执行以下操作：

Off _1. 打开一个终端窗口，然后执行以下命令，清理环境：

sqlplus system/
DROP MATERIALIZED VIEW all_cust_sales_mv;
DROP MATERIALIZED VIEW costs_mv;
DROP MATERIALIZED VIEW costs_pm_mv;
DROP MATERIALIZED VIEW cust_sales_mv;
DROP MATERIALIZED VIEW some_cust_sales_mv;
DROP MATERIALIZED VIEW cust_id_sales_aggr;
DROP MATERIALIZED VIEW sales_cube_mv;
DROP MATERIALIZED VIEW sales_gby_mv;
DROP MATERIALIZED VIEW CUST_TOTAL_SALES_MV;
DROP MATERIALIZED VIEW CUST_SALES_TIME_MV;

Off _2. 现在您需要创建高速缓存。执行下列命令：

alter system flush shared_pool;

grant advisor to sh;

connect sh/sh;

SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,

sum(s.quantity_sold) as quantity

FROM sales s , customers c, products p

WHERE c.cust_id = s.cust_id

AND s.prod_id = p.prod_id

AND c.cust_state_province IN ('Dublin','Galway')

GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales

FROM sales s, customers c WHERE s.cust_id= c.cust_id GROUP BY c.cust_id;

select sum(unit_cost) from costs group by prod_id;

利用 SQL 高速缓存获取建议

返回列表

您将使用您刚刚设置的 SQL 高速缓存来获取来自 SQL Access Advisor 的建议。执行以下操作：

Off _1. 打开浏览器，输入以下 URL：

http://:5500/em

输入sys/ 作为SYSDBA，单击Login。

1. Off _2. 向下滚动至主页底部，然后单击 Related Links 下的Advisor Central。
2. Off _3. 单击SQL Access Advisor 链接。
3. Off _4. 确保选中了Current and Recent SQL Activity，然后单击Show Advanced Options。
4. Off _5. 向下滚动。在 Filter Options 下，选择Filter workload based on these options。选择Only the top resource consuming SQL statements 和 Only SQL statements executed by the following users。然后在 Users 域中输入SH，然后单击Next。
5. Off _6. 选择Both Indexes and Materialized Views，然后单击Next。
6. Off _7. 输入任务名OBE，为 Schedule Type 选择Standard，然后单击Next。
7. Off _8. 在概要窗口中，单击Submit。

查看和执行建议

返回列表 现在您可以查看结果，如果您希望,可以执行它们。执行以下操作：

1. Off _1. 确保选择了您的作业，然后单击 View Result。
2. Off _2. 单击 Recommendation ID 1，查看建议的细节。

在这里您可以自定义对象名称、模式和表空间，以执行建议。向下滚动，并将 Create Materialized View 的

1. Off ^3. Schema Name 修改为SH，然后单击OK。
2. Off _4. 当您安排执行时，如果要查看将执行的 SQL 脚本，可以单击Show SQL。
3. Off _5. 向下滚动至底部，您将看到创建实体化视图的语句和您刚作的修改。单击OK。
4. Off _6. 单击Schedule Implementation，执行建议。
5. Off _7. 为 Job Name 输入OBEIMPL，然后单击Submit。
6. Off _8. 执行作业创建完成并且现在正在运行。单击Run History 标签。
7. Off _9. 确保选择了您的作业，然后单击 View。
8. Off _10. 查看概要，然后单击路径式导航栏中的 database 导航项。
9. Off _11. 单击Materialized View。
10. Off _12. 在 Schema 域中输入SH，然后单击Go。
11. Off _13. 注意新创建的实体化视图出现在列表中。单击路径式导航栏中的Database 导航项，然后单击Tables 链接。

使用 Memory Advisor

返回主题列表

在这一部分中，您将主动管理并将一些与 Oracle 例程内存配置相关的任务自动化。通过将内存配置自动化，您有了更多的时间来处理影响您的企业的应用或商务实际问题。Memory Advisor 是 Oracle 数据库内部的一个智能专家系统，它为各种 SGA 和 PGA 组件主动确定最优设置。当实现自动化

时，Oracle 将根据工作负载的需求自动调整各种池和高速缓存的设置。 启用自动共享内容管理 修改总 SGA 大小 使用 PGA Advisor 启用自动共享内存管理 返回列表 执行以下操作，启用 SGA 的自动共享内存管理：

Off _2. 向下滚动至主页底部，然后单击 Related Links 下的Advisor Central。

1. Off _3. 选择Memory Advisor。
2. Off _4. 为 Automatic Shared Memory Management 单击Enable。
3. Off _5. 单击OK，启用自动共享内存管理。
4. Off _6. Oracle 服务器现在将根据工作负载的需求自动调整各种池和高速缓存的设置。

修改总 SGA 大小

返回列表 当处于自动共享内存管理模式下时，要修改总的 SGA 大小，您将需要确保最大的 SGA 大小足够大。执行以下操作：

1. _1. 向下滚动页面。将 Maximum SGA Size 修改为261 MB，然后单击Apply。注意：如果您接收到一个错误，那么单击 Refresh 按钮重试。Off
2. Off _2. 单击Yes，确认修改。
3. Off _3. 提供主机证书和数据库证书。单击OK。
4. Off _4. 当您修改 Max SGA Size 参数时，例程必须重新启动。单击Yes，确认数据库重启。
5. Off _5. 数据库重启过程将开始。单击Refresh。
6. Off _6. 输入sys/ 作为SYSDBA，单击Login。
7. Off _7. 向下滚动，然后单击Advisor Central。
8. Off _8. 单击Memory Advisor。
9. Off _9. 将 Total SGA Size 参数修改为260 MB。单击Apply。

注意 Max SGA Size 参数自动调整与内存细粒大小保持一致，虽然您原来已把它设为 261 MB，但 Oracle 服务器自动将其修改为 264 MB。

Off _10. 一旦您接收到了确认通告 — 指示参数已成功修改，您还将注意到一些 SGA 组件的内存分配已自动调整。

使用 PGA Advisor

返回列表 执行以下操作，分配与 PGA 相关的内存：

1. Off _1. 单击PGA。
2. Off _2. 单击Advice……
3. Off _3. PGA Aggregate Target Advice 图显示在高速缓存中找到数据（以便您不需访问磁盘）的频率。在本文中，应当注意当前的 PGA Aggregate Size 被设为 24 MB 左右，所有请求的服务的 88% 以上都从内存中获得。这还显示溢出范围从 12 MB 左右开始。在 12 MB 时，PGA 请求应用高速缓存的概率大约为 90%。 PGA Aggregate Size 指示（根据当前的工作负载和数据库中的会话数量），在该数据库中为所有 PGA 分配的大小不超过 24 MB。单击OK。
4. Off _4. 单击PGA Memory Usage Details。
5. Off _5. 本图显示内存大小请求的使用率详情和各种 PGA 内存请求的执行百分率。单击OK。

将鼠标移到这个图标上，以隐藏所有的屏幕截图