 |
|
Buffer Busy Waits and
DML Contention
Oracle Tips by
Burleson
|
When multiple tasks want to insert or
update rows in a table, there may be contention in the segment
header for the table. This contention can manifest itself as a buffer
busy wait or a freelist wait. Let’s look at some queries that can be
run to identify these contention conditions. You are now ready to
understand how they occur at the table and index levels.
Oracle keeps a v$ view called v$waitstat
and the stats$waitstat table for monitoring wait events. The
following query shows how many times Oracle has waited for a freelist
to become available. As you can see, it does not tell you which
freelists are experiencing the contention problems:
SELECT CLASS,
COUNT
FROM V$WAITSTAT
WHERE CLASS = 'free list';
CLASS
COUNT
---------------
------------
free list
383
The main problem with the v$waitstat
view and the stats$waitstat table is that they only keep the
wait statistics for the whole database and do not distinguish waits by
table or index name. Here, you can see that Oracle had to wait 383
times for a table freelist to become available. This could represent a
wait of 383 times on the same table or perhaps a single wait for 83
separate tables. While 383 seems to be a large number, remember that
Oracle can perform hundreds of I/Os each second, so 383 could be quite
insignificant to the overall system. In any case, if you suspect that
you know which table’s freelist is having the contention, the table
can be exported, dropped, and redefined to have more freelists. While
an extra freelist consumes more of Oracle’s memory, additional
freelists can help throughput on tables that have lots of insert
statements. Generally, you should define extra freelists only on those
tables that will have many concurrent update operations.
Using STATSPACK to Find DML Wait Contention
Now let’s look at how STATSPACK can identify
these wait conditions for concurrent DML. The stats$waitstat
table contains a historical listing of all wait events. The
stats$waitstat contain the following classes:
SQL> select
distinct class from stats$waitstat
CLASS
------------------
bitmap block
bitmap index block
data block
extent map
free list
save undo block
save undo header
segment header
sort block
system undo block
system undo header
undo block
undo header
unused
rpt_waitstat.sql
set pages
999;
set lines 80;
column mydate heading 'Yr. Mo Dy Hr' format
a13;
column class
format a20;
column wait_count
format 999,999;
column time
format 999,999,999;
column avg_wait_secs
format 99,999;
break on to_char(snap_time,'yyyy-mm-dd') skip 1;
select
to_char(snap_time,'yyyy-mm-dd HH24')
mydate,
e.class,
e.wait_count - nvl(b.wait_count,0)
wait_count,
e.time - nvl(b.time,0)
time
from
stats$waitstat b,
stats$waitstat e,
stats$snapshot sn
where
e.snap_id = sn.snap_id
and
b.snap_id = e.snap_id-1
and
b.class = e.class
and
(
e.wait_count - b.wait_count > 1
or
e.time - b.time > 1
)
;
Here is a sample report from this query. Here we see a
list of all wait events and the object of the wait. This information
can sometimes provide insight into a contention problem within Oracle.
Yr. Mo Dy Hr
CLASS
WAIT_COUNT TIME
------------- -------------------- ---------- ------------
2000-12-20 11 data block
2 0
2000-12-20 12 data block
21 0
2000-12-20 12 undo header
5 0
2000-12-20 13 data block
407
0
2000-12-20 13 segment header
3 0
2000-12-20 13 undo block
270
0
2000-12-20 13 undo header
61 0
2000-12-20 16 data block
55 0
2000-12-20 16 undo block
8 0
2000-12-20 16 undo header
5 0
2000-12-20 17 data block 252
0
2000-12-20 18 data block
311
0
2000-12-20 18 undo block
173
0
2000-12-21 00 data block
2,268
0
2000-12-21 00 undo block
744 0
2000-12-21 00 undo header
132
0
2000-12-21 01 data block
2,761
0
2000-12-21 01 undo block
1,078
0
2000-12-21 01 undo header
419
0
2000-12-21 05 data block
7 0
2000-12-21 09 data block
17 0
2000-12-21 09 undo block
8 0
2000-12-21 10 data block
30 0
2000-12-21 10 undo block
29
0
2000-12-21 10 undo header
4 0
2000-12-21 11 data block
139
0
2000-12-21 11 undo header
2 0
2000-12-21 12 data block
17 0
2000-12-21 13 data block
11 0
2000-12-21 14 data block
42 0
2000-12-21 14 undo header
2 0
2000-12-21 15 data block
10 0
2000-12-21 15 undo block
5 0
2000-12-21 16 data block
23 0
2000-12-21 17 data block
17 0
2000-12-21 17 undo block
2 0
2000-12-21 18 data block
122
0
2000-12-21 18 undo block
117
0
2000-12-21 18 undo header
19 0
2000-12-21 21 data block
15 0
2000-12-21 22 data block
3 0
2000-12-22 02 data block
59 0
2000-12-22 08 data block
19 0
2000-12-22 09 data block
72 0
2000-12-22 09 undo block
2 0
2000-12-22 10 data block
57 0
2000-12-22 10 undo block
7 0
2000-12-22 10 undo header
3 0
2000-12-22 11 data block
423
0
2000-12-22 11 undo block
10
0
2000-12-22 16 data block
2 0
2000-12-22 17 data block
319
0
2000-12-22 17 undo block
149
0
2000-12-22 17 undo header
44 0
2000-12-22 18 data block
3 0
2000-12-22 18 undo header
2 0
2000-12-22 19 data block
16 0
2000-12-22 20 data block
5,526
0
2000-12-22 20 segment header
30 0
2000-12-22 20 undo block
46 0
Note that the segment header and data block waits are
often related to competing SQL update tasks that have to wait
on a single freelist in the segment header.
While this STATSPACK report is useful for
summarizing wait conditions within Oracle, it does not tell us the
names of the objects that experienced the wait conditions. The
following section will show you how to drill down and find the
offending data block for buffer busy waits.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
