挂起但未瘫痪：附连内存的 SGA 查询

　　假设您使用 Oracle 企业管理器诊断和解决性能问题。一天，出现了一个棘手的问题：一个设计糟糕的应用程序引起了严重的库高速缓存锁定问题，并且数据库呈现挂起状态。您必须快速确定导致该问题发生的会话，并快速终止这些会话。

　　您可以启动 Oracle 企业管理器来诊断此问题。且慢！如果整个数据库充满了挂起的会话，那么 Oracle 企业管理器中的查询是否也会挂起？

　　如果使用 Oracle 数据库 10g 第 2 版，则答案是否定的。正如我在第 2 部分中所介绍的，在该版本中，“在内存访问模式下进行监控”选项使企业管理器可以直接从 SGA 内存（而不是 V$SESSION）中选择会话。因为在该模式下绕过了 SQL 层，挂起的数据库就不会阻碍此查询的执行。相反，将自动启动查询。

　　让我们看看该特性是如何工作的。在 Enterprise Manager 屏幕上，选择 Performance 选项卡并滚动到该页面底部的“Additional Monitoring Links”部分，屏幕与下图类似。



　　注意红色椭圆框中“Hang Analysis”的超链接。单击该链接，将显示一个与下图类似的屏幕。



　　该图展示了各种“死”会话。在该示例中，您可以看到 SID 为 193 的会话（根会话）已经阻止了其他两个会话，即 192 和 214。该图中的会话颜色表示会话受阻的时间。可以单击 SID 访问 Session Details 屏幕，以获取更多信息。

　　还记得 ORADEBUG 实用程序吗？Oracle 企业管理器使用该实用程序获得有关系统挂起的数据。启用 SGA 直接连接时，Oracle 将对每个实例使用一个 SQL 收集器。该收集器随企业管理器自动启动。检索到以下视图中的数据：

V$SESSION 
V$SESSION_WAIT 
V$SYSTEM_EVENT 
V$SYSSTAT 

　　附连内存的 SGA 查询是一个非常强大的特性，以后肯定能帮您解决棘手问题。我们都对可“征服”数据库的应用程序有一种亲切感，为什么呢？现在，您可以给出答案了。我认为此特性是第 2 版一个最适用于数据库管理员的特性。

　　可中断的 SQL Access Advisor

　　您可能熟悉 Oracle 数据库 10g 中的 SQL Access Advisor。它主要提供了一个自动化的方法，通过确定可提高 SQL 性能的索引和物化视图来调整 SQL 工作负载。

　　但请考虑下面这种情况：您遇到了一些性能问题，并希望对一组 SQL 语句运行 SQL Access Advisor。为获得一个更准确的分析，您选择了“综合模式”选项。随后，您将等待结果。

　　如果 SQL 工作负载很大（包含上百条语句）并且 SQL 语句比较复杂，您可能等待较长的时间。但与些同时却用户迫切要求您提供答案。您该怎么办？

　　在 Oracle 数据库 10g 第 2 版中，您可以轻松地中断此顾问程序并查看到目前为止生成的建议或查找结果。第 1 版中的 SQL Tuning Advisor 中提供这些功能，现在已经扩展到 SQL Access Advisor。

　　让我们看看该功能的工作方式。从 Advisor Central 屏幕中，单击 SQL Access Advisor 链接。



　　从标题“Actions”旁边的右侧下拉列表中选择“Interrupt”选项，然后按 Go 按钮。该命令将中断 SQL Access Advisor，您可以立即看到建议。当然，这些建议并不是完整的集合，但在大多数情况下可以满足用户的需要。

　　如果您使用的是命令行版本的 SQL Access Advisor 而不是 Oracle 企业管理器，那么您是否仍可以查看工作进度？当然，您可以使用新的视图 V$ADVISOR_PROGRESS。

SQL> desc v$advisor_progress 
Name                              Null?类型 
 ----------------------------------------- -------- ----------- 
SID                                                NUMBER 
SERIAL#                                            NUMBER 
USERNAME                                           VARCHAR2(30) 
OPNAME                                             VARCHAR2(64) 
ADVISOR_NAME                                       VARCHAR2(64) 
TASK_ID                                            NUMBER 
TARGET_DESC                                        VARCHAR2(32) 
SOFAR                                              NUMBER 
TOTALWORK                                          NUMBER 
UNITS                                              VARCHAR2(32) 
BENEFIT_SOFAR                                      NUMBER 
BENEFIT_MAX                                        NUMBER 
FINDINGS                                           NUMBER 
RECOMMENDATIONS                                    NUMBER 
TIME_REMAINING                                     NUMBER 
START_TIME                                         DATE 
LAST_UPDATE_TIME                                   DATE 
ELAPSED_SECONDS                                    NUMBER 
ADVISOR_METRIC1                                    NUMBER 
METRIC1_DESC                                       VARCHAR2(64) 

