tuning 第十章（二）

最新推荐文章于 2025-08-31 09:44:03 发布

转载最新推荐文章于 2025-08-31 09:44:03 发布 · 227 阅读

标签

#运维 #数据库 #操作系统

本文详细介绍了Oracle数据库中各类等待事件及其解决方案，包括资源争用、I/O瓶颈、并发控制等问题，帮助理解性能瓶颈。

三、等待事件数据
V$SESSION, V$SESSION_WAIT, V$SESSION_HISTORY,

V$SESSION_EVENT, and V$SYSTEM_EVENT视图提供了哪些资

源在等待的信息，如果timed_statistics设置为true，还能

统计每种资源需要等待多久才能获得。

需要等待事件最多的时间往往能够暗示性能瓶颈所在。

1.buffer busy waits
这个等待事件说明缓冲区中有一部分缓存正在被多个过程调

用。
查询v$waitstat可以看到每类buffer的等待数据。
一般会出现buffer busy waits的buffer包括data block,

segment header, undo header, and undo block.

v$session_wait中P1表示file id，P2表示块id，P3表示类

id（Class ID）

为了找到等待的原因，首先在发生buffer busy waits时查

看v$session的row_wait_obj#列，
然后用查到的row_wait_obj#查询dba_objects中的对象和对

象类型：
SELECT row_wait_obj#
FROM V$SESSION
WHERE EVENT = 'buffer busy waits';

SELECT owner, object_name, subobject_name,

object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;

根据争用块的类型和实际段来决定解决方法。

段头：
如果争用发生在段头，很可能是空闲列表free list的争用

如果可能，将段空间管理的方式设置为自动（ASSM）
如果不能使用ASSM，可以使用下列信息：
空闲列表是包含段中不同区上的空闲数据块列表（A free

list is a list of free data blocks that usually

includes blocks existing in several different

extents within the segment.）空闲列表是空闲空间没有

达到PCTFREE大小或使用空间已经降到PCTUSED以下的块的列

表。用freelists参数确定空闲列表的数量。freelists的默

认值是一部分，最大值决定于数据块的大小。
用以下语句查看当前段的空闲列表设置情况：
SELECT SEGMENT_NAME, FREELISTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = segment name
AND SEGMENT_TYPE = segment type;

设置或增加空闲列表的数量。如果增加不能解决问题，可以

使用空闲列表组（free list groups）。如果是RAC环境，

确保每个实例都有自己的空闲列表组。

数据块：
如果争用发生在表或索引上，而非段头，需要索引类型是否

合适，考虑使用ASSM、全局hash分区索引或增加空闲列表来

避免多线程对相同的块上执行插入操作。

undo header：
对于在回滚段段头上的争用，如果未使用自动撤销管理，可

以增加更多的回滚段。

undo block：
如果未使用自动撤销管理，考虑使用更大的回滚段大小。

2.db file scattered read
这个事件说明用户进程正在将缓存读入到SGA buffer cache

，等待物理i/o的返回。
db file scattered read将数据采用分散读的方式读入多个

不连续的内存空间。分散读通常是多块读，可以是全表扫描

或索引的快速全扫描。
db file scattered read说明发生了全扫描。当将全扫描的

数据插入buffer cache时，读到的块并不是连续被读入内存

区域中的。
每次读都是分散的，因为块在内存中分散。

v$session_wait的P1是绝对文件名、P2是读到的块、P3是块

的数量

在负载很重的系统上，物理读等待应该是空闲等待外最大的

等待。然而，还需要考虑是否有直接读等待（并行查询的全

表扫描）或db file scattered read是否应该通过索引来优

化。
其他可能造成i/o过重的包括：
buffer cache的命中率低
在一个响应时间过长的用户等待时间中，这些等待事件占了

大部分

管理过量的i/o：
通过sql调优减少i/o量
管理负载来减少i/o需要
用dbms_stats包收集系统统计信息，允许查询优化器准确计

算可能的路径
使用自动存储管理
增加磁盘来减少每个磁盘上的i/o
将i/o分散到不同磁盘来减少热区

