buffer busy waits
buffer busy waits
=================
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=34405.1
当会话想要访问缓冲存储器中的数据块，而该数据块正在被其它会话使用时产生buffer busy
waits事件。其它会话可能正在从数据文件向缓冲区存储器度曲同样的数据块，或正在缓冲存储器
中对其进行修改。
为了确保读取器会话拥有与获得所有更改或无更改的数据块一致的映像，正在修改该数据块
的会话在其标题中标记一个标志，让其他会话知道有一个更改正在进行而等候更改的的完成。
视图v$waitstat不是OWI的组件，但其为没一类缓冲区提供了有用的等待统计。遭遇buffer
busy等待事件最常见的缓冲区类为块、段标题、撤消块、撤消标题。
下面事例显示一个查询v$waitstat视图的采样输出：
SELECT * FROM V$waitstat WHERE COUNT>0;
CLASS COUNT TIME
------------------ ---------- ----------
data block 4170082 1668098
segment header 116 98
undo header 916 1134
undo block 2087 1681
1、等待参数
buffer wait busy的等待参数描述如下：
◎ P1 在Oracle8及极其以后版本的数据库里，P1显示询问数据块驻留的绝对文件号。
◎ P2 进程需要访问的实际块号。
◎ P3 在Oracle10g以前的版本中，着是表示等待原因的数字。Oracle在内河代码中在
多个地方用不同的原因码提交。该原因码取决于版本。
2、等待时间
100厘秒或1秒。
◎ Oracle会话正在等待钉住一个缓冲区。必须在读取或修改缓冲区前将它钉住。在任何
时刻只有一个进程可以钉住一个缓冲区。
◎ buffer busy waits表明读/读、读/写、写/写争用。
◎ 采取的适当措施取决于P3参数中的原因码。
在SGA中读取或修改缓冲区的会话必须首先获取cache buffers chains锁存器，并且遍历
这个缓冲区链，直到他发现必需的缓冲区头。然后，他必须以共享模式或独占模式获取
一个缓冲区锁或缓冲区头上的pin，这取决于他计划的操作。一旦缓冲区头被钉住，会话
就释放cache buffers chains锁存器，并在缓冲区自身上执行计划的操作。如果无法获
取一个pin，会话就在buffer busy waits等待事件上等待。这种等待时间不会应用于在
会话的私有PGA中执行的读取或写入操作。
3、诊断的原因、诊断和动作
◎ 表示为什么进程无法获得一个缓冲区pin的主要原因码。
◎ buffer busy waits等待时间需要的块类。
◎ 和buffer busy waits时间相关的SQL语句。
◎ 缓冲区所属的段。
--查找等待块类型
SELECT 'segment Header' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Segments a,
V$session_Wait b
WHERE a.Header_File = b.P1
AND a.Header_Block = b.P2
AND b.Event = 'buffer busy waits'
UNION
SELECT 'freelist Groups' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Segments a,
V$session_Wait b
WHERE b.P2 BETWEEN a.Header_Block + 1 AND (a.Header_Block + a.Freelist_Groups)
AND a.Header_File = b.P1
AND a.Freelist_Groups > 1
AND b.Event = 'buffer busy waits'
UNION
SELECT a.Segment_Type || ' Block' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Extents a,
V$session_Wait b
WHERE b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1
AND a.File_Id = b.P1
AND b.Event = 'buffer busy waits'
AND NOT EXISTS (SELECT 1
FROM Dba_Segments
WHERE Header_File = b.P1
AND Header_Block = b.P2);
◆ 带有原因码130的数据块(类#1)争用
如果buffer busy waits的等待事件主要集中在数据块(类#1)上，并且原因码130，则
表明应用程序运行在同一时刻查询相同数据集的多个会话，采用如下三件事最小化问题：
§减少并发级别或该表在运行现成直接内分区工作的方法。
§优化SQL语句，减少物理读取和逻辑读取的数量。
§增加freeLists和freeList Groups的数量。
◆ 带有原因码220的数据块(类#1)争用
多个会话同时在相同的对象上DML。采用如下三件事最小化问题：
§减少并发级别或改变划分部分的方法。
§减少块中行的数量。
§在另一个具有较小块尺寸的表空间中重新构建对象(Oracle 9i或以上版本)。
可以使用较大的PCTFREE重新构建表或索引。可以使用命令改变表以最小化每个块的最
小行数：
ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;
从Oracle 9i开始，可以在另外一个具有较小的块尺寸的表空间中移动或重新构建对象。
虽然这些动作可以最小化buffer busy waits问题，但是他们无疑将增加全表扫描时间
和磁盘空间利用率。常言道，世上没有免费的午餐。
◆ 数据段头(类#4)的争用
如果buffer busy waits的等待时间主要集中在数据段头(即表或索引段头，并且不是
插销段头)上，这意味着数据库中的一些表或索引具有高段头活动。如下解决问题：
§增加已经确定对象的进程FreeLists和FreeList Groups的数量。
§确保PctFree和PctUsed之间内的间隙不会太小。
§确保下一个区尺寸不会太小。
如果不希望混浠FreeLists和FreeList Groups，可以依靠自动段空间管理(Automatic
Segment Space Management,ASSM)特性，以分散从插入语句中引入的数据(9i特性)。
◆ 撤消段都(类#17)的争用
如果buffer busy waits等待时间主要集中在撤消段头上，这表明数据库中的回滚段
过少，或者他们的尺寸太小，从而造成对段头的频繁更新。如果在Oracle 9i中引入的系
统管理撤消，就不需要处理这种问题，因为Oracle将根据需要增加额外的撤消段。
◆ 撤消块的争用(类#18)
如果buffer busy waits等待时间主要集中在撤消块上，这通常意味着多个并发会话同
时查询更新的数据。当应用程序可以在不同的时间内查询和DML时，这种问题就不会存在。
◆ 系统级诊断
--文件等待次数
SELECT b.File_Id,
b.File_Name,
a.COUNT
FROM X$kcbfwait a,
Dba_Data_Files b
WHERE a.Indx = b.File_Id-1
AND a.COUNT > 0
ORDER BY a.COUNT;
buffer busy waits常常是由于很频繁的insert ，需要重建，或者没有充足的回滚段引起的
发生条件：
block正被读入缓冲区或者缓冲区正被其他session使用, 当缓冲区以一种非共享方式或者如正在被读入到缓冲时,
就会出现该等待.该值不应该大于1%
解决办法：
出现此情况通常可能通过几种方式调整：增大data buffer,增加freelist，减小pctused,增加回滚段数目，
增大initrans,考虑使用LMT, 确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
P1 = file# (Absolute File# in Oracle8 onwards)
P2 = block#
P3 = id (Reason Code)
原因代码：
A block is being read
=====
100 We want to NEW the block but the block is currently being read by another session (most likely for undo).
200 We want to NEW the block but someone else has is using the current copy so we have to wait for them to finish.
230 Trying to get a buffer in CR/CRX mode , but a modification has started on the buffer that has not yet been completed.
- A modification is happening on a SCUR or XCUR buffer, but has not yet completed
(dup.) 231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.
130 Block is being read by another session and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefor it will read the CR version of the block.
110 We want the CURRENT block either shared or exclusive but the Block is being read into cache by another session, so we have to wait until their read() is completed.
(duplicate) 120 We want to get the block in current mode but someone else is currently reading it into the cache. Wait for them to complete the read. This occurs during buffer lookup.
210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock and so does not show up as waiting very long. In this case the statistic: "exchange deadlocks" is incremented and we yield the CPU for the "buffer deadlock" wait event.
(duplicate) 220 During buffer lookup for a CURRENT copy of a buffer we have found the buffer but someone holds it in an incompatible mode so we have to wait.
1.
SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"
FROM x$kcbsw s, x$kcbwh w
WHERE s.indx=w.indx
and s."OTHER_WAIT">0
ORDER BY 3
;
2. SELECT count, file#, name
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#
ORDER BY count
3.SELECT distinct owner, segment_name, segment_type
FROM dba_extents
WHERE file_id= &FILE_ID
;
4.SELECT p1 "File", p2 "Block", p3 "Reason"
FROM v$session_wait
WHERE event='buffer busy waits'
;
相关解决办法详解
================
This document discusses a rare and difficult to diagnose database performance
problem characterized by extremely high buffer busy waits that occur at
seemingly random times. The problem persists even after traditional buffer
busy wait tuning practices are followed (typically, increasing the number of
freelists for an object).
SCOPE & APPLICATION
-------------------
This document is intended for support analysts and customers. It applies to
both Unix and Windows-based systems, although the examples here will be
particular to a Unix-based (Solaris) system.
In addition to addressing a specific buffer busy wait performance problem,
in section II, this document presents various techniques to diagnose and
resolve this problem by using detailed data from a real-world example. The
techniques illustrated here may be used to diagnose other I/O and performance
problems.
RESOLVING INTENSE AND "RANDOM" BUFFER BUSY WAIT PERFORMANCE PROBLEMS
--------------------------------------------------------------------
This document is composed of two sections; a summary section that broadly
discusses the problem and its resolution, and a detailed diagnostics section
that shows how to collect and analyze various database and operating system
diagnostics related to this problem. The detailed diagnostics section is
provided to help educate the reader with techniques that may be useful in
other situations.
I. Summary
~~~~~~~~~~~
1. Problem Description
~~~~~~~~~~~~~~~~~~~~~~~
At seemingly random times without regard to overall load on the database,
the following symptoms may be witnessed:
- Slow response times on an instance-wide level
- long wait times for "buffer busy waits" in Bstat/Estat or Statpack reports
- large numbers of sessions waiting on buffer busy waits for a group of
objects (identified in v$session_wait)
Some tuning effort may have been spent in identifying the segments
involved in the buffer busy waits and rebuilding those segments with a higher
number of freelists or freelist groups (from 8.1.6 on one can dynamically add
process freelists; segments only need to be rebuilt if changing freelist
groups). Even after adding freelists, the problem continues and is not
diminished in any way (although regular, concurrency-based buffer busy waits
may be reduced).
2. Problem Diagnosis
~~~~~~~~~~~~~~~~~~~~~
The problem may be diagnosed by observing the following:
- The alert.log file shows many occurrences of ORA-600, ORA-7445 and
core dumps during or just before the time of the problem.
- The core_dump_dest directory contains large core dumps during the
time of the problem. There may either be many core dumps or a few
very large core dumps (100s of MB per core file), depending on the
size of the SGA.
查看cdump下的文件及大小
- sar -d shows devices that are completely saturated and have high
request queues and service times. These devices and/or their
controllers are part of logical volumes used for database files.
磁盘使用情况
- Buffer busy waits, write complete waits, db file parallel writes and
enqueue waits are high (in top 5 waits, usually in that order).
Note that in environments using Oracle Advanced Replication, the
buffer busy waits may at times appear on segments related to
replication (DEF$_AQCALL table, TRANORDER index, etc...).
3. Problem Resolution
~~~~~~~~~~~~~~~~~~~~~~
The cause for the buffer busy waits and other related waits might be a
saturated disk controller or subsystem impacting the database's ability to read
or write blocks. The disk/controller may be saturated because of the many
core dumps occurring simultaneously requiring hundreds of megabytes each. If
the alert.log or core_dump_dest directory has no evidence of core dumps, then
the source of the I/O saturation must be found. It may be due to non-database
processes, another database sharing the same filesystems, or a poorly tuned
I/O subsystem.
The solution is as follows:
1) Find the root cause for the I/O saturation (core dumps,
another process or database, or poorly performing I/O
subsystem) and resolve it.
OR,
2) If evidence of core dumps are found:
- Find the causes for the core dumps and resolve
them (patch, etc)
- Move the core_dump_dest location to a filesystem
not shared with database files.
- Use the following init.ora parameters to reduce
or avoid the core dumps:
shadow_core_dump = partial
background_core_dump = partial
These core dump parameters can also be set to "none"
but this is not recommended unless the causes for the
core dumps have been identified.
B. SAR Diagnostics
~~~~~~~~~~~~~~~~~~
SAR, IOSTAT, or similar tools are critical to diagnosing this problem because
they show the health of the I/O system during the time of the problem. The
SAR data for the example we are looking at is shown below (shown
using "sar -d -f /var/adm/sa/sa16"):
SunOS prod1 5.6 Generic_105181-23 sun4u 05/16/01
01:00:00 device %busy avque r+w/s blks/s avwait avserv
sd22 100 72.4 2100 2971 0.0 87.0
sd22,c 0 0.0 0 0 0.0 0.0
sd22,d 0 0.0 0 0 0.0 0.0
sd22,e 100 72.4 2100 2971 0.0 87.0
/\
||
extremely high queue values (usually less than 2 during peak)
By mapping the sd22 device back to the device number (c3t8d0) and then back to
the logical volume through to the filesystem (using "
f" and Veritas'
utility /usr/sbin/vxprint), it was determined the filesystem shared the same
controller (c3) as several database files (among them were the datafiles for
the SYSTEM tablespace).
By looking within the filesystems using the aforementioned controller (c3),
several very large (1.8 GB) core dumps were found in the core_dump_dest
directory, produced around the time of the problem.
The following lists some key statistics to look at:
Statistic Total per Second per Trans
----------------------- ---------------- ------------ ------------
consistent changes 43,523 12.1 2.4 Much
free buffer inspected 6,087 1.7 0.3 <== higher
free buffer requested 416,010 115.6 23.1 than
logons cumulative 15,718 4.4 0.9 normal
physical writes 24,757 6.9 1.4
write requests 634 0.2 0.0
iii. Tablespace I/O Summary
The average wait times for tablespaces will be dramatically higher.
Tablespace IO Summary for DB: PROD Instance: PROD Snaps: 3578 - 3579
Avg Read Total Avg Wait
Tablespace Reads (ms) Writes Waits (ms)
----------- ----------- -------- ----------- ---------- --------
BOM 482,368 7.0 18,865 3,161 205.9 very
CONF 157,288 0.6 420 779 9897.3 <= high
CTXINDEX 36,628 0.5 7 4 12.5 very
RBS 613 605.7 23,398 8,253 7694.6 <= high
SYSTEM 18,360 3.6 286 78 745.5
DB_LOW_DATA 16,560 2.6 1,335 14 24.3
比如是由于热块造成的，可以使用修改pctfree到一个大的值，利用空间来提高性能。
================
统计某个区域的等待事件信息
================
CREATE TABLE sinoview.previous_events AS
SELECT SYSDATE timestamp, v$system_event.*
FROM v$system_event;
EXECUTE dbms_lock.sleep (30);
SELECT A.event,
A.total_waits
- NVL (B.total_waits, 0) total_waits,
A.time_waited
- NVL (B.time_waited, 0) time_waited
FROM v$system_event A, previous_events B
WHERE A.event NOT IN ('client message',
'dispatcher timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'lock manager wait for remote message',
'null event',
'parallel query dequeue',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deque wait',
'PX Idle Wait',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'virtual circuit status',
'wakeup time manager' )
AND B.event (+) = A.event
ORDER BY time_waited;
=================
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=34405.1
当会话想要访问缓冲存储器中的数据块，而该数据块正在被其它会话使用时产生buffer busy
waits事件。其它会话可能正在从数据文件向缓冲区存储器度曲同样的数据块，或正在缓冲存储器
中对其进行修改。
为了确保读取器会话拥有与获得所有更改或无更改的数据块一致的映像，正在修改该数据块
的会话在其标题中标记一个标志，让其他会话知道有一个更改正在进行而等候更改的的完成。
视图v$waitstat不是OWI的组件，但其为没一类缓冲区提供了有用的等待统计。遭遇buffer
busy等待事件最常见的缓冲区类为块、段标题、撤消块、撤消标题。
下面事例显示一个查询v$waitstat视图的采样输出：
SELECT * FROM V$waitstat WHERE COUNT>0;
CLASS COUNT TIME
------------------ ---------- ----------
data block 4170082 1668098
segment header 116 98
undo header 916 1134
undo block 2087 1681
1、等待参数
buffer wait busy的等待参数描述如下：
◎ P1 在Oracle8及极其以后版本的数据库里，P1显示询问数据块驻留的绝对文件号。
◎ P2 进程需要访问的实际块号。
◎ P3 在Oracle10g以前的版本中，着是表示等待原因的数字。Oracle在内河代码中在
多个地方用不同的原因码提交。该原因码取决于版本。
2、等待时间
100厘秒或1秒。
◎ Oracle会话正在等待钉住一个缓冲区。必须在读取或修改缓冲区前将它钉住。在任何
时刻只有一个进程可以钉住一个缓冲区。
◎ buffer busy waits表明读/读、读/写、写/写争用。
◎ 采取的适当措施取决于P3参数中的原因码。
在SGA中读取或修改缓冲区的会话必须首先获取cache buffers chains锁存器，并且遍历
这个缓冲区链，直到他发现必需的缓冲区头。然后，他必须以共享模式或独占模式获取
一个缓冲区锁或缓冲区头上的pin，这取决于他计划的操作。一旦缓冲区头被钉住，会话
就释放cache buffers chains锁存器，并在缓冲区自身上执行计划的操作。如果无法获
取一个pin，会话就在buffer busy waits等待事件上等待。这种等待时间不会应用于在
会话的私有PGA中执行的读取或写入操作。
3、诊断的原因、诊断和动作
◎ 表示为什么进程无法获得一个缓冲区pin的主要原因码。
◎ buffer busy waits等待时间需要的块类。
◎ 和buffer busy waits时间相关的SQL语句。
◎ 缓冲区所属的段。
--查找等待块类型
SELECT 'segment Header' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Segments a,
V$session_Wait b
WHERE a.Header_File = b.P1
AND a.Header_Block = b.P2
AND b.Event = 'buffer busy waits'
UNION
SELECT 'freelist Groups' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Segments a,
V$session_Wait b
WHERE b.P2 BETWEEN a.Header_Block + 1 AND (a.Header_Block + a.Freelist_Groups)
AND a.Header_File = b.P1
AND a.Freelist_Groups > 1
AND b.Event = 'buffer busy waits'
UNION
SELECT a.Segment_Type || ' Block' CLASS,
a.Segment_Type,
a.Segment_Name,
a.Partition_Name
FROM Dba_Extents a,
V$session_Wait b
WHERE b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1
AND a.File_Id = b.P1
AND b.Event = 'buffer busy waits'
AND NOT EXISTS (SELECT 1
FROM Dba_Segments
WHERE Header_File = b.P1
AND Header_Block = b.P2);
◆ 带有原因码130的数据块(类#1)争用
如果buffer busy waits的等待事件主要集中在数据块(类#1)上，并且原因码130，则
表明应用程序运行在同一时刻查询相同数据集的多个会话，采用如下三件事最小化问题：
§减少并发级别或该表在运行现成直接内分区工作的方法。
§优化SQL语句，减少物理读取和逻辑读取的数量。
§增加freeLists和freeList Groups的数量。
◆ 带有原因码220的数据块(类#1)争用
多个会话同时在相同的对象上DML。采用如下三件事最小化问题：
§减少并发级别或改变划分部分的方法。
§减少块中行的数量。
§在另一个具有较小块尺寸的表空间中重新构建对象(Oracle 9i或以上版本)。
可以使用较大的PCTFREE重新构建表或索引。可以使用命令改变表以最小化每个块的最
小行数：
ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;
从Oracle 9i开始，可以在另外一个具有较小的块尺寸的表空间中移动或重新构建对象。
虽然这些动作可以最小化buffer busy waits问题，但是他们无疑将增加全表扫描时间
和磁盘空间利用率。常言道，世上没有免费的午餐。
◆ 数据段头(类#4)的争用
如果buffer busy waits的等待时间主要集中在数据段头(即表或索引段头，并且不是
插销段头)上，这意味着数据库中的一些表或索引具有高段头活动。如下解决问题：
§增加已经确定对象的进程FreeLists和FreeList Groups的数量。
§确保PctFree和PctUsed之间内的间隙不会太小。
§确保下一个区尺寸不会太小。
如果不希望混浠FreeLists和FreeList Groups，可以依靠自动段空间管理(Automatic
Segment Space Management,ASSM)特性，以分散从插入语句中引入的数据(9i特性)。
◆ 撤消段都(类#17)的争用
如果buffer busy waits等待时间主要集中在撤消段头上，这表明数据库中的回滚段
过少，或者他们的尺寸太小，从而造成对段头的频繁更新。如果在Oracle 9i中引入的系
统管理撤消，就不需要处理这种问题，因为Oracle将根据需要增加额外的撤消段。
◆ 撤消块的争用(类#18)
如果buffer busy waits等待时间主要集中在撤消块上，这通常意味着多个并发会话同
时查询更新的数据。当应用程序可以在不同的时间内查询和DML时，这种问题就不会存在。
◆ 系统级诊断
--文件等待次数
SELECT b.File_Id,
b.File_Name,
a.COUNT
FROM X$kcbfwait a,
Dba_Data_Files b
WHERE a.Indx = b.File_Id-1
AND a.COUNT > 0
ORDER BY a.COUNT;
buffer busy waits常常是由于很频繁的insert ，需要重建，或者没有充足的回滚段引起的
发生条件：
block正被读入缓冲区或者缓冲区正被其他session使用, 当缓冲区以一种非共享方式或者如正在被读入到缓冲时,
就会出现该等待.该值不应该大于1%
解决办法：
出现此情况通常可能通过几种方式调整：增大data buffer,增加freelist，减小pctused,增加回滚段数目，
增大initrans,考虑使用LMT, 确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
P1 = file# (Absolute File# in Oracle8 onwards)
P2 = block#
P3 = id (Reason Code)
原因代码：
A block is being read
=====
100 We want to NEW the block but the block is currently being read by another session (most likely for undo).
200 We want to NEW the block but someone else has is using the current copy so we have to wait for them to finish.
230 Trying to get a buffer in CR/CRX mode , but a modification has started on the buffer that has not yet been completed.
- A modification is happening on a SCUR or XCUR buffer, but has not yet completed
(dup.) 231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.
130 Block is being read by another session and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefor it will read the CR version of the block.
110 We want the CURRENT block either shared or exclusive but the Block is being read into cache by another session, so we have to wait until their read() is completed.
(duplicate) 120 We want to get the block in current mode but someone else is currently reading it into the cache. Wait for them to complete the read. This occurs during buffer lookup.
210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock and so does not show up as waiting very long. In this case the statistic: "exchange deadlocks" is incremented and we yield the CPU for the "buffer deadlock" wait event.
(duplicate) 220 During buffer lookup for a CURRENT copy of a buffer we have found the buffer but someone holds it in an incompatible mode so we have to wait.
1.
SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"
FROM x$kcbsw s, x$kcbwh w
WHERE s.indx=w.indx
and s."OTHER_WAIT">0
ORDER BY 3
;
2. SELECT count, file#, name
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#
ORDER BY count
3.SELECT distinct owner, segment_name, segment_type
FROM dba_extents
WHERE file_id= &FILE_ID
;
4.SELECT p1 "File", p2 "Block", p3 "Reason"
FROM v$session_wait
WHERE event='buffer busy waits'
;
相关解决办法详解
================
This document discusses a rare and difficult to diagnose database performance
problem characterized by extremely high buffer busy waits that occur at
seemingly random times. The problem persists even after traditional buffer
busy wait tuning practices are followed (typically, increasing the number of
freelists for an object).
SCOPE & APPLICATION
-------------------
This document is intended for support analysts and customers. It applies to
both Unix and Windows-based systems, although the examples here will be
particular to a Unix-based (Solaris) system.
In addition to addressing a specific buffer busy wait performance problem,
in section II, this document presents various techniques to diagnose and
resolve this problem by using detailed data from a real-world example. The
techniques illustrated here may be used to diagnose other I/O and performance
problems.
RESOLVING INTENSE AND "RANDOM" BUFFER BUSY WAIT PERFORMANCE PROBLEMS
--------------------------------------------------------------------
This document is composed of two sections; a summary section that broadly
discusses the problem and its resolution, and a detailed diagnostics section
that shows how to collect and analyze various database and operating system
diagnostics related to this problem. The detailed diagnostics section is
provided to help educate the reader with techniques that may be useful in
other situations.
I. Summary
~~~~~~~~~~~
1. Problem Description
~~~~~~~~~~~~~~~~~~~~~~~
At seemingly random times without regard to overall load on the database,
the following symptoms may be witnessed:
- Slow response times on an instance-wide level
- long wait times for "buffer busy waits" in Bstat/Estat or Statpack reports
- large numbers of sessions waiting on buffer busy waits for a group of
objects (identified in v$session_wait)
Some tuning effort may have been spent in identifying the segments
involved in the buffer busy waits and rebuilding those segments with a higher
number of freelists or freelist groups (from 8.1.6 on one can dynamically add
process freelists; segments only need to be rebuilt if changing freelist
groups). Even after adding freelists, the problem continues and is not
diminished in any way (although regular, concurrency-based buffer busy waits
may be reduced).
2. Problem Diagnosis
~~~~~~~~~~~~~~~~~~~~~
The problem may be diagnosed by observing the following:
- The alert.log file shows many occurrences of ORA-600, ORA-7445 and
core dumps during or just before the time of the problem.
- The core_dump_dest directory contains large core dumps during the
time of the problem. There may either be many core dumps or a few
very large core dumps (100s of MB per core file), depending on the
size of the SGA.
查看cdump下的文件及大小
- sar -d shows devices that are completely saturated and have high
request queues and service times. These devices and/or their
controllers are part of logical volumes used for database files.
磁盘使用情况
- Buffer busy waits, write complete waits, db file parallel writes and
enqueue waits are high (in top 5 waits, usually in that order).
Note that in environments using Oracle Advanced Replication, the
buffer busy waits may at times appear on segments related to
replication (DEF$_AQCALL table, TRANORDER index, etc...).
3. Problem Resolution
~~~~~~~~~~~~~~~~~~~~~~
The cause for the buffer busy waits and other related waits might be a
saturated disk controller or subsystem impacting the database's ability to read
or write blocks. The disk/controller may be saturated because of the many
core dumps occurring simultaneously requiring hundreds of megabytes each. If
the alert.log or core_dump_dest directory has no evidence of core dumps, then
the source of the I/O saturation must be found. It may be due to non-database
processes, another database sharing the same filesystems, or a poorly tuned
I/O subsystem.
The solution is as follows:
1) Find the root cause for the I/O saturation (core dumps,
another process or database, or poorly performing I/O
subsystem) and resolve it.
OR,
2) If evidence of core dumps are found:
- Find the causes for the core dumps and resolve
them (patch, etc)
- Move the core_dump_dest location to a filesystem
not shared with database files.
- Use the following init.ora parameters to reduce
or avoid the core dumps:
shadow_core_dump = partial
background_core_dump = partial
These core dump parameters can also be set to "none"
but this is not recommended unless the causes for the
core dumps have been identified.
B. SAR Diagnostics
~~~~~~~~~~~~~~~~~~
SAR, IOSTAT, or similar tools are critical to diagnosing this problem because
they show the health of the I/O system during the time of the problem. The
SAR data for the example we are looking at is shown below (shown
using "sar -d -f /var/adm/sa/sa16"):
SunOS prod1 5.6 Generic_105181-23 sun4u 05/16/01
01:00:00 device %busy avque r+w/s blks/s avwait avserv
sd22 100 72.4 2100 2971 0.0 87.0
sd22,c 0 0.0 0 0 0.0 0.0
sd22,d 0 0.0 0 0 0.0 0.0
sd22,e 100 72.4 2100 2971 0.0 87.0
/\
||
extremely high queue values (usually less than 2 during peak)
By mapping the sd22 device back to the device number (c3t8d0) and then back to
the logical volume through to the filesystem (using "
f" and Veritas' utility /usr/sbin/vxprint), it was determined the filesystem shared the same
controller (c3) as several database files (among them were the datafiles for
the SYSTEM tablespace).
By looking within the filesystems using the aforementioned controller (c3),
several very large (1.8 GB) core dumps were found in the core_dump_dest
directory, produced around the time of the problem.
The following lists some key statistics to look at:
Statistic Total per Second per Trans
----------------------- ---------------- ------------ ------------
consistent changes 43,523 12.1 2.4 Much
free buffer inspected 6,087 1.7 0.3 <== higher
free buffer requested 416,010 115.6 23.1 than
logons cumulative 15,718 4.4 0.9 normal
physical writes 24,757 6.9 1.4
write requests 634 0.2 0.0
iii. Tablespace I/O Summary
The average wait times for tablespaces will be dramatically higher.
Tablespace IO Summary for DB: PROD Instance: PROD Snaps: 3578 - 3579
Avg Read Total Avg Wait
Tablespace Reads (ms) Writes Waits (ms)
----------- ----------- -------- ----------- ---------- --------
BOM 482,368 7.0 18,865 3,161 205.9 very
CONF 157,288 0.6 420 779 9897.3 <= high
CTXINDEX 36,628 0.5 7 4 12.5 very
RBS 613 605.7 23,398 8,253 7694.6 <= high
SYSTEM 18,360 3.6 286 78 745.5
DB_LOW_DATA 16,560 2.6 1,335 14 24.3
比如是由于热块造成的，可以使用修改pctfree到一个大的值，利用空间来提高性能。
================
统计某个区域的等待事件信息
================
CREATE TABLE sinoview.previous_events AS
SELECT SYSDATE timestamp, v$system_event.*
FROM v$system_event;
EXECUTE dbms_lock.sleep (30);
SELECT A.event,
A.total_waits
- NVL (B.total_waits, 0) total_waits,
A.time_waited
- NVL (B.time_waited, 0) time_waited
FROM v$system_event A, previous_events B
WHERE A.event NOT IN ('client message',
'dispatcher timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'lock manager wait for remote message',
'null event',
'parallel query dequeue',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deque wait',
'PX Idle Wait',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'virtual circuit status',
'wakeup time manager' )
AND B.event (+) = A.event
ORDER BY time_waited;
