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

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

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.

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


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

Oracle® is the registered trademark of Oracle Corporation.