应该从v$sqlarea中找到消耗i/o量比较大的语句，进行调整

。
可能的原因包括：
不合理的sql
没用到索引
表的高度并行
缺少准确的统计数据
db_file_multiblock_read_count参数设置过高

通过语句查到当前哪些会话在等待i/o：
SELECT SQL_ADDRESS, SQL_HASH_VALUE
FROM V$SESSION
WHERE EVENT LIKE 'db file%read';

从dba_objects中找到对应的对象名：
SELECT owner, object_name, subobject_name,

object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;

3.db file sequential read
说明用户正在将一个buffer读入SGA buffer cache，等待物

理i/o返回。顺序读是单块读。
通常是使用索引的结果，很少情况下由于区的限制或缓存的

作用全表扫描进行单块调用。

v$session_wait中P1指决定文件号、P2指读到的块、P3是块

的数量（应该是1）

在健康的系统上，物理读等待应该是空闲等待以外最大的。

但还需要考虑数据仓库系统上的db file sequential reas

是否看到大量并行的全表扫描

【比较：】
db file sequential read是SGA buffer上的单块读
db file scattered read是多个不连续SGA buffer上的多块

读
direct read是避开了SGA，到PGA的单块或多块读

4.direct path read and direct path read temp
当一个会话从磁盘直接向PGA读入buffers时，等待此事件。

如果i/o子系统未提供异步i/o，每个等待是对一个物理读的

相应。

如果i/o子系统支持异步i/o，就可以读pga中存在的块。
当进程尝试访问PGA中的块还未从磁盘读入，就需要等待并

更新该事件的统计信息。此后就不需要再等待了。

Check the following V$SESSION_WAIT parameter

columns:
■P1: File_id for the read call
■P2: Start block_id for the read call
■P3: Number of blocks in the read call

这种问题会在如下情况下发生
排序太大了，无法插入内存，一些排序数据被直接写入到磁

盘。这些数据随后被采用直接读的方式返回
并行数据查询
服务器进程的buffers过程比i/o块，即 i/o系统过载

file_id显示读操作是对临时表空间（排序）核实并行自造

错的全表扫描。这个等待是大数据量数据仓库的最大等待事

件。但如果负载不是决策支持系统，需要知道为什么出现此

类情况

磁盘的排序：
查看当前会话中等待的语句是否是造成排序的根源。

v$tempseg_usage可以看到生成排序的语句。确认通过调整

sql是否能减少排序操作。如果workarea_size_policy是

manual，考虑增加会话或某个进程的sort_area_size。如果

workarea_size_policy是auto，查看是否

pga_aggregate_target增加了。

全表扫描：
如果表的定义是高度并行，这个设置可能影响优化器，使其

使用并行的全表扫描。
查看使用直接路径读入的对象。如果全表扫描是合理的负载

方式，那么就确保i/o子系统有足够的并行度。
如果没使用oracle ASM，可以考虑使用条带化磁盘

hash area size：
对于为hash join调用的查询计划，过量i/o可能是

hash_area_size太小造成的。
如果workarea_size_policy设置为manual，那么考虑增加增

加系统或单个会话的hash_area_size。
如果workarea_size_policy设置为auto，确认是否需要增加

pga_aggregate_target

5.directpath write and direct path write temp
当会话直接从buffers写入到pga中时，会话等待改时间写完

。能够提高写入的操作包括在磁盘上排序、并行dml、

direct-path inserts、并行创建表和log操作。
类似于直接路径读，如果i/o子系统支持异步写入，写的数

量与写调用的数量不同。如果会话占用了pga的所有buffers

，就需要等待，直到i/o请求完成才可以继续工作。

Check the following V$SESSION_WAIT parameter

columns:
■P1: File_id for the write call
■P2: Start block_id for the write call
■P3: Number of blocks in the write call

此问题发生在：
排序操作太大，无法在内存完成，被写入磁盘
并行dml操作创建或定位对象
direct path loads

