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.class 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;
OBJ OTYPE SQL_ID CLASS ------ ------ ------------- ------------------ TOTO1 TABLE 8gz51m9hg5yuf data block TOTO1 TABLE 8gz51m9hg5yuf data block TOTO1 TABLE 8gz51m9hg5yuf segment header TOTO1 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$segstat
That 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)
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
Interesting link sent to me by Yong Huang http://www.askmaclean.com/archives/gc-buffer-busy-log-flush-sync-log-file-sync.html
which has an ADDM which on the particular instance in question, gave the following interesting analysis :
RECOMMENDATION 2: Host Configuration, 12% benefit (507182 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was
40 K and the average time per write was 10 milliseconds.
ADDITIONAL INFORMATION:
Waits on event “log file sync” were the cause of significant database
wait on “gc buffer busy” when releasing a data block. Waits on event
“log file sync” in this instance can cause global cache contention on
remote instances.
see http://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/
for some nice analysis of the buffer busy waits on the file header block