Searching for freelist and buffer busy waits

Whenever multiple insert or update tasks access a table, it is possible that Oracle may be forced to wait to access the first block in the table. The first block is called the segment header, and the segment header contains the freelist for the table.



The number of freelists for any table should be set to the high-water mark of concurrent inserts or updates.



The script below will tell you if you have waits for table or index freelists. If so, you need to identify the table and add additional freelists. In Oracle8i, you can add freelists with the ALTER table command, but in earlier versions of Oracle you must reorganize the table to add freelists.



The procedure for identifying the specific table associated with a freelist wait or a buffer busy wait is complex, but it is fully described in my Oracle Press book “Oracle High-Performance Tuning with STATSPACK.



column s_v format 999,999,999 heading 'Total Requests' new_value tnr

column count format 99999990 heading ‘count’ new_value cnt

column proc heading 'Ratio of waits'



PROMPT Current v$waitstat freelist waits...

PROMPT

set heading on;





prompt - This displays the total current waits on freelists

select

class,

count

from

v$waitstat

where

class = 'free list';





prompt - This displays the total gets in the database

select

sum(value) s_v

from

v$sysstat

where

name IN ('db block gets', 'consistent gets');



PROMPT Here is the ratio

select

&cnt/&tnr * 100 proc

from

dual;





Here is the listing from this script. Whenever waits appear in v$waitstat, you need to investigate to see which table or index is experiencing multiple concurrent inserts or updates.





SQL> @cont

Current v$waitstat freelist waits...

- This displays the total current waits on freelists



CLASS COUNT

------------------ ---------

free list 0

- This displays the total gets in the database



Total Num of Requests

---------------------

140318872

Here is the ratio



Ratio in %

----------

0



Please note the freelist contention also can be manifested as a buffer busy wait. This is because the block is already in the buffer, but cannot be accessed because another task has the segment header.



The section below describes the process the block address associated with a wait.



As we discussed, Oracle does not keep an accumulator to track individual buffer busy waits. To see them, you must create a script to detect them and then schedule the task to run frequently on your database server.



get_busy.ksh

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=proderp
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep \^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME

# sample every 10 seconds
SAMPLE_TIME=10

while true
do

#*************************************************************
# Test to see if Oracle is accepting connections
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s /< /tmp/check_$ORACLE_SID.ora
select * from v\$database;
exit
!

#*************************************************************
# If not, exit immediately . . .
#*************************************************************
check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -gt 0 ]
then
exit 0
fi

rm -f /export/home/oracle/statspack/busy.lst

$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat< /tmp/busy.lst

set feedback off;
select
sysdate,
event,
substr(tablespace_name,1,14),
p2
from
v\$session_wait a,
dba_data_files b
where
a.p1 = b.file_id
;
!

var=`cat /tmp/busy.lst|wc -l`

echo $var
if [[ $var -gt 1 ]];
then
echo
**********************************************************************"
echo "There are waits"
cat /tmp/busy.lst|mailx -s "Prod block wait found"\
info@remote-dba.net \
Larry_Ellison@oracle.com
echo
**********************************************************************"
exit
fi

sleep $SAMPLE_TIME
done



As we can see from this script, it probes the database for buffer busy waits every 10 seconds. When a buffer busy wait is found, it mails the date, tablespace name, and block number to the DBA. Here is an example of a block alert e-mail:



SYSDATE SUBSTR(TABLESP BLOCK
--------- -------------- ----------
28-DEC-00 APPLSYSD 25654



Here we see that we have a block wait condition at block 25654 in the applsysd tablespace. The procedure for locating this block is beyond the scope of this tip, but complete directions are in Chapter 10 of Oracle High Performance Tuning with STATSPACK.