出现此问题的解决方法为：
对于大量排序，使其在磁盘上排序；
对于并行dml，检查i/o分配，确保i/o子系统为当前并行度

配置了充足i/o资源。

6.enqueue（enq：）waits
队列被锁定来协调数据库资源。此事件说明会话在等待其他

会话的锁。

enqueue的名字中包括了等待事件的名字（enq：

enqueue_type-related_datails）
某些情况下相同enqueue type可能用于不同目的，比如：
■enq: TX - allocate ITL entry
■enq: TX - contention
■enq: TX - index contention
■enq: TX - row lock contention

v$event_name视图中包括了所有enq：的完整列表（等待事

件）。

相关信息在v$session_wait：
■P1: Lock TYPE (or name) and MODE
■P2: Resource identifier ID1 for the lock
■P3: Resource identifier ID2 for the lock

7.finding locks and lock holder
查看v$lock找到锁的所有者和受影响会话。
每个等待队列中的会话在v$lock中都对应一条request<>0的

列。使用以下两个查询之一找出锁的拥有者和等待者：
SELECT * FROM V$LOCK WHERE request > 0;

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type

FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;

解决方法根据队列类型不同而有所不同

ST enqueue：
问题很可能在于动态空间分配上。如果一个段的空间不足，

oracle动态为其分配一个区。这个enqueue只用于数据字典

管理的表空间。

解决此争用的方法：
检查临时表空间是否在使用临时文件，如果没有，开启临时

文件
如果是数据字典管理的表空间，改为本地管理表空间
如果无法使用本地管理表空间，增大文件下一个extent的大

小，避免空间的不断分配，从而减少ST enqueue。
（通过dba_segments中extents的监控可以看到那些段需要

增长。）
为段预分配空间，比如alter table allocate extent语句

HW enqueue：
在段的高水位线下连续分配空间

TM enqueue：
TM锁等待一般是由于外键列上没有创建索引造成的。创建索

引可以解决。

TX enqueue：
当事务初始化其更改时被独占，直到事务提交或回滚。

等待级别6：一个会话等待其他会话释放行级锁。发生在某

个用户更改或删除了一行，而另一个会话想要更改或删除时

。这种类型的TX enqueue等待返回的等待事件：enq：TX-

row lock contention。解决方法是第一个会话提交或回滚

。

等待级别4：如果一个会话等待ITL（interested

transaction list）放入块。发生在会话想要锁定块中的一

行，但一或多个其他回话在相同的块上有行级锁，块上没有

空闲的ITL槽。通常来说oracle动态增加ITL槽，如果没有足

够的空闲空间是不可能增加ITL的。如果出现这种情况，会

话等待一个插槽。这种类型的TX enqueue对应等待事件为

enq：TX-allocate ITL entry
这种情况的解决是增加可用的ITL数量，通过增加表的

INITRANS或MAXTRANS（使用alter语句或重建表）

等待级别4也可能是由于共享位图索引片断。位图索引索引

键值和一系列的rowid。每个位图索引覆盖表中的多行，如

果连个会话想要更改被同一个位图索引片断覆盖的行，第二

个会话就需要等待第一个会话结束事务。这种类型的TX

enqueue等待对应的事件是enq:TX-row lock contention

8.其他类型的等待事件
这种事务属于其他等待类，通常不应该发生在系统中。
此事务是未分类的总称，比如latch free 。只在

v$session_event、v$service_event视图中使用，这些事务

被汇总到这个事件中，减少用于管理统计信息的内存消耗。

9.free buffer waits
说明服务器进程找不到空闲列表，通过写入脏数据来腾出

buffers。脏buffer指内容被修改了的buffer，当DBWR将块

写入磁盘时，被释放。

DBWR在以下情况下不会将脏buffer写入：
i/o系统缓慢
存在DBWR等待的资源，比如闩
buffer cache太小，DBWR需要花费很长时间为服务器进程清

理buffers
buffer cache太大，一个DBWR进程不能腾出缓冲区中足够的

buffers来满足要求

发生这种情况，应该检查DBWR会话等待，看DBWR是否有延迟