　　此处的列 TOTALWORK 和 SOFAR 显示了已经完成的工作量以及总工作量，这与您从 V$SESSION_LONGOPS 视图中看到的内容相似。 检查是否启用了跟踪

　　如果会话执行的任务和预期不符，或者执行速度比较慢，那么大多数数据库管理员的第一步是检查等待事件。要构建配置文件，您可能还需要长期跟踪会话，那么在 user_dump_dest 目录中将生成一个跟踪文件。

　　现在，假设您在某段时间内对多个会话使用了端到端跟踪，但现在不知道哪些会话处于跟踪状态。如何找出这些对话呢？

　　方法之一是对大量跟踪文件进行筛选，以提取 SID 和 Serial# 列并在数据库的 V$SESSION 视图中进行匹配。毋庸质疑，这个过程比较复杂、困难并且容易出错。Oracle 数据库 10g 第 2 版中提供了一个更优秀、更简单的方法：您所要做的只是查看一个视图，即 V$SESSION。

　　新增了三个新列显示跟踪状态：

• sql_trace - 如果在会话中启用了 SQL 跟踪，则显示 TRUE/FALSE
• sql_trace_waits - 如果启用了会话跟踪，则可以让跟踪程序将等待信息写入跟踪文件，这对于诊断性能问题很有用。
• sql_trace_binds - 如果会话使用绑定变量，则可以让跟踪程序将绑定变量值写入跟踪文件。该列显示 TRUE/FALSE。

　　当未开启会话跟踪时，如果选择这些列：

select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds 
from v$session  
where username = 'HR' 

　　输出结果如下：

SID    SERIAL# SQL_TRAC SQL_T SQL_T 
---------- ---------- -------- ----- ----- 
196      60946 DISABLED FALSE FALSE 

　　此处您可以看到，SID 为 196、Serial# 为 60946 的会话未启用跟踪。

　　现在，您可以对等待事件（而不是绑定变量）启用跟踪。可以使用程序包 dbms_monitor 启用跟踪。

begin 
dbms_monitor.session_trace_enable ( 
session_id   => 196, 
serial_num   => 60960, 
waits        => true, 
binds        => false 
   ); 
end; 
/ 

　　现在，如果您要查看会话信息：

　　select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds 
from v$session  
where username = 'HR' 

　　输出结果如下：

SID    SERIAL# SQL_TRAC SQL_T SQL_T 
---------- ---------- -------- ----- ----- 
196      60960 ENABLED  TRUE  FALSE 

　　注意，仅当使用程序包 dbms_monitor 中的过程 session_trace_enable 启用跟踪（而不是通过 alter session set sql_trace = true 或设置事件 10046）时，才会填充视图 V$SESSION。在以后的某个时间点上，如果您要查明哪些会话已经启用了跟踪，可以使用以上查询执行此操作。

　　如果使用程序包 dbms_monitor 中的其他过程（如 SERV_MOD_ACT_TRACE_ENABLE 或 CLIENT_ID_TRACE_ENABLE）启用了跟踪，V$SESSION 视图将不显示该信息。相反，它们将记录到另一个视图 DBA_ENABLED_TRACES 中。可以将该视图与其他相关信息存储连接在一起以查看启用了跟踪的会话。例如，使用

SELECT *  
FROM (SELECT SID, 'SESSION_TRACE' trace_type 
FROM v$session 
WHERE sql_trace = 'ENABLED') 
UNION 
(SELECT SID, t.trace_type 
FROM v$session s, dba_enabled_traces t 
WHERE t.trace_type = 'CLIENT_ID' AND s.client_identifier = t.primary_id) 
UNION 
(SELECT SID, t.trace_type 
FROM v$session s, dba_enabled_traces t, v$instance i 
WHERE t.trace_type = 'SERVICE' 
AND s.service_name = t.primary_id 
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name)) 
UNION 
(SELECT SID, t.trace_type 
FROM v$session s, dba_enabled_traces t, v$instance i 
WHERE t.trace_type = 'SERVICE_MODULE' 
AND s.service_name = t.primary_id 
AND s.module = t.qualifier_id1 
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name)) 
UNION 
(SELECT SID, t.trace_type 
FROM v$session s, dba_enabled_traces t, v$instance i 
WHERE t.trace_type = 'SERVICE_MODULE_ACTION' 
AND s.service_name = t.primary_id 
AND s.module = t.qualifier_id1 
AND s.action = t.qualifier_id2 
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name)) 
UNION 
(SELECT SID, t.trace_type 
FROM v$session s, dba_enabled_traces t, v$instance i 
WHERE t.trace_type = 'DATABASE' 
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name)) 

