Oracle : buffer busy wait
来源:互联网 发布:淘宝店铺装修设计 编辑:程序博客网 时间:2024/05/22 04:45
http://www.oraclerealworld.com/oracle-buffer-busy-wait/
- data block
- IF OTYPE =
- INDEX , then the insert index leaf block is probably hot, solutions are
- Hash partition the index
- Use reverse key index
- TABLE, then insert block is hot,solutions
- Use free lists
- Put Object in ASSM tablespace
- Segment header - If “segment header” occurs at the same time as CLASS= “data block” on the same object and the object is of OTYPE= “TABLE” then this is just a confirmation that the TABLE needs to use free lists or ASSM.
- File Header Block - Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.
- free lists - Add free list groups to the object
- undo header - Not enough UNDO segments, if using old RBS then switch to AUM
- undo block - Hot spot in UNDO, application issue
select o.object_name obj, o.object_type otype, ash.SQL_ID, w.classfrom v$active_session_history ash, ( select rownum class#, class from v$waitstat ) w, all_objects owhere event='buffer busy waits' and w.class#(+)=ash.p3 and o.object_id (+)= ash.CURRENT_OBJ#Order by sample_time;
OBJ OTYPE SQL_ID CLASS------ ------ ------------- ------------------TOTO1 TABLE 8gz51m9hg5yuf data blockTOTO1 TABLE 8gz51m9hg5yuf data blockTOTO1 TABLE 8gz51m9hg5yuf segment headerTOTO1 TABLE 8gz51m9hg5yuf data block
set linesize 120
col block_type for a20
col objn for a25
col otype for a15
col filen for 9999
col blockn for 9999999
col obj for a20
col tbs for a10
select
bbw.cnt,
bbw.obj,
bbw.otype,
bbw.sql_id,
bbw.block_type,
nvl(tbs.name,to_char(bbw.p1)) TBS,
tbs_defs.assm ASSM
from (
select
count(*) cnt,
nvl(object_name,CURRENT_OBJ#) obj,
o.object_type otype,
ash.SQL_ID sql_id,
nvl(w.class,'usn '||to_char(ceil((ash.p3-18)/2))||' '||
decode(mod(ash.p3,2),
1,'header',
0,'block')) block_type,
--nvl(w.class,to_char(ash.p3)) block_type,
ash.p1 p1
from v$active_session_history ash,
( select rownum class#, class from v$waitstat ) w,
all_objects o
where event='buffer busy waits'
and w.class#(+)=ash.p3
and o.object_id (+)= ash.CURRENT_OBJ#
and ash.session_state='WAITING'
and ash.sample_time > sysdate - &minutes/(60*24)
--and w.class# > 18
group by o.object_name, ash.current_obj#, o.object_type,
ash.sql_id, w.class, ash.p3, ash.p1
) bbw,
(select file_id,
tablespace_name name
from dba_data_files
) tbs,
(select
tablespace_name NAME,
extent_management LOCAL,
allocation_type EXTENTS,
segment_space_management ASSM,
initial_extent
from dba_tablespaces
) tbs_defs
where tbs.file_id(+) = bbw.p1
and tbs.name=tbs_defs.name
Order by bbw.cnt
/
CNT OBJ OTYPE SQL_ID BLOCK_TYPE TBS ASSM
----- ------- ------- ------------- ---------------- ---------- ------
3 TOTO1 TABLE 8gz51m9hg5yuf segment header NO_ASSM MANUAL
59 TOTO1 TABLE 8gz51m9hg5yuf data block NO_ASSM MANUAL
1) IO read contention (only Oracle 9i and below)
2) Insert Block Contention on Tables or Indexes3) Rollback Segment Contention
On 7.0 - 8.1.5 see http://sites.google.com/site/embtdbo/oracle-buffer-busy-wait/oracle-buffer-busy-wait-7-8-1-5
On version 8 and 9, the p3 value has a different meaning. Instead of meaning the block type (which is the best thing to know) it means the kind of buffer busy wait. There are only two values that matter to us, values in
100 range = read waits (basically just an IO wait)
Reader blocking Reader, ie one reader is reading a block in and another person wants to read this block and waits on a buffer busy wait p3=130.
200 range = write contetion (same as in 10g)
Writers blocking other writers for example while doing inserts either because of no free lists on the table or because everyone is inserting into the same index block.
select
count(*) cnt,
o.object_name obj,
o.object_type otype,
ash.CURRENT_OBJ#,
ash.SQL_ID,
decode(substr(ash.p3,1,1),1,'read',2,'write',p3) p3
from v$active_session_history ash,
all_objects o
where event='buffer busy waits'
and o.object_id (+)= ash.CURRENT_OBJ#
group by o.object_name, o.object_type, ash.sql_id, ash.p3,ash.CURRENT_OBJ#
order by cnt
/
CNT OBJ OTYPE CURRENT_OBJ# SQL_ID P3
--- ------- ------- ------------ ---------- ------
1 -1 1375352856 read
2 -1 996767823 read
2 -1 2855119862 write
17 -1 1375352856 write
89 TOTO1 TABLE 296030 1212617343 write
109 296022 1212617343 write
Often the Current_obj# is -1 so we can’t figure out what the object is . There is an alternative method
col block_type for a18
col objn for a25
col otype for a15
col event for a15
col blockn for 999999
col segment_name for a20
col partition_name for a15
col owner for a15
set timing on
/*
drop table myextents;
l
create table myextents as select * from dba_extents;
l
*/
select
count(*),
ext.owner,
ext.segment_name,
ext.partition_name,
ext.segment_type,
decode(substr(ash.p3,1,1),1,'read',2,'write',p3) p3
--ash.p1,
--ash.p2
from v$active_session_history ash,
myextents ext
where
event = 'buffer busy waits'
and ( current_obj# = -1 or current_obj#=0 or current_obj# is null )
--and sample_time > sysdate - &minutes/(60*24)
--and session_state='WAITING'
and ext.file_id(+)=ash.p1 and
ash.p2 between ext.block_id and ext.block_id + ext.blocks
group by
ext.owner,
ext.segment_name,
ext.partition_name,
ext.segment_type,
p3
--ash.p1,
--ash.p2,
--ash.sql_id
Order by count(*)
/
CNT OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE P3
--- ------ -------------- --------------- ------------- --------
1 SYS _SYSSMU2$ TYPE2 UNDO read
1 SYS _SYSSMU3$ TYPE2 UNDO write
The first step in finding out the source of buffer busy waits is looking at
V$waitstatsThis will tell us what kind of datablocks we have contention on.
You can also get an idea of what file contains the object with the buffer busy waits by looking at:
X$KCBFWAIT
Starting in version 9i there is the table
v$segstatThat will list the objects with buffer busy waits.If you are on version 7 or 8 good luck finding the object without setting up ASH style data collection.
- Oracle’s SGA, or System Global Area, a memory that is shared between Oracle users
- LGWR – log writer process
- DBWR – database writer process
- User1,2,3 … – user processes, in this case “shadow processes”
On the machine file system are
- Redo log files
- Data files
- Log Buffer
- Library Cache
- Buffer Cache
A buffer busy can happen on oracle 7,8 and 9 when one user is reading a block into memory and a second user wants to read that block. Instead of the second user trying to read that block into memory as well, they just wait for the first user to finish. Starting in Oracle 10, this kind of wait was renames “read by other session”
select
tablespace_name,
extent_management LOCAL,
allocation_type EXTENTS,
segment_space_management ASSM,
initial_extent
from dba_tablespaces
TABLESPACE_NAME LOCAL EXTENTS ASSM
--------------- ---------- --------- ------
SYSTEM LOCAL SYSTEM MANUAL
UNDOTBS1 LOCAL SYSTEM MANUAL
SYSAUX LOCAL SYSTEM AUTO
TEMP LOCAL UNIFORM MANUAL
USERS LOCAL SYSTEM AUTO
EXAMPLE LOCAL SYSTEM AUTO
DATA LOCAL SYSTEM MANUAL
create tablespace data2
datafile '/d3/kyle/data2_01.dbf'
size 200M
segment space management auto;
BBW on index (because of insert)
BBW on old style RBS
Select CURRENT_OBJ#||' '||o.object_name objn,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID,
w.class ||' '||to_char(ash.p3) block_type
from v$active_session_history ash,
(select rownum class#, class from v$waitstat ) w,
all_objects o
where event='buffer busy waits'
and w.class#(+)=ash.p3
and o.object_id (+)= ash.CURRENT_OBJ#
Order by sample_time;
OBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE
----------- ------ ------ ------ ------------- ------------
54962 TOTO1 TABLE 16 45012 8gz51m9hg5yuf data block
54962 TOTO1 TABLE 16 161 8gz51m9hg5yuf segment header
0 14 9 8gz51m9hg5yuf 87
0 14 9 8gz51m9hg5yuf 87
IF the block is of class > 18, the there will be no object name, so we have to look it up ourselves to be sure:
select segment_name,
segment_type
from dba_extents
where
&P2 between
block_id and block_id + blocks – 1
and
file_id = &P1 ;
Plug in 14 for P1 the file # and 9 for P2 the block number:
SEGMENT_NAME SEGMENT_TYPE
-------------- --------------
R2 ROLLBACK
alter system set undo_management=auto scope=spfile;
Time P1 P2 OBJN OTYPE FN BLOCKN BLOCK_TYPE
----- --- --- ---- ----- -- ------ -----------------
11:44 202 2 -1 0 0 file header block
11:44 202 2 TOTO TABLE 1 60218 file header block
11:44 202 2 TOTO TABLE 1 60218 file header block
11:44 202 2 TOTO TABLE 1 60218 file header block
11:44 202 2 TOTO TABLE 1 60218 file header block
- Oracle : buffer busy wait
- Oracle wait event --- buffer busy waits
- Oracle buffer busy waits
- oracle buffer busy waits
- buffer busy wait - file header block
- Common Wait Events---buffer busy waits
- oracle等待事件4——buffer busy wait 特别介绍
- 高级OWI与ORACLE性能调整读书笔记之UPDATE/UPADTE引起的buffer busy wait
- 移动项目中buffer busy wait案例解析
- 模拟产生CBC LATCH与buffer busy wait等待事件
- 并发update/update引起的buffer busy wait
- Causes of Oracle Buffer Busy Waits
- oracle 模拟出 buffer busy waits 事件
- 从buffer busy waits看oracle内存使用机制
- oracle 重要的等待事件--buffer busy waits
- Buffer busy wait与latch :cache buffers chains有何联系?
- latch -buffer busy wait 之表数据块争用导致热块
- latch -buffer busy wait 之索引数据块争用导致热块
- C++中的数组指针
- SAS:SAS 常用过程之 统计描述过程proc means
- Android SDK r14离线安装包
- 基于lucene.net 和ICTCLAS2014的站内搜索的实现1
- 《最长非降子序列》 动态规划算法入门
- Oracle : buffer busy wait
- 百度之星初赛第二轮第一题超时代码(可能还有错误)
- #if defined和#if !defined
- ser文件与Java对象序列化
- android开源项目下载代码的方法
- 设计模式的分类
- C语言中的time函数
- matlab实现的手写体字符截取字符区域的方法
- Eclipse 配色方案的修改