Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter