深入解析oracle：中的等待事件

等待事件
很长时间以来，通过什么样的手段来衡量数据库的状况，发现数据库的问题，优化数据
库的性能一直是人们广为争论的话题。从 Oracle 7.0.12 开始，Oracle 引入了等待事件，随即等
待事件成为了数据库性能优化的一个重要指导。
当一个进程连接到数据库之后，进程所经历的种种等待就开始被记录，并且通过一系列
的动态性能视图进行展现。通过等待事件用户可以很快地发现数据库的性能瓶颈，从而进行针
对性优化和分析。本章将着重介绍等待事件在 Oracle 研究及优化过程中的作用。
9.1 等待事件的源起
等待事件的概念是在 Oracle 7.0.12 中引入的，大致有 100 个等待事件。在 Oracle 9.0 中这
个数目增加到了大约 150 个，在 Oracle 8i 中大约有 220 个事件，在 Oracle 9iR2 中大约有 400
个等待事件，在 Oracle 10gR2 中大约有 874 个等待事件，而在最近的 Oracle 11gR1 中，等待
事件的数目已经接近了 1000 个。
虽然不同的版本和组件安装可能会有不同数目的等待事件，但是这些等待事件都可以通
过查询 V$EVENT_NAME 视图获得：
SQL> select * from v$version where rownum <2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> select count(*) from v$event_name;
COUNT(*)
----------
961
研究 Oracle 的等待事件，V$EVENT_NAME 视图是一个很好的开始，这个视图记录着当
前数据库支持的等待事件及其基本信息。
Oracle 的等待事件，主要可以分为两类，即空闲（idle）等待事件和非空闲（non-idle）等
待事件。空闲事件指 Oracle 正等待某种工作，在诊断和优化数据库的时候，我们不用过多注
意这部分事件。非空闲等待事件专门针对 Oracle 的活动，指数据库任务或应用运行过程中发
生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
在 Oracle 10g 之前，Oracle 的 Statspack 会创建一个视图 stats$idle_event 记录空闲等待事
件：
SQL> select * from stats$idle_event;
EVENT
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·2·
----------------------------------------------------------------
smon timer
pmon timer
rdbms ipc message
Null event
parallel query dequeue
pipe get
client message
SQL*Net message to client
SQL*Net message from client
SQL*Net more data from client
dispatcher timer
virtual circuit status
lock manager wait for remote message
PX Idle Wait
wakeup time manager
15 rows selected.
从 Oracle 10g 开始，Oracle 对等待事件进行了更为详细的分类，V$EVENT_NAME 视图
也增加了相关分类的字段：
SQL> desc v$event_name
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT# NUMBER
EVENT_ID NUMBER
NAME VARCHAR2(64)
PARAMETER1 VARCHAR2(64)
PARAMETER2 VARCHAR2(64)
PARAMETER3 VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
V$EVENT_NAME 视图中的 PARAMETER1、PARAMETER2、PARAMETER3 非常重要，
对于不同的等待事件参数其意义各不相同：
SQL> select name,PARAMETER1,PARAMETER2,PARAMETER3 from v$event_name
2 where name ='db file scattered read';
NAME PARAMETER1 PARAMETER2 PARAMETER3
----------------------- --------------- --------------- ---------------
db file scattered read file# block# blocks
第 1 章 章名章名章名章名章名
·3·
看一下 Oracle 11gR1 中主要分类及各类等待事件的个数：
SQL> SELECT wait_class#, wait_class_id, wait_class, COUNT (*) AS "count"
2 FROM v$event_name GROUP BY wait_class#, wait_class_id, wait_class
3 ORDER BY wait_class#;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS count
----------- ------------- -------------------- ----------
0 1893977003 Other 632
1 4217450380 Application 15
2 3290255840 Configuration 21
3 4166625743 Administrative 51
4 3875070507 Concurrency 26
5 3386400367 Commit 2
6 2723168908 Idle 80
7 2000153315 Network 35
8 1740759767 User I/O 22
9 4108307767 System I/O 23
10 2396326234 Scheduler 3
11 3871361733 Cluster 47
12 644977587 Queueing 4
13 rows selected.
也可以通过查询 V$SYSTEM_WAIT_CLASS 视图获得各类主要等待事件的等待时间和等
待次数等信息，通过分类以及统计信息，可以很直观地快速获得数据库的整体印象，在以下输
出中，可以看出数据库的主要等待消耗在 User I/O 操作上：
SQL> select * from v$system_wait_class order by time_waited;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ----------- -------------------- ----------- -----------
3875070507 4 Concurrency 8433 751
4217450380 1 Application 366 2558
1893977003 0 Other 15690 14765
3386400367 5 Commit 30520 49246
3290255840 2 Configuration 5701 102057
2000153315 7 Network 6261634 103300
4108307767 9 System I/O 1258815 1613868
1740759767 8 User I/O 9027852 3358285
2723168908 6 Idle 4402568 794064698
9 rows selected
从 Oracle 10g 开始，可以通过如下查询来首先了解数据库的空闲等待事件：
select name,wait_class from v$event_name where wait_class='Idle';
在 Oracle 11g 中，空闲等待已经增加到 80 个左右。
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·4·
9.2 从等待发现瓶颈
等待事件所以为众多 DBA 所关注与研究，是因为通过等待事件可以迅速发现数据库瓶颈，
并及时解决问题。在网上，我曾经发起过一个讨论，让大家“列举你认为最重要的 9 个动态性
能视图”，很多人的回复里都选择了和等待事件相关的几个视图，它们是 V$SESSION、
V$SESSION_WAIT 和 V$SYSTEM_EVENT。
来看一下这几个视图的作用及重要意义。
l V$SESSION 视图记录的是数据库当前连接的 Session 信息。
l V$SESSION_WAIT 视图记录的是当前数据库连接的活动 Session 正在等待的资源或
事件信息。
l 由于 V$SESSION 记录的是动态信息，和 Session 的生命周期相关，并不记录历史信
息，所以 Oracle ᨀ供另外一个视图 V$SYSTEM_EVENT 来记录数据库自启动以来所有等待事
件的汇总信息。通过 V$SYSTEM_EVENT 视图，可以迅速地获得数据库运行的总体概况。
9.2.1 V$SESSION 和 V$SESSION_WAIT
由于 V$SESSION 记录当前连接数据库的 Session 信息，而 V$SESSION_WAIT 视图记录
这些 Session 的等待，很多时候我们要联合这两个视图进行查询以获取更多的诊断信息。从
Oracle 10g 开始，为了方便用户，Oracle 开始将这两个视图进行整合。
在 Oracle 10gR1 中，Oracle 在 V$SESSION 中增加关于等待事件的字段，实际上也就是把
原来 V$SESSION_WAIT 视图中的所有字段全部整合到了 V$SESSION 视图中（如果进一步研
究你会发现，实际上 V$SESSION 的底层查询语句及 X$表已经有了变化）。这一变化使得查询
得以简化，但是也使得 V$SESSION_WAIT 开始变得多余。
此外 V$SESSION 中还增加了 BLOCKING_SESSION 等字段，以前需要通过 dba_waiters
等视图才能获得的信息，现在也可以直接从 V$SESSION 中得到了。
在 Oracle 10gR2 中，Oracle 又为 V$SESSION 增加了额外几个字段：SERVICE_NAME、
SQL_TRACE、SQL_TRACE_WAITS、SQL_TRACE_BINDS。这几个字段显示当前 Session 连
接方式及是否启用了 SQL_TRACE 跟踪等。
在 Oracle 11gR1 中，V$SESSION 的内容进一步增强，增加了很多新的字段，比如
SQL_EXEC_START、SQL_EXEC_ID 用于记录 SQL 执行的开始时间及执行 ID（相应的还有
PREV_EXEC_START、PREV_EXEC_ID 等字段）。
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sid,username,sql_exec_start,sql_exec_id from v$session
2 where sql_exec_id is not null;
SID USERNAME SQL_EXEC_START SQL_EXEC_ID
---------- ------------------------------ ------------------- -----------
126 EYGLE 2008-07-16 10:44:25 16777236
130 SYS 2008-07-16 10:44:25 16777217
第 1 章 章名章名章名章名章名
·5·
在新的数据库版本中，Oracle 在小处动的手脚也是非常多的，而无疑这些小手脚会给用户
的管理维护带来极大的方便。以下是 Oracle 9iR2 中 V$SESSION_WAIT 视图的结构：
SQL> desc v$session_wait
Name Null? Type
-------- -------- ------------------------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
其中，event 代表等待事件的名称，p<n>text 用以᧿述具体的参数，p<n>分别代表以十进
制定义的参数（parameter）参数值，p<n>Raw 是以十六进制表示的参数值。对于不同 event，
具体参数表示的含义也不相同，可以通过 v$event_name 视图来查看这些参数的定义。
9.2.2 V$SESSION_EVENT 和 V$SYSTEM_EVENT
上一节ᨀ到的 V$SESSION 及 V$SESSION_WAIT 视图记录了活动会话当前正在发生的等
待，但是要知道一个活动会话在其生命周期只能可能经历很多等待，这些等待通过
V$SESSION_EVENT 视图记录。但是需要注意的是，这个视图记录的是累积信息，同一会话
对于同一事件发生的多次等待会被累计。以下是一个会话的等待事件输出：
SQL> select sid,event,time_waited,time_waited_micro
2 from v$session_event where sid=546 order by 3;
SID EVENT TIME_WAITED TIME_WAITED_MICRO
---------- ---------------------------------------- ----------- -----------------
546 log file sync 0 3084
546 latch: library cache lock 1 7171
546 latch: library cache pin 2 16112
546 latch: library cache 4 43621
546 buffer busy waits 9 86652
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·6·
546 latch: shared pool 10 103100
546 db file scattered read 12 123146
546 latch: cache buffers chains 17 165332
546 log file switch completion 57 572292
546 events in waitclass Other 88 877450
546 db file sequential read 1471 14713213
546 os thread startup 14224 142236350
546 control file sequential read 49067 490672360
546 rdbms ipc message 1021388726 1.0214E+13
已选择 14 行。
V$SESSION_EVENT 的信息和会话生命周期相关， 这些信息同时会被累积到
V$SYSTEM_EVENT 视图作为数据库整体等待数据保存，比如：
SQL> select event,total_waits,time_waited,average_wait from v$system_event
2 where event='latch: shared pool';
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
latch: shared pool 13931 2770 .2
但是注意，V$SESSION_EVENT 或者 V$SYSTEM_EVENT 视图的累积信息以及关于等待
的平均计算，使我们无法得知个别等待消耗的时间长短。
为了解决这一问题，Oracle 10g 引入了一个新的视图 v$event_histogram，通过这个视图可
以看到等待事件的柱状图分布，从而可以对一个等待事件的具体分布有进一步的了解，在以下
查询输出中可以看到，Shared Pool Latch 的竞争主要是 10 毫秒以内的短时竞争，但是注意等
待时间在 256 毫秒左右的等待也有 5 次，长时间的 Latch 竞争是在数据库优化时需要认真关注
的：
SQL> SELECT event, wait_time_milli, wait_count
2 FROM v$event_histogram WHERE event = 'latch: shared pool';
EVENT WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
latch: shared pool 1 8604
latch: shared pool 2 2248
latch: shared pool 4 1208
latch: shared pool 8 781
latch: shared pool 16 400
latch: shared pool 32 150
latch: shared pool 64 49
latch: shared pool 128 21
latch: shared pool 256 5
已选择 9 行。
第 1 章 章名章名章名章名章名
·7·
9.2.3 Oracle 11g 实时 SQL 监控
前面ᨀ到，在 Oracle Database 11g 中，v$session 视图增加了一些新的字段，这其中包括
SQL_EXEC_START 和 SQL_EXEC_ID，这两个字段实际上代表了 Oracle 11g 的一个新特性：
实时的 SQL 监视（Real Time SQL Monitoring）。
在 Oracle 11g 之前的版本，长时间运行的 SQL 可以通过监控 v$session_longops 来观察，
当某个操作执行时间超过 6 秒，就会被记录在 v$session_longops 中，通常可以监控到全表扫
᧿、全索引扫᧿、哈希联接、并行查询等操作；而在 Oracle 11g 中，当 SQL 并行执行时，会
立即被实时监控到，或者当 SQL 单进程运行时，如果消耗超过 5 秒的 CPU 或 I/O 时间，它也
会被监控到。监控数据被记录在 V$SQL_MONITOR 视图中，当然也可以通过 Oracle 11g 新增
的 package DBMS_MONITOR 来主动对 SQL 执行监控部署。
来看一下主要视图 V$SQL_MONITOR 的结构：
SQL> desc v$sql_monitor
Name Null? Type
----------------------------------------- -------- ----------------------------
KEY NUMBER
STATUS VARCHAR2(19)
FIRST_REFRESH_TIME DATE
LAST_REFRESH_TIME DATE
REFRESH_COUNT NUMBER
SID NUMBER
PROCESS_NAME VARCHAR2(5)
SQL_ID VARCHAR2(13)
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_CHILD_ADDRESS RAW(4)
SESSION_SERIAL# NUMBER
PX_SERVER# NUMBER
PX_SERVER_GROUP NUMBER
PX_SERVER_SET NUMBER
PX_QCINST_ID NUMBER
PX_QCSID NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
FETCHES NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·8·
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
注意这里的 SQL_EXEC_ID 就是 V$SESSION 视图中新增字段的来源。这个视图还记录了
SQL 的 CPU_TIME 以及 BUFFER_GETS 等重要信息，对于诊断 SQL 性能问题具有极大的帮
助。结合 V$SQL_MONITOR 视图与 V$SQL_PLAN_MONITOR 视图可以进一步查询 SQL 的
执行计划等信息。联合一些其他视图，如 v$active_session_history、v$session、v$session_longops、
v$sql、v$sql_plan 等，可以获得关于 SQL 的更多信息。
V$SQL_MONITOR 收集的信息每秒刷新一次，接近实时，当 SQL 执行完毕，信息并不会
立即从 v$sql_monitor 中删除，至少会保留 1 分钟，v$sql_plan_monitor 视图中的执行计划信息
也是每秒更新一次，当 SQL 执行完结，它们同样至少被保留 1 分钟。
实时 SQL 监控需要 statistics_level 初始化参数设置为 TYPICAL 或 ALL：
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> SELECT statistics_name,session_status,system_status,activation_level,session_settable
2 FROM v$statistics_level WHERE statistics_name = 'SQL Monitoring';
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
------------------------------ -------- -------- ------- ---
SQL Monitoring ENABLED ENABLED TYPICAL YES
同时 CONTROL_MANAGEMENT_PACK_ACCESS 参数必须是 DIAGNOSTIC+TUNING
（这是缺省设置）：
SQL> show parameter control_manage
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
在如上设置下，数据库会启动自动的实时 SQL 监控，Oracle 还ᨀ供 Hints 可以强制制定对
SQL 执行监控或者不允许执行监控，这两个 Hints 是 monitor 与 no_monitor。
强制对某个 SQL 使用实时监控可以如下改写 SQL：
select /*+ monitor */ count(*) from emp where sal > 5000;
指定不执行实时监控：
select /*+ no_monitor */ count(*) from emp where sal >5000;
查看数据库中已经生成的监控信息可以使用 DBMS_SQLTUNE 包来实现：
set long 10000000
set longchunksize 10000000
第 1 章 章名章名章名章名章名
·9·
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
以下是一个 Oracle Database 11g 生产环境中的查询输出，系统中目前记录了一条 SQL 的
监视信息。这条 SQL 使用了全表扫᧿，看起来缺乏索引，Oracle 现在自动为用户记录了详细
的信息：
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
SQL> REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
-----------------------------------------------------------------------------------------
select * from forecast where cityid = '886' and to_char(forecastdate,'YYYY/MM/DD') =
'2008/07/15'
-----------------------------------------------------------------------------------------
Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session ID : 71
REPORT_SQL_MONITOR
-----------------------------------------------------------------------------------------
SQL ID : 1rrshaasrsa1z
SQL Execution ID : 16777218
Plan Hash Value : 2831319728
Execution Started : 07/15/2008 15:47:31
First Refresh Time : 07/15/2008 15:47:35
Last Refresh Time : 07/15/2008 15:47:37
--------------------------------------------------------------------
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Reads |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | |
---------------------------------------------------------------------
| 3.51 | 0.67 | 0.00 | 2.84 | 1 | 8350 | 8203 |
--------------------------------------------------------------------
SQL Plan Monitoring Details
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·10·
======================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Starts |
| | | | (Estim) | | Active(s) | Active | |
======================================================================================
| 0 | SELECT STATEMENT | | | 2478 | 1 | +6 | 1 |
| 1 | TABLE ACCESS FULL | FORECAST | 25 | 2478 | 5 | +2 | 1 |
--------------------------------------------------------------------------------------
对于数据库中已经捕获的 SQL，通过其 SQL_ID，使用 DBMS_SQLTUNE 程序包中的
REPORT_SQL_MONITOR 函数，我们可以生成更为直观的 SQL 报告输出，辅助分析和诊断。
该函数的主要参数如下图所示：
通常情况下，ᨀ供 SQL_ID 等少数参数，即可生成报告，TYPE 参数用于指定报告类型，
这里可以指定生成：TEXT、HTML、XML、ACTIVE 模式的报告。ACTIVE 模式的报告最为
华丽直观。
首先可以通过查询 v$sql_monitor 获得那些被监控收集过的 SQL 信息：
SQL> select sql_id from v$sql_monitor;
SQL_ID
-------------
6rqxj647ut9pn
f4kcr0dn9rv6z
f6cz4n8y72xdc
以下是简单的查询语句，用于生成 HTML 类型的报告：
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
第 1 章 章名章名章名章名章名
·11·
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '6rqxj647ut9pn',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
下图是报告的页面展示：
最全面的报告是 ACTIVE 类型，这个类型的报告会通过 OTN 站点获得展现的框架和 JS
脚本，如果不能连接到公网，你可以在本地构建相应的文件，我在自己的站点保存了这些脚本：
mkdir -p eygle.com/sqlmon
cd eygle.com/sqlmon
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf
这样在生成 SQL 报告时，就可以调用自己网站的脚本文件。以下是通过脚本调用，生成
了一个 ACTIVE 类型的报告：
[eygle@enmoteam2 ~]$ sqlplus "/ as sysdba" @eygle.sql
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 6 15:01:55 2012
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·12·
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在脚本 eygle.sql 中定义了 SQL_ID ,通过这个 SQL_ID 生成了 ACTIVE REPORT：
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '6rqxj647ut9pn',
type => 'ACTIVE',
report_level => 'ALL',
base_path => 'http://www.eygle.com/sqlmon') AS report
FROM dual;
SPOOL OFF
这样生成的报告较以前的 SQL Report 更为直观，报告的第一部分展示了 SQL 的执行时间、
逻辑读、IO 请求次数及读取数据量等信息：
接下来是具体细节，包括执行计划的 Flash 展现，可以通过柱状图清晰看到各个执行步骤
的时间消耗比重，以及 CPU 消耗：
第 1 章 章名章名章名章名章名
·13·
在 PLAN 页面，还有执行计划的图形展现，非常清晰直观：
对于并行执行的 SQL，还可以通过相应的并行执行页面，显示不同进程的执行性能等：
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·14·
这是 Oracle 数据库在自动化诊断方面的又一增强，也可以通过 OEM 来观察其输出展现。
9.2.4 从 V$SQLTEXT 中追踪
在数据库出现瓶颈时，通常可以从 V$SESSIION_WAIT 找到那些正在等待资源的 Session，
通过 Session 的 sid，联合 V$SESSION 和 V$SQLTEXT 视图可以捕获这些 Session 正在执行的
SQL 语句。
以下是一个生产数据库的问题诊断和解决过程，可以从中体会一下等待事件在解决问题
中的指导作用。该生产环境的操作系统为 Solaris 8，数据库版本为 9.1.7.4，业务及开发人员报
告系统运行缓慢，已经影响业务系统正常使用，请求协助诊断。
数据库运行缓慢，转换为数据库语言那就是数据库可能经历了等待，那么可以通过
V$SESSION_WAIT（从 Oracle 10g，V$SESSION 视图可以取代 V$SESSION_WAIT 的这一诊
断功能）视图来入手。查询 V$SESSION_WAIT 获取各进程等待事件：
SQL> select sid,event,p1,p1text from v$session_wait;
SID EVENT P1 P1TEXT
---------- ------------------------------ ---------- ------------------------------
124 latch free 1.6144E+10 address
140 buffer busy waits 17 file#
66 buffer busy waits 17 file#
10 db file sequential read 17 file#
18 db file sequential read 17 file#
54 db file sequential read 17 file#
49 db file sequential read 17 file#
48 db file sequential read 17 file#
46 db file sequential read 17 file#
45 db file sequential read 17 file#
……
244 rows selected.
对于本案例，通过以上输出发现存在大量 db file scattered read 及 db file sequential read 等
待，并且全表扫᧿的等待都位于文件号为 17 的数据文件上。显然全表扫᧿等操作成为系统最
严重的性能影响因素。
说明：db file scattered read（DB 文件分散读取）这种情况通常显示与全表扫描相关的等待。当数
据库进行全表扫时，基于性能的考虑，数据会分散（scattered）读入 Buffer Cache。如果这
个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适
的索引，可能需要检查这些数据表已确定是否进行了正确的设置。
然而这个等待事件不一定意味着性能低下，在某些条件下 Oracle 会主动使用全表扫描来替
换索引扫描以提高性能，这和访问的数据量有关，在 CBO 下 Oracle 会进行更为智能的选
择，在 RBO 下 Oracle 更倾向于使用索引。
第 1 章 章名章名章名章名章名
·15·
9.2.5 捕获相关 SQL
确定这些进程因为数据访问产生了等待，可以考虑捕获这些 SQL 以发现问题。这里用到
了以下脚本 getsqlbysid.sql，该脚本通过已知 session 的 sid，联合 v$session、v$sqltext 视图，
获得相关 Session 正在执行的完整 SQL 语句。
SELECT sql_text FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID = '&sid')
ORDER BY piece ASC;
使用该脚本，通过从 v$session_wait 中获得的等待全表或索引扫᧿的进程 SID，捕获问题
SQL：
SQL> @getsqlbysid
Enter value for sid: 18
old 5: where b.sid='&sid'
new 5: where b.sid='18'
SQL_TEXT
-----------------------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 2047
order by i.datpublishdate desc, i.numorder desc
SQL> /
Enter value for sid: 54
old 5: where b.sid='&sid'
new 5: where b.sid='54'
SQL_TEXT
-----------------------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 33
order by i.datpublishdate desc, i.numorder desc
SQL> /
Enter value for sid: 49
old 5: where b.sid='&sid'
new 5: where b.sid='49'
SQL_TEXT
-------------------------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 26
order by i.datpublishdate desc, i.numorder desc
对几个进程进行跟踪，分别得到以上 SQL 语句，这些 SQL 可能就是问题产生的根源。以
上语句如果良好编码应该使用绑定变量，但是目前这个不是我们关心的。
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·16·
使用该应用用户连接，通过 SQL*Plus 的 AUTOTRACE 功能检查以上 SQL 的执行计划：
SQL> set autotrace trace explain
SQL> select i.vc2title,i.numinfoguid
2 from hs_info i where i.intenabledflag = 1
3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate
4 and i.numcatalogguid = 3475
5 order by i.datpublishdate desc, i.numorder desc ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=228 Card=1 Bytes=106)
1 0 SORT (ORDER BY) (Cost=228 Card=1 Bytes=106)
2 1 TABLE ACCESS (FULL) OF 'HS_INFO' (Cost=218 Card=1 Bytes=106)
SQL> select count(*) from hs_info;
COUNT(*)
----------
227404
通过执行计划看到以上查询使用了全表扫᧿，而该表这里有 22 万记录，全表扫᧿已经不
再适合。检查该表，存在以下索引：
SQL> select index_name,index_type from user_indexes where table_name='HS_INFO';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
HSIDX_INFO1 FUNCTION-BASED NORMAL
HSIDX_INFO_SEARCHKEY DOMAIN
PK_HS_INFO NORMAL
进一步的检查该表索引键值：
SQL> select index_name,column_name from user_ind_columns where table_name ='HS_INFO';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
HSIDX_INFO1 NUMORDER
HSIDX_INFO1 SYS_NC00024$
HSIDX_INFO_SEARCHKEY VC2INDEXWORDS
PK_HS_INFO NUMINFOGUID
SQL> desc hs_info
Name Null? Type
--------------------------------- -------- --------------------------------------------
NUMINFOGUID NOT NULL NUMBER(15)
NUMCATALOGGUID NOT NULL NUMBER(15)
INTTEXTTYPE NOT NULL NUMBER(38)
VC2TITLE NOT NULL VARCHAR2(60)
第 1 章 章名章名章名章名章名
·17·
VC2AUTHOR VARCHAR2(100)
……
检查发现在 numcatalogguid 字段上并没有索引，该字段具有很好的区分度，考虑在该字段
创建索引以消除全表扫᧿。
SQL> create index hs_info_NUMCATALOGGUID on hs_info(NUMCATALOGGUID);
Index created.
SQL> set autotrace trace explain
SQL> select i.vc2title,i.numinfoguid
2 from hs_info i where i.intenabledflag = 1
3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate
4 and i.numcatalogguid = 3475
5 order by i.datpublishdate desc, i.numorder desc ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=106)
1 0 SORT (ORDER BY) (Cost=12 Card=1 Bytes=106)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HS_INFO' (Cost=2 Card=1 Bytes=106)
3 2 INDEX (RANGE SCAN) OF 'HS_INFO_NUMCATALOGGUID' (NON-UNIQUE) (Cost=1
Card=1)
观察系统状况，原大量等待消失：
SQL> select sid,event,p1,p1text from v$session_wait where event not like 'SQL%';
SID EVENT P1 P1TEXT
---------- ------------------------------ ---------- --------------------------
1 pmon timer 300 duration
2 rdbms ipc message 300 timeout
3 rdbms ipc message 300 timeout
6 rdbms ipc message 180000 timeout
59 rdbms ipc message 6000 timeout
118 rdbms ipc message 6000 timeout
275 rdbms ipc message 30000 timeout
147 rdbms ipc message 6000 timeout
62 rdbms ipc message 6000 timeout
11 rdbms ipc message 30000 timeout
4 rdbms ipc message 300 timeout
305 db file sequential read 17 file#
356 db file sequential read 17 file#
19 db file scattered read 17 file#
5 smon timer 300 sleep time
15 rows selected.
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·18·
至此，此问题得以解决。
通过以上案例，可以知道从等待事件进行追踪的诊断方法，这种方法在日常数据库诊断
中很常用，在后面章节中还将会进一步的详细介绍。
9.3 Oracle 10g 的增强
虽然 V$SESSION_WAIT 记录的信息如此重要，但是这些重要的信息是随 Session 而消逝
的，如果我们希望获得数据库的历史状态及 Session 的历史等待信息等数据，是不可得的。
所以很多时候很难回答这样的问题：
¡ 这个系统昨天是什么样子的？
¡ 今天和昨天相比有什么不同？
¡ 1 个小时前的那次性能下滑是哪个用户引起的？
¡ 是哪些事件使我们今天用了更多的时间来等待？
你也可能一次又一次地听到 Oracle Support 这样问：
¡ 问题出现时系统是怎样的状况？
¡ 问题出现时系统有哪些等待？
¡ 你能否重现（Reproduce）问题以便我们判断？
很多这样的问题是极其使人恼火的，我们当然不希望问题重现（reproduce）再次引起宕
机或业务损失，而那些问题看起来分明是不作为的责任推卸。可是事实是，失去了现场和当时
的状态以及 Session 的实时信息，DBA 也的确很难判断问题的所在。
从 Oracle 10g 开始，Oracle 开始改变这一切，所以赘述这么多，我只想更郑重地告诉大家，
这一改变是多么的重要。
9.3.1 新增 v$session_wait_history 视图
为了更有效地保留 Session 信息，Oracle 10g 新增加了一个 v$session_wait_history 视图，
该视图用以记录活动 Session 的最近 10 次等待事件。以下查询输出了 SID 为 120 的会话最近
的 10 次等待，注意其中关于 db file sequential read 等待事件的记录，可以从中得知每次等待发
生的文件号以及数据块，以前这些信息一旦成为历史就无法获取：
SQL> select event,p1text,p1,p2text,p2,p3text,p3,wait_time
2 from v$session_wait_history where sid=120;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME
---------------------------- -------- --- ------- ------ -------- -- ----------
db file sequential read file# 14 block# 97456 blocks 1 0
row cache lock cache id 11 mode 0 request 3 49
row cache lock cache id 11 mode 0 request 3 0
db file sequential read file# 10 block# 260171 blocks 1 1
db file sequential read file# 14 block# 570536 blocks 1 10
第 1 章 章名章名章名章名章名
·19·
db file sequential read file# 14 block# 6363 blocks 1 12
db file sequential read file# 14 block# 35285 blocks 1 9
db file sequential read file# 14 block# 40674 blocks 1 9
db file sequential read file# 14 block# 69631 blocks 1 1
db file sequential read file# 14 block# 82498 blocks 1 3
10 rows selected
v$session_wait_history 缺省记录活动会话最近的 10 次等待，这个约束受到一个隐含参数
的影响，这个参数就是_session_wait_history，其缺省值是 10，如果想保留活动会话更多的等
待，可以通过修改这个隐含参数来进行：
SQL> @GetHidPar
Enter value for par: session_wait
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%session_wait%'
NAME VALUE PDESC
------------------------------ ---------- -------------------------------------------
_session_wait_history 10 enable session wait history collection
通过 v$session_wait_history 这个视图，可以将 V$SESSION_WAIT 的功能进行延伸，获取
更多的相关信息辅助数据库问题诊断。这是 Oracle 迈出的一小步。
9.3.2 ASH 新特性
如果说 v$session_wait_history 是一小步，那么 ASH 则是 Oracle 迈出根本变革的一大步。
从 Oracle 10g 开始，Oracle 引入了 ASH 新特性，也就是活动 Session 历史信息记录（Active
Session History，ASH）。ASH 以 V$SESSION 为基础，每秒钟采样一次，记录活动会话等待的
事件。因为记录所有会话的活动是非常昂贵的，所以不活动的会话不会被采样，这一点从 ASH
的“A”上就可以看出。采样工作由 Oracle 10g 新引入的一个后台进程 MMNL 来完成。
是否启用 ASH 功能，受一个内部隐含参数控制：
SQL> @GetHparDes.sql
Enter value for par: ash_en
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%ash_en%'
NAME VALUE DESCRIB
------------- -------- ---------------------------------------------------------
……
_ash_enable TRUE To enable or disable Active Session sampling and flushing
而采样时间同样由另一个内部隐含参数决定：
SQL> @GetHparDes.sql
Enter value for par: ash_sampling
old 6: AND x.ksppinm LIKE '%&par%'
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·20·
new 6: AND x.ksppinm LIKE '%ash_sampling%'
NAME VALUE DESCRIB
---------------------- ------ ----------------------------------------------------------
_ash_sampling_interval 1000 Time interval between two successive Active Session samples in
millisecs
1000 毫秒，正好是 1 秒的时间。
注意：隐含参数通常具有特殊的作用，一般不建议用户查询或者修改，本文大量引用隐含参数的
目的只有一个，那就是希望大家知道，所有我们在文档中见到的限制、约束、阈值、比率
都是有来源的，只要足够细心，我们就能找出数据库的真相，不再靠记忆来学习。
很多人可能更关心性能，如此频繁的采样是否会极大地影响数据库的性能呢？采样的性
能影响无疑是存在的，但是因为 Oracle 的采样工具可以直接访问 Oracle 10g 内部结构，所以
是极其高效的，对于性能的影响也非常小，这也正是 Oracle ᨀ供优化或诊断工具的优势所在。
ASH 信息被设计为在内存中滚动的，在需要的时候早期的信息是会被覆盖的。ASH 记录
的信息可以通过 v$active_session_history 视图来访问，对于每个活动 SESSION，每次采样会在
这个视图中记录一行信息。
这部分内存在 SGA 中分配：
SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ ----------------------- ----------
shared pool ASH buffers 6291456
注意 ASH buffers 的最小值为 1MB，最大值不超过 30MB，大小按照以下算法分配：
Max ( Min (cpu_count * 2MB, 5% * SHARED_POOL_SIZE, 30MB), 1MB)
在以上公式中，如果 SHARED_POOL_SIZE 未显示设置，则限制为 2%*SGA_TARGET。
这一算法在 Oracle 10g 的不同版本中，可能不同。根据这个算法，我的采样系统分配的 ASH
Buffers 为 6MB。
SQL> select name,value,display_value from v$parameter
2 where name in ('shared_pool_size','cpu_count');
NAME VALUE DISPLAY_VALUE
------------------------------ -------------------- --------------------
cpu_count 4 4
shared_pool_size 125829120 120M
另外一个生产系统中，这一内存分配为 8MB：
SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> show parameter cpu_count
NAME TYPE VALUE
第 1 章 章名章名章名章名章名
·21·
------------------------------------ ----------- ------------------------------
cpu_count integer 4
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 900M
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> select * from v$sgastat where name like 'ASH%';
POOL NAME BYTES
------------ ------------------------------ ----------
shared pool ASH buffers 8388608
记录在 SGA 中的 ASH 信息，可以通过 v$session_wait_history 进行查询：
SQL> desc v$session_wait_history
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
SID NUMBER Y
SEQ# NUMBER Y
EVENT# NUMBER Y
EVENT VARCHAR2(64) Y
P1TEXT VARCHAR2(64) Y
P1 NUMBER Y
P2TEXT VARCHAR2(64) Y
P2 NUMBER Y
P3TEXT VARCHAR2(64) Y
P3 NUMBER Y
WAIT_TIME NUMBER Y
WAIT_COUNT NUMBER Y
可以通过 Oracle ᨀ供的工具生成 ASH 的报告，报告可以以几分钟未为跨度对数据库进行
精确分析；也可以以数小时或数天为时间跨度，为数据库ᨀ供概要分析。
生成 ASH 报告主要可以通过两种方式：脚本方式和 OEM 图形方式。
1．脚本方式
调用$ORACLE_HOME/rdbms/admin/ashrpt.sql 脚本，回答一系列问题之后，就可以生成一
个 ASH 的报告，报告包括 TOP 等待事件、TOP SQL、TOP SQL 命令类型、TOP Sessions 等
内容，摘录部分报告内容如下。
调用 ashrpt.sql 脚本：
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·22·
SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3965153484 DANALY 1 danaly
……
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库可用的采样数据：
Oldest ASH sample available: 31-Mar-06 08:31:52 [ 4325 mins in the past]
Latest ASH sample available: 04-Sep-06 22:39:11 [ ###### mins in the past]
……
用户定义概要如下：
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 3965153484
Inst num : 1
Begin time : 02-Apr-06 08:37:42
End time : 03-Apr-06 08:37:59
Slot width : Default
Report targets : 0
Report name : ashrpt_1_0403_0837.txt
生成的报告如下：
ASH Report For DANALY/danaly
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
DANALY 3965153484 danaly 1 10.2.0.1.0 NO danaly.hurrr
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
4 900M (100%) 772M (85.8%) 210M (23.3%) 9.0M (0.9%)
Analysis Begin Time: 02-Apr-06 08:37:42
Analysis End Time: 03-Apr-06 08:37:59
Elapsed Time: 1,440.3 (mins)
Sample Count: 2,946
第 1 章 章名章名章名章名章名
·23·
Average Active Sessions: 0.03
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: DANALY/danaly (Apr 02 08:37 to 08:37)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 22.84 0.01
log file sync Commit 19.23 0.01
-------------------------------------------------------------
Top Background Events DB/Inst: DANALY/danaly (Apr 02 08:37 to 08:37)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
log file parallel write System I/O 21.83 0.01
control file parallel write System I/O 15.44 0.01
db file parallel write System I/O 15.41 0.01
CPU + Wait for CPU CPU 5.26 0.00
-------------------------------------------------------------
……..
Top SQL Command Types DB/Inst: DANALY/danaly (Apr 02 08:37 to 08:37)
……..
Distinct Avg Active
SQL Command Type SQLIDs % Activity Sessions
---------------------------------------- ---------- ---------- ----------
INSERT 8 11.30 0.00
SELECT 54 6.79 0.00
PL/SQL EXECUTE 21 2.17 0.00
UPDATE 10 2.07 0.00
-------------------------------------------------------------
Top SQL Statements DB/Inst: DANALY/danaly (Apr 02 08:37 to 08:37)
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·24·
74y62ap82k1xk 2315018254 7.74 CPU + Wait for CPU 7.74
INSERT INTO MGMT_CURRENT_METRICS (TARGET_GUID, KEY_VALUE, COLLECTION_TIMESTAMP,
METRIC_GUID, VALUE, STRING_VALUE) VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 )
……
-------------------------------------------------------------
Top Sessions DB/Inst: DANALY/danaly (Apr 02 08:37 to 08:37)
……
End of Report
Report written to ashrpt_1_0403_0837.txt
2．OEM 图形方式
使用 OEM，可以在性能页，单击“运行 ASH 报告”按钮生成 ASH 报告，如图 9-1 所示。
图 9-1 生成 ASH 报告
OEM 生成的 ASH 报告非常清晰和直观。ASH 的概况信息如图 9-2 所示。等待事件信息
如图 9-3 所示，等待参数信息如图 9-4 所示，TOP SQL 信息如图 9-5 所示。只要试用一下就可
以感受到 ASH 的强大功能。
第 1 章 章名章名章名章名章名
·25·
图 9-2 ASH 概况信息
图 9-3 等待事件信息
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·26·
图 9-4 等待参数信息
图 9-5 TOP SQL 信息
只要试用一下就可以感受到 ASH 的强大功能。
9.3.3 自动负载信息库：AWR 的引入
内存中记录的 ASH 信息始终是有限的，为了保存历史数据，这些信息最终需要写入磁盘。
这些历史信息的存储，引出了 Oracle10g 的另外一个新特性：自动负载信息库 （Automatic
Workload Repository，AWR）。
1．AWR 的采样机制
AWR 收集关于该特定数据库的操作统计信息和其他统计信息，Oracle 以固定的时间间隔
（默认为每小时一次）为其所有重要统计信息和负载信息执行一次快照，并将这些快照存储在
AWR 中。这些信息在 AWR 中保留给定的时间（默认为一周），然后被清除。执行快照的频
率及其保持时间都可以自定义，以满足不同环境的独特需要。
AWR 的采样工作由后台进程 MMON 每 60 分钟执行一次，ASH 信息同样会被采样写出
到 AWR 负载库。虽然 ASH buffers 被设计为保留 1 小时的信息，但是很多时候这个内存是不
第 1 章 章名章名章名章名章名
·27·
足够的，当 ASH buffers 写满之后，另外一个后台进程 MMNL 将会主动将 ASH 信息写出。由
于数据量巨大，把所有的 ASH 数据写到磁盘上是不可接受的。一般是在写到磁盘的时候过滤
这个数据，写出的数据占采样数据的 10%，写出时通过 direct-path insert 完成，尽量减少日志
生成，从而最小化数据库性能影响。
通过图 9-6 可以理解一下 ASH 与 AWR 的关系。
图 9-6 ASH 与 AWR 的关系
AWR 的行为受到数据库另外一个重要初始化参数 STATISTICS_LEVEL 的影响，该参数
有 3 个可选值。
¡ BASIC：设置为 BASIC 时，AWR 的统计信息收集和所有自我调整的特性都被关闭。
¡ TYPICAL：设置为 TYPICAL 时，数据库收集部分统计信息，这些信息为典型的数据
库监控需要，是数据库的缺省设置。
¡ ALL：所有可能的统计信息都被收集。
ASH 信息的写出比例受一个隐含参数控制：
SQL> @GetHparDes.sql
Enter value for par: filter_ratio
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%filter_ratio%'
NAME VALUE DESCRIB
------------------------------ ---------- ------------------------------------------------
_ash_disk_filter_ratio 10 Ratio of the number of in-memory samples to the
number of samples actually written to disk
写出到 AWR 负载库的 ASH 信息记录在 AWR 的基础表 wrh$active_session_hist 中，
wrh$active_session_hist 是一个分区表，Oracle 会自动进行数据清理。
wrh$active_session_hist 记录的这些历史信息，可以通过 dba_hist_active_sess_history 视图
进行聚合查询，通过简化后的图 9-7 来看一下 Oracle 以 Session 为起点的一系列用以追踪和诊
断的数据库对象。
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·28·
图 9-7 一系列用以追踪和诊断的数据库对象
简单总结一下：
l V$SESSION 代表数据库活动的开始，是为源起；
l V$SESSION_WAIT 视图用以实时记录活动 SESSION 的等待情况，是当前信息；
l V$SESSION_WAIT_HISTORY 是对 V$SESSION_WAIT 的简单增强，记录活动
SESSION 的最近 10 次等待；
l V$ACTIVE_SESSION_HISTORY 是 ASH 的核心，用以记录活动 Session 的历史等待
信息，每秒采样 1 次，这部分内容记录在内存中，期望值是记录 1 个小时的内容；
l WRH$_ACTIVE_SESSION_HISTORY 是 V$ACTIVE_SESSION_HISTORY 在 AWR
的存储地，V$ACTIVE_SESSION_HISTORY 中记录的信息会被定期（每小时 1 次）
地刷新到负载库中，并缺省保留一个星期用于分析；
l DBA_HIST_ACTIVE_SESS_HISTORY 视图是 WRH$_ACTIVE_SESSION_HISTORY
视图和其他几个视图的联合展现，我们通常通过这个视图进行历史数据的访问。
通过以上分析过程可以看到，关于 Session 信息的记录，Oracle 从不同的粒度进行了增强，
目的只有一个，那就是全面真实地记录、监控和反映数据库的运行状况。
2．AWR 的采样数据存储
AWR 记录的信息还远不止于此，通过系统的自动采样，AWR 可以收集数据库运行的各
方面统计信息及等待等重要数据，ᨀ供给数据库诊断分析使用。当然 AWR 的信息需要独立存
储，在 Oracle 10g 中，新增的 SYSAUX 表空间是这类信息的存储地：
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES/1024 "MB"
2 from V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME LIKE '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME MB
-------------- ----------------------------------------------- ------------ --------
SM/AWR Server Manageability - Automatic Workload Repository SYS 250.875
在 Oracle 10g 之前的版本中，类似的功能是由 Statspack 实现，但是 Statspack 需要由用户
自行安装调度，并且其收集的信息十分有限。我们一直ᨀ到的 Session 历史信息 Statspack 就是
第 1 章 章名章名章名章名章名
·29·
无法ᨀ供的。AWR 大大强化了这部分信息，由于 AWR 收集的信息十分完备，所以经常被称
为“数据库的数据仓库”。
AWR收集的信息通过一系列的视图展现出来，可以查询这些视图获得数据库的信息采样：
SQL> select object_name,object_type from dba_objects
2 where object_name like 'DBA_HIST%' and object_type='VIEW' and rownum <5;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DBA_HIST_DATABASE_INSTANCE VIEW
DBA_HIST_SNAPSHOT VIEW
DBA_HIST_SNAP_ERROR VIEW
DBA_HIST_BASELINE VIEW
这些系统视图的底层表大致有 3 类 WRM$表存储 AWR 的元数据（Workload Repository
Metadata），WRH$表存储采样快照的历史数据（Workload Repository Historical），WRI$表存储
同数据库建议功能相关的数据。Oracle 10g 中相关表的数量大致如下：
SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> select substr(table_name,1,4),count(*) from dba_tables
2 where table_name like 'WR%' group by substr(table_name,1,4);
SUBSTR(T COUNT(*)
-------- ----------
WRM$ 5
WRH$ 94
WRI$ 61
从 Oracle 11g 开始，这个家族又增加了新的成员，WRR$类表代表的是 Oracle 11g 新功能
Workload Capture 以及 Workload Replay 相关信息：
SQL> select substr(table_name,1,4),count(*) from dba_tables
2 where table_name like 'WR%' group by substr(table_name,1,4);
SUBSTR(T COUNT(*)
-------- ----------
WRM$ 8
WRR$ 9
WRH$ 113
WRI$ 84
SQL> select table_name from dba_tables where table_name like 'WRR%';
TABLE_NAME
------------------------------
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·30·
WRR$_REPLAY_STATS
WRR$_REPLAY_SEQ_DATA
WRR$_REPLAY_SCN_ORDER
WRR$_REPLAY_DIVERGENCE
WRR$_REPLAYS
WRR$_FILTERS
WRR$_CONNECTION_MAP
WRR$_CAPTURE_STATS
WRR$_CAPTURES
9 rows selected.
AWR 的历史数据表主要通过分区表进行存储，这些分区表信息可以通过
DBA_TAB_PARTITIONS 视图进行查询：
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions
2 where table_name like 'WR%' and rownum <5;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -----------------
WRH$_EVENT_HISTOGRAM WRH$_EVENT__1478080230_347 SYSAUX
WRH$_SYSTEM_EVENT WRH$_SYSTEM_1478080230_251 SYSAUX
WRH$_SQLSTAT WRH$_SQLSTA_1478080230_251 SYSAUX
WRH$_FILESTATXS WRH$_FILEST_1478080230_251 SYSAUX
3．AWR 报告展现
AWR 记录的数据可以通过报告来展现，报告可以通过运行脚本生成类似 Statspack report
的 AWR 报告，生成报告的脚本位于$ORACLE_HOME/rdbms/admin/awrrpt.sql，报表可以通过
两种形式输出：TEXT 和 HTML。用脚本生成 AWR 报告的过程与生成 Statspack 报告非常类
似，需要以 sys 用户执行这个脚本，执行过程需要选择报表类型、天数（用来决定显示那几天
内的 snapshot）、begin_snap、end_snap 以及报表名称等 5 个参数。
以下是一个 HTML 格式报表的展现示例，如图 9-8 所示。
第 1 章 章名章名章名章名章名
·31·
图 9-8 一个 HTML 格式报表
值得注意的是，从 Oracle 11g 开始，AWR 报告中增加了很多和操作系统相关的信息，这
些信息此前无法通过报告获取。新增的内容包括主机 CPU 和内存信息：
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
wapdb.eygle.com Linux IA (32-bit) 2 1 1 1.98
负载概要信息增加了 CPU 信息：
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.0 0.1 0.01 0.00
DB CPU(s): 0.0 0.1 0.00 0.00
Redo size: 486.7 6,879.0
……
W/A MB processed: 283,089.4 4,001,459.7
以及 CPU 负载信息、实例 CPU 耗用以及内存使用等信息：
Host CPU (CPUs: 2 Cores: 1 Sockets: 1)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.12 0.06 0.4 0.4 1.6 99.3
Instance CPU
~~~~~~~~~~~~
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·32·
% of total CPU for Instance: 0.4
% of busy CPU for Instance: 59.6
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 2,027.1 2,027.1
SGA use (MB): 600.0 600.0
PGA use (MB): 282.1 283.0
% Host Mem used for SGA+PGA: 43.52 43.52
4．AWR 比较报告诊断案例
值得一ᨀ的是 AWR 报告还有另外一种形式的展现，那就是 AWR 比较报告。通常生成
AWR 报告的脚本是 awrrpt.sql，而比较报告可以通过 awrddrpt.sql 生成（这个脚本通过调用
awrddrpi.sql 脚本生成报告）。这个脚本生成报告的过程与 awrrpt.sql 有所不同。
运行这个脚本，可以选择以 HTML 格式生成报告：
SQL> @?/rdbms/admin/awrddrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
接下来列出数据库的 DBID 等信息，以下输出来自一个双机热备环境，同一数据库在不同
阶段可能运行在不同主机，以下列出两条记录，接下来定义了报告数据库的 DBID 和实例号：
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1992983269 1 GHCCDB ghccdb ccdb1
* 1992983269 1 GHCCDB ghccdb ccdb2
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1992983269 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
接下来选择列出采样的时间，缺省列出全部：
第 1 章 章名章名章名章名章名
·33·
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ghccdb GHCCDB 5275 13 Jul 2008 12:00 1
5276 13 Jul 2008 13:00 1
5277 13 Jul 2008 14:00 1
5278 13 Jul 2008 15:00 1
注意接下来ᨀ示与以往的不同，这里ᨀ示定义第一对起始和结束的快照 ID，这里选择问
题时段的 5276～5277 时段：
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5276
First Begin Snapshot Id specified: 5276
Enter value for end_snap: 5277
First End Snapshot Id specified: 5277
接下来是和之前类似的过程，再次列出实例信息：
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1992983269 1 GHCCDB ghccdb ccdb1
* 1992983269 1 GHCCDB ghccdb ccdb2
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1992983269 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
接下来同样列举采样数据：
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·34·
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days2:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ghccdb GHCCDB 5275 13 Jul 2008 12:00 1
5276 13 Jul 2008 13:00 1
5277 13 Jul 2008 14:00 1
5278 13 Jul 2008 15:00 1
这里定义与之前不同的采样时段，选择 5277～5278 时段：
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 5277
Second Begin Snapshot Id specified: 5277
Enter value for end_snap2: 5278
Second End Snapshot Id specified: 5278
最后定义输出报告名称，缺省的以 awrdiff 开头，也就是 AWR 报告对比之意：
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_5276_1_5277.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrdiff_1_5276_1_5277.html
现在看看这个生成的报告与普通报告的不同，首先第一部分题目显示“WORKLOAD
REPOSITORY COMPARE PERIOD REPORT”，表示这是一个不同阶段的比较报告，第一个报
告以及第二个报告的相关信息会对比列出，便于比较，如图 9-9 所示。
第 1 章 章名章名章名章名章名
·35·
图 9-9 AWR 对比报告
负载概要信息部分通过对比各类统计数据，可以直观地告诉我们不同时段数据库性能的
变化，这是一个真实的诊断案例，注意逻辑读（Logical Reads）部分，第二个时段比第一个时
段多出 194.24%，是第一个时段的近 3 倍，如果不是业务量的正常增长，那么就极有可能是系
统出现异常，如图 9-10 所示。
图 9-10 负载概要信息
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·36·
在这个案例中，继续检查 Buffer Gets Top 10 SQL，注意前两条 SQL，如图 9-11 所示，第
一条 SQL 在第一个时段执行了 37 次，但是在第二个时段却执行了 3168 次；第二条 SQL 在第
一个时段执行了 27 次，在第二个时段执行了 2928 次。这两个 Buffer Gets 在 2 万左右的 SQL
执行数量的激增导致了系统负荷急剧攀升。
图 9-11 检查 Buffer Gets Top 10 SQL
回过头来看 Top 5 Time Events，如图 9-12 所示，在问题时段的 Latch 竞争极高，其中 latch:
cache buffers chains 正是由于过量的 Buffer 扫᧿导致的，综合考虑，前面两个 SQL 的频繁执行
正是性能问题的罪魁祸首，剩下的工作就很简单了，找到两个 SQL 频繁执行的原因，消除应
用异常，系统即可恢复正常。
图 9-12 查看 Top 5 Time Events
创建比较报告也可以通过 Database Control 来进行，在“主页-性能-其他监视链接”选择
“快照”选项即可进入 AWR 数据页，在该页面选择“比较时段”后，即可开始创建不同时段
的采样比较报告，如图 9-13 所示。
第 1 章 章名章名章名章名章名
·37·
图 9-13 定义快照
在完成两个阶段的起始与结束快照定义之后即可确认完成，进行报告创建，如图 9-14 所
示。
图 9-14 确认比较时段
为了能够通过比较机制对数据库不同阶段的性能情况进行比较，可以为 AWR 创建基线
（Base Line），创建的基线不会被清除，以后生成的采样数据或者优化后采样可以同保留的基
线进行对比，以确定数据库的性能变化。创建 Base Line 可以通过 Database Control 进行，也可
以通过命令完成，在内部都是通过 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE
来完成 Base Line 的创建：
PROCEDURE CREATE_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_SNAP_ID NUMBER IN
END_SNAP_ID NUMBER IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·38·
创建的 Base Line 可以通过数据字典视图 dba_hist_baseline 查询。类似以前的 Statspack，
Oracle 允许将 AWR 数据导出并迁移到其他数据库以便于以后分析。Oracle 10gR2 ᨀ供了新工
具来完成导出和迁移 AWR 数据的工作。
DBMS_SWRF_INTERNAL.AWR_EXTRACT 可以用来导出数据，awrextr.sql 脚本就是用
来完成这个工作的，而导入工作可以通过 DBMS_SWRF_INTERNAL 包中的 AWR_LOAD 和
MOVE_TO_AWR 过程来完成，awrload.sql 脚本用于完成这个工作。
5．RAC 环境 AWR 信息的对比展现
在 Oracle Database 11g 中，Oracle 引入了一个新的脚本工具 spawrrac.sql 用于收集 RAC 环
境下的数据库对比信息，在某些情况下可以清晰的展现 RAC 环境中的一些问题，这个脚本同
样可以用于 Oracle Database 10g，在 10.2.0.4 中使用一切正常，其他版本请测试后使用。
该脚本的文件说明信息如下(spawrrac 意即 Server Performance AWR RAC report)：
Rem $Header: spawrrac.sql 23-apr-2007.11:13:39 cgervasi Exp $
Rem
Rem spawrrac.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem spawrrac.sql - Server Performance AWR RAC report
Rem
Rem DESCRIPTION
Rem This scripts generates a global AWR report to report
Rem performance statistics on all nodes of a cluster.
Rem
Rem NOTES
Rem Usually run as SYSDBA
运行和使用 awrrpt.sql 脚本类似：
SQL> @?/rdbms/admin/spawrrac.sql
Instances in this AWR schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Instance
DB Id DB Name Count
----------- ------------ --------
4266683088 SMSDB 2
Enter value for dbid: 4266683088
第 1 章 章名章名章名章名章名
·39·
Using 4266683088 for database Id
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 31 days of Completed Snapshots
Snap Instance
DB Name Snap Id End Interval Time Level Count
------------ --------- ----------------- ----- --------
.....
SMSDB 9566 02 Feb 2009 00:00 1 2
9567 02 Feb 2009 01:00 1 2
9568 02 Feb 2009 02:00 1 2
9569 02 Feb 2009 03:00 1 2
9570 02 Feb 2009 04:00 1 2
9571 02 Feb 2009 05:00 1 2
9572 02 Feb 2009 06:00 1 2
9573 02 Feb 2009 07:00 1 2
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 9572
Begin Snapshot Id specified: 9572
Enter value for end_snap: 9573
End Snapshot Id specified: 9573
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is spawrrac_9572_9573. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·40·
Using the report name spawrrac_9572_9573
生成的报告中，对于 OS 系统信息以及 RAC 信息具有清晰的对比展现，可以很容易发现
RAC 环境中的异常及性能问题，以下对前面生成的报告输出做简要说明。
首先的 OS 统计信息中我们就可以发现，两个节点主机的繁忙程度严重不同，实例 2 非常
繁忙，这说明两个节点负载不均衡，也可能节点 2 上有定时的任务执行：
进一步的在 Global Cache 的信息中，实例 2 请求了大量的跨实例访问的数据块，这进一步
说明了实例 2 上存在大规模的查询或任务操作：
在后面的 SQL 展现中，我们发现如下一条 SQL 是导致大量 CPU 使用以及逻辑读的 SQL，
这是一个物化视图的刷新引起的：
6. AWR 使用信息报告
Oracle 还随软件ᨀ供一个脚本用于输出 AWR 的使用信息，这个脚本是 awrinfo.sql，运行
这个脚本，将会输出 AWR 的空间使用、快照采样、ASH 及 ADDM 等 AWR 元数据信息。输
出显示为 3 类：
l AWR Snapshot Info Gathering；
l Advisor Framework Diagnostics；
l AWR and ASH Usage Info Gathering。
下面是运行这个脚本的输出的摘要示例：
SQL> @?/rdbms/admin/awrinfo.sql
第 1 章 章名章名章名章名章名
·41·
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ------------------------- ---- ---------------- ------------ ---
* 2590148133 EYGLE eygle - Linux IA (32-bit) 1 19:55:16 (06/05) 1585852 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 186.6 MB ( 1% of 32,769.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 112.4 MB ( 60.3% )
| Schema SYSMAN occupies 59.6 MB ( 31.4% )
| Schema WMSYS occupies 6.9 MB ( 3.7% )
| Schema SYSTEM occupies 6.8 MB ( 3.7% )
| Schema DBSNMP occupies 1.6 MB ( 0.8% )
| Schema TSMSYS occupies 0.3 MB ( 0.1% )
|
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- -------------------------------------------------- ---------------
FIXED 2.0 WRH$_SYSMETRIC_SUMMARY - 85% TABLE
FIXED 0.9 WRH$_SYSMETRIC_SUMMARY_INDEX - 90% INDEX
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_266 - 83% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_97 - 83% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_121 - 83% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_242 - 84% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_145 - 83% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_169 - 87% TABLE PARTITION
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_194 - 83% TABLE PARTITION
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·42·
FIXED 0.6 WRH$_LATCH.WRH$_LATCH_2590148133_218 - 83% TABLE PARTITION
SQL 0.6 WRH$_SQLSTAT.WRH$_SQLSTA_2590148133_169 - 84% TABLE PARTITION
SQL 0.5 WRH$_SQLSTAT.WRH$_SQLSTA_2590148133_97 - 87% TABLE PARTITION
SQL 0.5 WRH$_SQLSTAT.WRH$_SQLSTA_2590148133_266 - 85% TABLE PARTITION
9.3.4 自动数据库诊断监控：ADDM 的引入
有了这个 AWR 这个“数据仓库”之后，Oracle 自然可以在此基础之上实现更高级别的智
能应用，更大程度地发挥 AWR 的作用，这就是 Oracle 10g 引入的另外一个功能自动数据库诊
断监控程序（Automatic Database Diagnostic Monitor，ADDM），通过 ADDM，Oracle 试图使
数据库的维护、管理和优化工作变得更加自动和简单。
ADDM 可以定期检查数据库的状态，根据内建的专家系统，自动确定潜在的数据库性能
瓶颈，并ᨀ供调整措施和建议。由于这一切都是内建在 Oracle 数据库系统之内的，其执行效
率很高，几乎不影响数据库的总体性能。
新版的 Database Control 可以以一种方便直观的形式ᨀ供 ADDM 的结果和建议，并引导
管理员逐步实施 ADDM 的建议，快速解决性能问题。
通过图 9-15 可以直观地看到 AWR 及 ADDM 的关系。
图 9-15 AWR 及 ADDM 的关系
对于 ADDM，本章不打算做过多的详细介绍。
第 1 章 章名章名章名章名章名
·43·
9.4 顶级等待事件
前文还ᨀ到另外一个重要视图 V$SYSTEM_EVENT，该视图记录的是数据库自启动以来
等待事件的汇总。通过查询该视图，就可以快速获得数据库等待事件的总体概况，了解数据库
运行的基本状态：
SQL> SELECT *
2 FROM (SELECT event, time_waited
3 FROM v$system_event
4 ORDER BY time_waited DESC)
5 WHERE ROWNUM < 11;
EVENT TIME_WAITED
---------------------------------------------------------------- -----------
SQL*Net message from client 9.2256E+10
rdbms ipc message 1.2383E+10
pmon timer 1834857492
smon timer 1771582338
jobq slave wait 414242315
db file scattered read 54344796
enqueue 29142826
latch free 18022667
db file sequential read 11925101
log file sync 9500670
10 rows selected.
以上是一个产品环境中的 Top10 等待事件，我们注意到 Top5 等待都是空闲等待，所以不
必过多关注，但是接下来的 5 个等待事件都是常见的重要等待事件，如果能够进行针对性优化，
数据库性能将会得到大幅ᨀ升。
在 Oracle 的 Statspack Report 中，有一部份信息为 Top 5 Wait Events（在 Oracle 9i 中更改
为 Top 5 Time Events），这部分信息就是来自 V$SYSTEM_EVENT 视图的采样。
以下是一个 Statspack 的诊断报告：
DB Name DB Id Instance Inst Num Release OPS Host
---------- ----------- ---------- -------- ---------- ---- ----------
K2 1999167370 k2 1 9.1.5.0.0 NO k2
这是一个 9.1.5 的数据库系统，通过脚本增强，可以在 9.1.5 的数据库上使用 Statspack 来
进行数据库诊断。
Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
170 176 25-Feb-03 10:00:11 25-Feb-03 15:00:05 299.90
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·44·
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 64000
db_block_size: 8192
log_buffer: 8388608
shared_pool_size: 157286400
……
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ----------------------- -------
db file scattered read 16,842,920 3,490,719 43.32
latch free 844,272 3,270,073 40.58
buffer busy waits 114,421 933,136 11.58
db file sequential read 2,067,910 117,750 1.46
enqueue 464 110,840 1.38
-------------------------------------------------------------
这里的 Top 5 Wait Events 是诊断的重要依据。这是一个典型的性能低下的系统，几个重
要的等待事件都在 Top 5 中出现，其中，前 3 个等待极为显著，需要进行相应的调整。
在 5 小时的采样间隔内，其中 db file scattered read 累计等待时间约 10 小时，已经成为影
响系统性能的主要原因。了解了这些以后就可以进一步查看 Statspack Report 中相关 SQL 部分，
看是否存在可疑的 SQL 语句。
SQL ordered by Gets for DB: K2 Instance: k2 Snaps: 170 - 176
Gets % of
Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
------------------------------------------------------------------------------
6,480,163 12 540,013.6 2.4 3791855498
SELECT "PROCESS_REQ"."WORK_ID", "PROCESS_REQ"."STOCK_NO", "PROCESS_R
3,784,566 16 236,535.4 1.4 2932917818
SELECT * FROM FIND_LATER_WO ORDER BY NOTE,ORDER_NO
1,200,976 3 400,325.3 .4 4122791109
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
923,944 9 102,660.4 .3 2200071737
SELECT "ITEM_STOCK"."ITEM_NO" , "ITEM_STOCK"."STOCK_NO" ,
921,301 3 307,100.3 .3 2218843294
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
911,285 3 303,761.7 .3 1769130587
第 1 章 章名章名章名章名章名
·45·
SELECT "LISTS"."ITEM_NO" , "LISTS"."SUB_ITEM" , "LISTS"
831,439 2 415,719.5 .3 1349577999
SELECT "GROUP_OPER"."ITEM_NO" , "GROUP_OPER"."PROCESS_ID" ,
802,918 1 802,919.0 .3 3613809507
SELECT "LISTS"."ITEM_NO" , "LISTS"."SUB_ITEM" , "ITEM".
800,548 2 400,274.0 .3 2643788247
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
666,085 2 333,042.5 .2 3391363608
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM_STOCK"."STOCK_NO",
…….
注意到以上很多查询导致的 Buffer Gets 都非常庞大，我们非常有理由怀疑索引存在问题，
甚至缺少必要的索引。以上记录的是 SQL 的片段，通过 Hash Value 值结合 v$sql_text 可以获
得完整的 SQL 语句（可以参考前文的案例）。
在这次诊断中，我紧接着去查询的是 v$session_longops 视图，一个分组查询的结果如下：
TARGET COUNT(*)
---------------------------------------------------------------- ----------
SA.PPBT_GRAPHOBJTABLE 418
SA.PPBT_PPBTOBJRELATTABLE 53
发现这些问题 SQL 的全表扫᧿（结合 v$session_longops 视图中的 OPNAME）主要集中在
PPBT_GRAPHOBJTABLE 和 PPBT_PPBTOBJRELATTABLE 两张数据表上。进一步研究发现
这两个数据表上没有任何索引，并且有相当的数据量：
SQL> select count(*) from SA.PPBT_PPBTOBJRELATTABLE;
COUNT(*)
----------
1209017
SQL> select count(*) from SA.PPBT_GRAPHOBJTABLE;
COUNT(*)
----------
2445
在创建了合适的索引后，系统性能得到了大幅ᨀ高!
9.5 重要等待事件
在了解了等待事件的作用和变迁之后，让我们来了解一下重要的等待事件。
9.5.1 db file sequential read-数据文件顺序读取
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·46·
db file sequential read 是个非常常见的 I/O 相关的等待事件，通常显示与单个数据块相
关的读取操作，在大多数的情况下，读取一个索引块或者通过索引读取一个数据块时，
都会记录这个等待。
这个等待事件有 3 个参数 P1、P2、P3，其中 P1 代表 Oracle 要读取的文件的绝对文
件号，P2 代表 Oracle 从这个文件中开始读取的起始数据块块号，P3 代表读取的 BLOCK
数量，通常这个值为 1，表明是单个 BLOCK 被读取。
SQL> select name,parameter1,parameter2,parameter3
2 from v$event_name where name='db file sequential read';
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ---------- ---------- ----------
db file sequential read file# block# blocks
在 Oracle 10g 中，这个等待事件被归入 User I/O 一类：
SQL> select name,wait_class
2 from v$event_name where name='db file sequential read';
NAME WAIT_CLASS
------------------------------ -------------------------
db file sequential read User I/O
图 9-16 简要说明了单块读取的操作方式。
图 9-16 单块读取的操作
如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，没有正
确地使用驱动表；或者可能索引的使用存在问题，并非索引总是最好的选择。在大多数情况下，
第 1 章 章名章名章名章名章名
·47·
通过索引可以更为快速地获取记录，所以对于一个编码规范、调整良好的数据库，这个等待事
件很大通常是正常的。有时候这个等待过高和存储分布不连续、连续数据块中部分被缓存有关，
特别对于 DML 频繁的数据表，数据以及存储空间的不连续可能导致过量的单块读，定期的数
据整理和空间回收有时候是必须的。
需要注意在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，
全表扫᧿可能会明显快于索引扫᧿，所以在开发中就应该注意，对于这样的查询应该进行避免
使用索引扫᧿。
从 Oracle 9iR2 开始，Oracle 引入了段级统计信息收集的新特性，可以通过查询
V$SEGMENT_STATISTICS 视图，找出物理读取显著的索引段或者是表段，研究其数据结构，
看能否通过重建或者重新规划分区、存储参数等手段降低其 I/O 访问。
Oracle 9iR2 中，收集的统计信息共有 11 类：
SQL> select * from v$segstat_name;
STATISTIC# NAME SAMPLED
---------- ---------------------------------------- ----------
0 logical reads YES
1 buffer busy waits NO
2 db block changes YES
3 physical reads NO
4 physical writes NO
5 physical reads direct NO
6 physical writes direct NO
8 global cache cr blocks served NO
9 global cache current blocks served NO
10 ITL waits NO
11 row lock waits NO
11 rows selected.
在 Oracle 10gR2 中，这类统计信息增加为 15 个：
SQL> select * from v$segstat_name;
STATISTIC# NAME SAM
---------- ------------------------------ ---
0 logical reads YES
1 buffer busy waits NO
2 gc buffer busy NO
3 db block changes YES
4 physical reads NO
5 physical writes NO
6 physical reads direct NO
7 physical writes direct NO
9 gc cr blocks received NO
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·48·
10 gc current blocks received NO
11 ITL waits NO
12 row lock waits NO
14 space used NO
15 space allocated NO
17 segment scans NO
15 rows selected.
对于 CBO 模式下的数据库，应当及时收集统计信息，使 SQL 可以选择正确的执行计划，
避免因为统计信息陈旧而导致的执行错误等。
9.5.2 db file scattered read 等待事件
在前面的案例中，已经多次见到 db file scattered read 等待事件，在生产环境之中，这个等
待事件可能更为常见。这个事件表明用户进程正在读数据到 Buffer Cache 中，等待直到物理 I/O
调用返回。DB File Scattered Read 发出离散读，将存储上连续的数据块离散的读入到多个不连
续的内存位置。Scattered Read 通常是多块读，在 Full Table Scan 或 Fast Full Scan 等访问方式
下使用。
Scattered Read 代表 Full Scan，当执行 Full Scan 读取数据到 Buffer Cache 时，通常连续的
数据在内存中的存储位置并不连续，所以这个等待被命名为 Scattered Read（离散读）。每次多
块读读取的数据块数量受初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT 限制。图 9-17
简要说明了 Scattered Read 的数据读取方式。
图 9-17 Scattered Read 的数据读取
从 V$EVENT_NAME 视图可以看到，该等待有 3 个参数，分别代表文件号、起始数据块
号、数据块的数量：
第 1 章 章名章名章名章名章名
·49·
SQL> select * from v$event_name where name='db file scattered read';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ---------------------- ---------------------- ------------- ----
206 db file scattered read file# block# blocks
数据文件号、起始数据块号加上数据块的数量，通过这些信息可以知道 Oracle Session 正
在等待的对象文件等信息。该等待可能和全表扫᧿（Full Table Scan）或者快速全索引扫᧿
（Index Fast Full Scan）的连续读取相关，根据经验，通常大量的 db file scattered read 等待可能
意味着应用问题或者索引缺失。
在实际环境的诊断过程中，可以通过 v$session_wait 视图发现 Session 的等待，再结合其
他视图找到存在问题的 SQL 等根本原因，从而从根本上解决问题。此类诊断案例，可以参考
9.2 小节的内容。
当这个等待事件比较显著时，用户也可以结合 v$session_longops 动态性能视图来进行诊
断，该视图中记录了长时间（运行时间超过 6 秒的）运行的事务，可能很多是全表扫᧿操作（不
管怎样，这部分信息都是值得我们注意的），上一个案例就是通过 v$session_longops 快速发现
了问题所在。
从 Oracle 9i 开始，Oracle 新增加了一个视图 V$SQL_PLAN 用于记录当前系统 Library
Cache 中 SQL 语句的执行计划，可以通过这个视图找到存在问题的 SQL 语句，以下是在一个
生产系统中查询得到的结果：
SQL> @getplan
Enter value for waitevent: free buffer waits
old 15: AND b.event = '&waitevent')
new 15: AND b.event = 'free buffer waits')
HASH_VALUE CHILD_NUMBER OPERATION OBJECT COST KBYTES
---------- ------------ ---------------------------------------- -------------------
2838180055 0 INSERT STATEMENT CHOOSE Cost=41733 41733
2838180055 0 TABLE ACCESS FULL I_CM_POWER_TEMP 41733 1356468
进而可以通过 v$sql_text 视图获得这个问题 Session 正在执行的 SQL 语句：
SQL> select sid,event from v$session_wait;
SID EVENT
---------- ----------------------------------------------------------------
1 pmon timer
4 rdbms ipc message
7 rdbms ipc message
5 rdbms ipc message
8 rdbms ipc message
21 free buffer waits
49 free buffer waits
2 db file parallel write
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·50·
3 db file parallel write
6 smon timer
……
16 rows selected.
SQL>@ GetSqlBySid
Enter value for sid: 49
old 5: where b.sid='&sid'
new 5: where b.sid='49'
SQL_TEXT
-----------------------------------------------------------------------
insert into i_cm_power_new(PNAME,YYS,SPHM,SJH,SENTTIME,NOTES,PLACE,RMK)
select PNAME,YYS,SPHM,SJH,SENTTIME,NOTES,PLACE,RMK FROM i_cm_power_temp
通过 V$SQL_PLAN 视图，可以获得大量有用的信息，比如获得全表扫᧿的对象：
SQL> select distinct object_name,object_owner from v$sql_plan p
2 where p.operation='TABLE ACCESS' and p.options='FULL'
3 and object_owner = 'MKT';
OBJECT_NAME OBJECT_OWNER
--------------------------------------------------- -----------------
HD_TEMP MKT
I_CM_BILL MKT
I_CM_IVR_BUTTON MKT
……
TOOLS_HD MKT
TOOLS_HD_NEW MKT
TOOLS_HD_NEW_BAK MKT
TOOLS_IVRBLIST MKT
TOOLS_USER_CANCEL MKT
29 rows selected
或者获得全索引扫᧿对象：
SQL> select distinct object_name,object_owner from v$sql_plan p
2 where p.operation='INDEX' and p.options='FULL SCAN' ;
OBJECT_NAME OBJECT_OWNER
------------------------------ -----------------------------------
FK_ITEM_LEVEL_CODE AVATAR
FK_ITEM_SELLCNT_CODE AVATAR
FK_MYZZIM_CRTDATE AVATAR
I_SYSAUTH1 SYS
SYS_C008211 WLLM
进而可以通过 V$SQL_PLAN 和 V$SQLTEXT 联合，获得这些查询的 SQL 语句，查找全
第 1 章 章名章名章名章名章名
·51·
表扫᧿的 SQL 语句可以参考如下语句：
SELECT sql_text FROM v$sqltext t, v$sql_plan p
WHERE t.hash_value = p.hash_value AND p.operation = 'TABLE ACCESS' AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;
查找 Fast Full Index 扫᧿的 SQL 语句可以参考如下语句：
SELECT sql_text FROM v$sqltext t, v$sql_plan p
WHERE t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN'
ORDER BY p.hash_value, t.piece;
这些信息对于发现数据库问题，优化数据库性能具有极强的指导意义。本例中用到的 SQL
代码 getplan.sql 内容如下：:
SET linesize 120
COL operation format a55
COL cost format 99999
COL kbytes format 999999
COL object format a25
SELECT hash_value, child_number, LPAD (' ', 2 * DEPTH) || operation || ' ' || options
|| DECODE (ID, 0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST) ) operation,
object_name OBJECT, COST, ROUND (BYTES / 1024) kbytes
FROM v$sql_plan WHERE hash_value IN (
SELECT a.sql_hash_value FROM v$session a, v$session_wait b
WHERE a.SID = b.SID AND b.event = '&waitevent')
ORDER BY hash_value, child_number, ID;
在 Oracle 10g 中，Oracle 对等待事件进行了分类，db file scattered read 事件被归入 User I/O
一类：
SQL> select name,PARAMETER1 p1,PARAMETER2 p2,PARAMETER3 p3,
2 WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS
3 from v$event_name where name='db file scattered read';
NAME P1 P2 P3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------------------------- -------- -------- -------- ------------- ----------- ----------
db file scattered read file# block# blocks 1740759767 8 User I/O
完成对等待事件的分类之后，Oracle 10g 的 ADDM 可以很容易地通过故障树分析定位到
问题所在，帮助用户快速发现数据库的瓶颈及瓶颈的根源，这就是 Oracle 的 ADDM 专家系统
的设计思想。