。

如果有等待，判断是什么导致的，并解决：
查看v$filestat，大多数写操作发生在哪儿
查看操作系统的i/o统计信息，写的时间是否可接受？

如果i/o太慢：
使用更快的i/o加快写的速度
通过更多的磁盘和控制器来增加i/o速度

缓存太小：
可能是由于缓存太小，DBWR显得太活跃了。
通过查看buffer cache命中率是否过低来判断。也可以使用

v$db_cache_advice判断大的缓存大小是否能提高性能。

缓存对于一个DBWR来说太小了：
如果缓存大熊啊充足，i/o消耗平均，可以使用异步i/o或多

个数据库写进程来改善DBWR

考虑多个数据写进程与i/o异步：
当事务率很高或者buffer cache大小太大，一个DBWN进程不

能承受时可以考虑。

db_writer_processes初始化参数用于配置数据写进程。配

置多个DBWR进程分别写buffers，还可以分担这些进程的负

载。
对于八核或以上CPU的系统或者多个处理器组的系统推荐使

用多个DBWR进程。
基于cpu颗数或进程组数，既可以为db_writer_processes选

择默认的设置，也可以调整。

dbwr_io_slaves：如果多个DBWR进程不能实施，oracle还提

供了一个设备，可以将i/o负载分发到多个从属进程上。

DBWR是唯一扫描buffer cache LRU列表的进程，而这些块的

i/o被i/o slaves承担。i/o slaves的数量决定于

dbwr_io_slaves。
dbwr_io_slaves在多DBWR不能使用时（比如只有一个CPU）

。i/o slaves在异步i/o不可用时也可以使用，因为i/o

slaves模拟是不闭塞的。异步i/o是操作系统级别的，通常

来说是有好处的。

DBWR i/o slaves在第一次i/o请求时立即分配。DBWR继续进

程相关的工作，不再考虑i/o。i/o slaves只是代表DBWR进

行i/o操作，在i/o slaves之间的写入是并行的。
DBWR_ID_SLAVES需要为i/o buffers和请求队列分配额外的

共享内存。多个DBWR进程不能和i/o slaves共用。

两者的选择：一个dbwr无法满足负载要求时使用多个dbwr，

而在配置多个dbwr进程之前需要先检查异步i/o是否可用并

在系统中配置了。
如果系统支持异步i/o但当前没用，开启异步i/o并观察问题

是否得到缓解；
如果系统不支持异步i/o或者异步i/o配置不能解决dbwr的瓶

颈，就配置多个dbwr进程。

使用多个dbwr并行收集和写buffer，多个dbwn进程可以分配

更多的i/o slaves。因此i/o slaves没有多dbwr好。i/o

slaves只有在无法配置dbwr的情况下才配置。

11.idle wait events空闲等待事件
属于空闲等待类，表示服务器进程由于没有负载而等待。通

常来说是没有瓶颈的。
调优时大部分的空闲时间可以忽略，因为它们与瓶颈无关。

12.latch event闩事件
闩是低级别的锁，在oracle中用于保护内存结构。当服务器

进程尝试添加闩时将更改状态。
进程发生的闩，名字中都有其原因相关的部分。比如latch

：library cache或latch：cache buffers chains。

在闩等待占到了等待时间的多数或者对于单个用户问题时才

需要关注。
查看相关资源的使用情况，比如，如果数据字典缓存争用严

重，就需要检查软解析和硬解析的情况
检查发生闩争用的会话中sql语句是否有共同点。
Check the following V$SESSION_WAIT parameter

columns:
■P1: Address of the latch
■P2: Latch number
■P3: Number of times process has slept, waiting for

the latch

256页表格总结了闩等待事件及对应的解决方案。

共享池和librarycache闩争用：
通常来说是由于解析问题。

（1）未共享的sql
手工查看执行次数少于四次的语句，是否相似（只有值不同

）
或者将此过程自动化，评估一个语句需要花费的字节数并据

此分组。

