 |
|
Finding Buffer Busy
Waits with STATSPACK
Oracle Tips by
Burleson
|
I am discussing buffer busy waits now because
buffer busy waits are usually associated with segment header
contention that can be remedied by adding additional freelists for the
table or index. However, buffer busy waits are measured at the
instance level and it is to our benefit to look at the instance-wide
reports on buffer busy waits.
Before proceeding, let’s remember that a buffer
busy wait occurs when a database block is found in the data buffer but
it is unavailable because another Oracle task is using the data block.
What follows is a sample STATSPACK report to display buffer busy waits
for each of the three data buffers.
rpt_bbw.sql
set pages
9999;
column buffer_busy_wait format 999,999,999
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'yyyy-mm-dd HH24')
mydate,
new.name,
new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot
sn
where
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.buffer_busy_wait-old.buffer_busy_wait > 1
group by
to_char(snap_time,'yyyy-mm-dd HH24'),
new.name,
new.buffer_busy_wait-old.buffer_busy_wait
;
Here is a sample of the report from this script. Note
that it provides instance-wide buffer busy waits and does not tell us
the data blocks where the wait occurred. We will see advanced
techniques for finding the blocks in the next section.
yr. mo dy Hr
NAME
BUFFER_BUSY_WAIT
------------- -------------------- ----------------
2000-09-21 15 DEFAULT
3
2000-10-02 15 DEFAULT
11
2000-12-11 18 DEFAULT
20
We can enhance this report to show times when the number
of buffer busy waits is causing a performance problem. The script that
follows alerts us when there are more than 400 buffer busy waits
between snapshot intervals.
rpt_bbw_alert.sql
set pages
9999;
column buffer_busy_wait format 999,999,999
column mydate heading 'Yr. Mo Dy Hr.' format a16
select
to_char(snap_time,'yyyy-mm-dd HH24')
mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.buffer_busy_wait-old.buffer_busy_wait > 4000
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
We can run this script and learn those time periods when
buffer busy waits were excessive. This can provide the DBA with
valuable clues about the tables and processes that were involved in
creating the block wait conditions.
SQL> @rpt_bbw_alert.sql
Yr. Mo Dy Hr. BUFFER_BUSY_WAIT
---------------- ----------------
2001-01-04 01
4,570
2001-01-04 06
4,576
2001-01-04 07
4,582
2001-01-04 11
4,669
2001-01-04 12
4,687
2001-01-04 13
4,692
2001-01-04 14
4,762
2001-01-04 20
4,867
2001-01-04 21
4,875
2001-01-04 23
4,883
2001-01-05 00
4,885
2001-01-07 20
5,462
2001-01-07 21
5,471
2001-01-07 22
5,476
2001-01-07 23
5,482
2001-01-08 00
5,482
2001-01-08 01
5,482
2001-01-08 02
5,484
2001-01-08 03
5,504
2001-01-08 04
5,505
2001-01-08 10
5,365
2001-01-08 11
5,396
2001-01-08 12
5,505
2001-01-08 13
5,943
2001-01-08 14
6,155
2001-01-08 15
6,226
2001-01-08 16
6,767
2001-01-08 17
14,396
2001-01-08 18
13,958
2001-01-08 19
13,972
2001-01-08 20
13,977
2001-01-08 21
13,979
2001-01-08 22
13,981
2001-01-08 23
13,982
2001-01-09 00
13,986
2001-01-10 23
4,517
2001-01-11 00
5,033
2001-01-16 21
9,048
2001-01-16 22
9,051
2001-01-16 23
9,051
We can also gain insight into the patterns behind buffer
busy waits by averaging them by the hour of the day. The following
STATSPACK script can be used to develop a buffer busy wait
“signature.”
rpt_avg_bbw_hr.sql
set pages
9999;
column buffer_busy_wait format 999,999,999
column mydate heading 'Yr. Mo Dy Hr.' format a16
select
to_char(snap_time,'HH24')
mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
having
avg(new.buffer_busy_wait-old.buffer_busy_wait) > 0
group by
to_char(snap_time,'HH24')
;
Here is the output from this script that we can paste
into a spreadsheet for charting. We clearly see the average buffer
busy waits for each hour of the day.
Yr. Mo Dy
Hr. BUFFER_BUSY_WAIT
---------------- ----------------
00
155
02
19
03
0
06
5
07
4
08
8
09
28
10
66
11
28
13
31
14
45
15
169
16
61
17
364
18
48
19
34
20
88
22
17
23
186
The chart in Figure 17-8
shows the plot of buffer busy waits during a typical day. Here we see
a clear spike in waits at 3:00
p.m. and again at 5:00
p.m. The next step would be to go to the stats$sql_summary
table and try to locate the SQL and the underlying tables for these
waits.
Figure 6: Average
buffer busy waits by hour of the day
Now that you understand the general nature of
buffer busy waits, let’s move on and see how we can find the exact
object that caused the buffer busy wait.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
