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 the Offending Block for a Buffer Busy Wait
Oracle Tips by Burleson
 

As I 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"\
  don@remote-dba.net \
  Larry_Ellison@oracle.com
  echo
***********************************************************"
 exit
fi

sleep $SAMPLE_TIME
done

As you 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 P2
--------- -------------- ----------
28-DEC-00 APPLSYSD        25654

Here we see that we have a block wait condition at block 25654 in the applsysd tablespace. To see the contents of this data block, we have several command options:

SQL> alter system dump datafile 1 block 25654;
System altered.

or:

SQL > alter system dump datafile
SQL > '/u03/oradata/PROD/applsysd01.dbf' block 25654;
System altered.

or:

SQL> ALTER SESSION SET EVENTS
2>   'IMMEDIATE TRACE NAME BLOCKDUMP LEVEL 25654';
System altered.

This will then generate a trace file that contains the detailed information about the contents of the data block. In most cases, this will be the first block in the table (the segment header). Let’s go to the udump directory and inspect the trace file.

oracle*PROD-/u01/app/oracle/admin/PROD/udump
>ls -alt|head
total 5544
-rw-r--r--   1 oracle     dba          69816 Dec 28 14:16 ora_4443.trc

Next, we look at the contents of the trace file using the UNIX more command.

root> more ora_4443.trc

Dump file /u01/app/oracle/admin/PROD/udump/ora_4443.trc
Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
.
.
.

Block header dump: rdba: 0x00406436
 Object id on Block? Y
 seg/obj: 0x63  csc: 0x00.d3aa2  itc: 9  flg: -  typ: 2 - INDEX

Here we see that the object on this block is an index and the object ID is hex 63. We convert the hex 63 and see that our object ID is number 99.

We can then run a query against dba_objects and see the name of the index.

SQL> select object_name, object_type
  2  from dba_objects
  3  where object_id=99;

OBJECT_NAME
-----------------------------------------------------------------
OBJECT_TYPE
---------------
VUST_IDX
INDEX


SQL> select table_name from dba_indexes
  2  where index_name = 'CUST_IDX';

TABLE_NAME
------------------------------
CUSTOMER

So, here we see that our wait event was on the root index node for the cust_idx index. This index has only a single freelist, and it appears that the contention was caused by multiple tasks competing for an insert on the customer table.

Now that you see how to monitor buffer busy waits, let’s move on to see how indexes and referential integrity constraints affect Oracle DML.


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