SELECT SQL_TEXT
FROM V$SQLSTATS
WHERE EXECUTIONS < 4
ORDER BY SQL_TEXT;

SELECT SUBSTR(SQL_TEXT, 1, 60), COUNT(*)
FROM V$SQLSTATS
WHERE EXECUTIONS < 4
GROUP BY SUBSTR(SQL_TEXT, 1, 60)
HAVING COUNT(*) > 1;

也可以查具有相同执行计划的sql语句的distinct值：
SELECT SQL_TEXT FROM V$SQLSTATS WHERE

PLAN_HASH_VALUE IN
(SELECT PLAN_HASH_VALUE
FROM V$SQLSTATS
GROUP BY PLAN_HASH_VALUE HAVING COUNT(*) > 4)
ORDER BY PLAN_HASH_VALUE;

（2）准备共享sql
用以下语句查看v$sqlstats视图：
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLSTATS
ORDER BY PARSE_CALLS;

如果一个语句的parse_calls值与executions数量接近，需

要继续reparsing。将这个语句调到更多数量的parse calls

。

（3）通过会话
通过定位语句发生在哪些会话来定位不必要的parse calls

。
也许是特定的程序或者确定类型的应用做了重复解析。通过

以下语句查询：
SELECT pa.SID, pa.VALUE "Hard Parses", ex.VALUE

"Execute Count"
FROM V$SESSTAT pa, V$SESSTAT ex
WHERE pa.SID = ex.SID
AND pa.STATISTIC#=(SELECT STATISTIC#
FROM V$STATNAME WHERE NAME = 'parse count (hard)')
AND ex.STATISTIC#=(SELECT STATISTIC#
FROM V$STATNAME WHERE NAME = 'execute count')
AND pa.VALUE > 0;

结果是一系列会话以及他们进行的重复解析次数。每个会话

都可以通过SID在v$session中找到程序名字。

以上查询应该注意发生的时间比例。

cache buffers lru chain：
这个闩保护缓存中的buffers列表。从buffers中查找、增删

改时必须获得一个闩。

对于负载均衡系统symmetric multiprocessor system（SMP

），oracle自动将LRU闩的数量设置为cpu个数的一半。对于

non-SMP系统来说，一个LRU闩就足够了。

LRU闩的争用会阻碍多CPU的SMP系统性能。LRU latch争用可

以通过v$latch、v$session_event、v$system_event来查看

，为了避免争用，可以考虑调整应用，DSS系统的jobs避开

buffer cache或重新设计应用

cache buffers chains：
区缓存链用于保护buffer cache中的buffer列表。这些闩在

从buffer cache中查找、增、删buffers时使用。这些闩的

争用通常意味着有热块。

使用v$latch_children查看相关信息。如果一个特定cache

buffers chains的子闩与其他子闩相比有大量的gets、

misses、sleeps，这就是子闩争夺的。

这个闩有内存地址，通过ADDR列定位。使用addr列的值和

X$BH表来定位这个闩所保护的块。

通过高度争用的闩所在地址来查看文件和块：
SELECT OBJ data_object_id, FILE#, DBABLK,CLASS,

STATE, TCH
FROM X$BH
WHERE HLADDR = 'address of latch'
ORDER BY TCH;

X$BH是buffer 的数量，这个值大就意味着热块。

拥有TCH值高的块就是潜在的热块。多运行几次这个查询，

如果某个结果总是出现在查询的结果中，就是热块。通过文

件号和块号查询dba_extents来定位段。

找到热块后，可以通过以下语句找到对应的段：
SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

查询中&obj是OBJ列的值，即上一个查询中的X$BH。

row cache objects闩保护数据字典。

13.log file parallel write
此事件将重做记录从log buffer写入到重做日志文件中。

14.library cache pin
此事件管理着library cache 的并发。对象的pin操作（固

定）引起内存的大量使用。如果一个客户端需要修改或检测

对象，就需要在锁住后将对象固定住。

15.library cache lock
这个事件控制着客户端library cache的并发。在对象上加