　　输出结果如下：

SID TRACE_TYPE 
---------- --------------------- 
136 SERVICE_MODULE 
136 SERVICE_MODULE_ACTION 

　　您可以看到，您已经对会话 136 的 Service Module 和 Service Module Action 启用了跟踪。但 DBA_ENABLED_TRACES 并未显示绑定变量或等待事件。 活动会话历史记录

　　现在您应该很清楚自动工作负载信息库 (AWR) 的重要性和有用性。（如果需要，请阅读有关 AWR 的内容。）简单而言，AWR 以预定时间间隔在用户和系统级别捕获与工作负载相关的性能数据，包括按不同的维度、量度捕获的性能统计信息、操作系统统计信息以及 ASH 数据。

　　活动会话历史记录 (ASH) 中记录了最近所有活动会话的活动，它通过内存中的循环缓冲区高效地捕获这些活动并将它们高效地写入 AWR ，可将开销降低到最低程度。可以按不同的维度增加 ASH 数据：TOP SQL、对象、文件、会话、模块、操作等。

　　然而，大多数数据库管理员通常只要诊断临时的性能问题。为诊断此类问题，Oracle 数据库 10g 第 2 版引入了 ASH 报表。ASH 报表可用于整个数据库或特定会话、SQL_ID、模块、操作或这些维度的组合。

　　访问 ASH 报表的方法之一是从数据库页面进行访问。选择 Performance 选项卡，将出现一个类似下图的屏幕。



　　注意“Run ASH Report”按钮（位于红色椭圆框内）。单击该按钮将显示 Active Session History 报表：



　　您可以在该屏幕中输入你期望的时段的起始时间和结束时间的日期和时间。根据需要输入日期和时间，然后按右上角的“Generate Report”按钮。默认的日期和时间将有一个 5 分钟的间隔。

　　单击该按钮后，您将在屏幕上看到该时段的 ASH 报表。如果仔细观察，您将看到该报表类似于 STASPACK 报表；但由于它源自 AWR 数据，因此其中的量度更为有用。下面显示了该屏幕的一小部分：


　
　　可以按按钮“Save to File”将该报表保存到文件，以便以后查看。

　　注意“ASH Report”部分中的链接。在此处，您可以全览不同类型的与性能相关的可用统计信息。例如，您只需单击 Top Events 链接便会看到该时段中的最重要事件。如果该时段出现性能问题，该信息将为您提供很大的帮助。通过查看 ASH 报表中列出的各个维度的偏差，您通常可以确定导致瞬时尖峰的瓶颈。

　　注意，该报表是根据相应的从 AWR 收集的或从内存中缓冲区提取的数据而得出的；因此，如果要诊断先前出现的性能问题，只需激活该时段的 ASH 报表便会看到任何可能已经出现的问题。

　　也可以通过命令行运行 ASH 报表，方法是运行位于 $OH/rdbms/admin/ashrpt.sql 中的由 Oracle 附带的 SQL 脚本。 优化程序统计信息管理

　　Oracle 数据库 10g 提供了多个非常有用的用于管理优化程序统计信息的特性，如一个用于锁定统计信息以防止后期覆盖的特性。这些特性使收集和管理优化程序统计信息这一任务变得轻而易举。在 Oracle 数据库 10g 第 2 版中，您可以使用 Oracle 企业管理器执行该操作。

　　从 Database 主页中，单击 Administration 选项卡。向下滚动到“Statistics Management”的部分，在该部分中您将看到如下所示的 Manage Optimizer Statistics 链接。



　　单击该超链接将转到下个屏幕：Manage Optimizer Statistics 页面。



　　从该屏幕中，您可以使用右侧的超链接执行各种与统计信息相关的任务。例如，使用 Configure 按钮，您可以通过选择一个新窗口轻松地为作业配置一个不同的时段。

　　一个尤其有用的特性是位于“Related Links”下的 Statistics Options 链接。单击它将显示以下屏幕：



　　从该屏幕中您可以执行许多有用的任务，如更改并行度的默认值以及估算百分比。
传输 AWR 数据

　　假设您要尝试解决生产数据库中的某些性能问题。本文有一部分已经说明了 AWR 数据对于分析是很重要的。但在正常的生产期间分析 AWR 数据可能并不合适，甚至是不可行的。相反，您可能希望将数据加载到某个中心位置以进行比较分析。如何完成该任务呢？