锁，使其发生如下之一情况：
一个客户端阻止其他客户端访问相同的对象；
客户端可以长期保持依赖，不允许另外的客户端修改对象。

16.log buffer space
当服务器进程等待log buffer的空闲空间时发生此事件。由

于所有的重做都比LGWR写入要快。

发生此情况时，修改redo log buffer的大小。如果log

buffer的大小合适，确保在线重做日志文件所在的磁盘没有

i/o争用。
log buffer space等待事件是由于重做日志文件所在磁盘上

的i/o争用或者log buffer太小造成的。查看磁盘上包含重

做日志文件的i/o profile确认是否存在i/o瓶颈。如果i/o

没问题，就是重做日志文件缓存太小的问题。增加其大小知

道这个事件不再发生。

17.log file switch
包括有两个等待事件：
■log file switch (archiving needed)
■log file switch (checkpoint incomplete)

两个事件中，LGWR都不能切换到下一个重做日志文件上。所

有提交操作需要等待该事件。

对于log file switch（archiving needed）事件，确认为

什么归档不能够将日志按时归档。可能原因为：
归档目的地没有足够空间；
归档操作无法足够快的读取redo logs（和LGWR存在争用）

；
归档操作无法足够快的写入（在归档目的地存在争用或者

ARCH进程不足），如果派出了其他可能性（磁盘慢或者满了

），可以考虑增加归档进程。默认是2；
如果强制远程传送归档日志，检查是否是由于网络或者错误

导致的缓慢。分配更多i/o资源或增加归档目的地的空间可

能解决问题；
检查DBWR是否慢，可能是由于过载或者i/o系统慢，检查

DBWR的时间和i/o系统，如果需要，分配i/o；
检查是不是重做日志太少了。如果系统在DBWR完成检查点之

前生成了足够的redo，增加重做日志文件的大小或个数。

18.log file sync
一个会话提交或回滚是，会话的redo信息需要通过LGWR刷新

到重做日志文件中。这个服务器进程（LGWR）在log file

sync事件写入重做日志的操作完成后才能提交或回滚。

如果这个事件占用了大量的等待事件，检查平均等待事件。
如果平均等待时间少，但等待次数多，应用可能在每次

insert操作后都做了提交操作，而非批量提交。可以通过批

量提交解决问题。
如果平均等待事件长，检查日志写入的会话等待，查看大部

分等待事件在什么地方。如果等待是由于i/o慢，尝试如下

操作：
减少在重做日志文件所在磁盘上其他i/o活动或使用单独磁

盘；
将redo logs放在不同磁盘来最小化归档对于LGWR的影响；
将redo logs放到i/o更快的磁盘（比如从RAID切换到RAID1

）；
考虑使用裸设备提高写入速度；
根据应用的类型，考虑是否可以批量提交；

19.rdbms ipc reply
此事件是等待某个后台进程的响应。

20.sql*net events
下列事件说明数据库进程在等待数据库链接或客户端进程的

允许：
■SQL*Net break/reset to client
■SQL*Net break/reset to dblink
■SQL*Net message from client
■SQL*Net message from dblink
■SQL*Net message to client
■SQL*Net message to dblink
■SQL*Net more data from client
■SQL*Net more data from dblink
■SQL*Net more data to client
■SQL*Net more data to dblink

如果这些事件占用了大量等待事件，瓶颈可能在于网络或者

中间件层。

客户端相关的事件可以通过sql*net message from client

事件的描述来获得；数据库链接相关的事务可以通过

sql*net message from dblink相关事务的描述获得。

（1）sql*net message from client
尽管这是一个空闲事件，却可以用于诊断哪些方面没有问题

。此事件说明服务器进程在等待客户端进程的响应。有时该

事件能积累大量的相应时间，既可能是网络，也坑能是资源

瓶颈。

网络瓶颈：
如果应用在客户端和服务器交互时发生问题，可能是网络有

瓶颈。
大量的该等待事件
大部分时间客户端和服务器都处于空闲（等待网络）

解决方法：
调整应用，减少交互
探索减少潜在因素
修改系统配置，避免网络争用