　　Oracle 数据库 10g 第 2 版中提供了一个用于此目的的新程序包 DBMS_SWRF_INTERNAL。要将它下载到 Data Pump 转储文件，您将使用过程 AWR_EXTRACT：

1  begin 
2     DBMS_SWRF_INTERNAL.AWR_EXTRACT ( 
3        dmpfile   => 'awr_data.dmp', 
4        dmpdir    => 'TMP_DIR', 
5        bid       => 302, 
6        eid       => 305 
  7     ); 
8* end; 

　　我们来更详细地介绍一下以上各行。

行	说明
3	此处介绍了数据的目标文件名。这是一个 Data Pump 导出文件。如果未提供文件名，则使用默认值 awrdat.dmp。
4	写入转储文件的目录对象。在本示例中，您可能已将目录 TMP_DIR 定义为 /tmp。
5	该时段起始快照的 ID。
6	结束快照 ID。您在此处导出位于 302 和 305 之间的快照。

　　现在，您可以将转储文件 awr_data.dmp 置于一个新位置，并使用同一程序包中的另一个过程 AWR_LOAD 加载它：

1  begin 
2     DBMS_SWRF_INTERNAL.AWR_LOAD ( 
3        SCHNAME => 'ARUP', 
4        dmpfile => 'awr_data', 
5        dmpdir => 'TMP_DIR' 
  6     ); 
7* end; 

　　在此代码中，您将转储文件 awr_data.dmp 的内容加载到由目录对象 TMP_DIR 指定的目录中。加载 AWR 数据时，它并不直接加载到 SYS 模式中，而是先进入另一个模式中。参数 SCHNAME 中提供了模式名（如第 3 行中所示）。导入后，该数据移动到 SYS 模式中：

1  begin 
2     DBMS_SWRF_INTERNAL.MOVE_TO_AWR ( 
3        SCHNAME => 'ARUP' 
  4     ); 
5* end; 

　　此处，您将模式 ARUP 中的 AWR 数据移动到 SYS。

　　正如我在上面指出的，将 AWR 移动到其他数据库具有很多优点和用途。可以在不同的数据库中分析数据而不会对生产造成太大的影响。此外，可以构建一个由从多个数据库中收集的 AWR 数据组成的中心数据库。

　　所有这些加载步骤都已经放置到一个名为 awrload.sql 的文件（位于 $ORACLE_HOME/rdbms/bin 目录中）中。同样，脚本 awrextr.sql 包含提取过程的所有步骤。

　　尽管这个将生产 AWR 数据卸载到备用数据库的机制已被外部化，但它在 Oracle 数据库 10g 第 2 版中的主要用途是帮助解决客户报告的任何问题。使用此方法，客户可以发送 AWR 转储文件格式的原始数据，支持人员随后可以将这些数据导入到他们的模式中以帮助重现和诊断问题。

　　比较时段报表

　　假设出现这样一种情况：您刚刚通知业务和应用程序小组召开一个紧急会议。原因很明显：数据库很慢。（是否有任何其他原因？）开发技术主管指出了会议的要点：于昨天凌晨 1 点至凌晨 3 点之间运行的批处理程序非常慢。该程序在该时间通常运行大约 30 分钟，而昨天凌晨却运行了两个小时。业务小组主管果断声明：“公司经历了潜在的收入损失。”

　　“最近是否进行过更改”，您问到。“没有，一切都未更改”，开发技术主管非常肯定地回答到。（“是的，没错”，您心里是这样认为的。）

　　听起来熟悉吗？如果您从事生产支持这个苦差事的时间甚至是我的十倍，您便会立即同意。您会怎么做？

　　幸运地是，您拥有 Oracle 数据库 10g 第 2 版，并在 Oracle 企业数据库中启动了 Snapshot 或 Time Periods 比较。使用此特性，您可以看到两个时间间隔（而不仅仅是两个时间点）之间的量度更改。例如，在本示例中，您可能请求查看昨天凌晨 1 点至凌晨 3 点之间的快照更改，并查看前天同一时段之间的快照更改。如果批处理过程在前天运行良好，而在昨天凌晨出现异常，那么快照更改将给您提供一个重要线索。

　　以下是它的工作方式：启动 Oracle 企业管理器并转到 Performance 选项卡。在该页面底部，您将看到“Additional Monitoring Links”部分。在该链接组中，搜索“Snapshots”。单击该链接将显示一个类似如下所示的屏幕。

查看本文来源