Troubleshooting BUFFER BUSY WAITS

Troubleshooting BUFFER BUSY WAITS:

=======================================================

Note:

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.

BUFFER BUSY WAITS COUNT ON DATABASE:

set pagesize 5000

set lines 180

set long 5000

col username for a15

col osuser for a15

col program for a20

col "LOGON_TIME" for a23

col status for a8

col machine for a15

col SQL_TEXT for a90

col EVENT for a50

col P1TEXT for a10

col P2TEXT for a10

col P3TEXT for a10

col p1 for 9999999999999

col p2 for 9999999999999

col p3 for 9999999999999

col "LAST_CALL_HRS" for 99999.999

select event,count(event) "BUFFER_BUSY_WAITS/LOCK_COUNT" from v$session_wait having count(event)>= 1 and event like '%buffer busy waits%' group by event;

BUFFER_BUSY_WAITS SESSIONS DETAIL:

col event for a10

select s.sid,username,osuser,program,machine,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",last_call_et/3600 "LAST_CALL_HRS",sw.event from

v$session s,v$session_wait sw where s.sid=sw.sid and sw.event like '%buffer busy waits%';

SQL_TEXT OF BUFFER_BUSY_WAITS SESSIONS:

col "EVENT" for a25

select s.sid,username "USERNAME",sql_text "SQL_TEXT",sw.event "EVENT" from v$session s,v$session_wait sw,v$sqltext sq where s.sid=sw.sid and

sq.address = s.sql_address and sw.event like '%buffer busy waits% order by sw.sid,piece';

TYPE_OF_SEGMENT_CONTENDED_FOR

SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;

USE THE BELOW SQL_FILE TO IDENTIFY THE SEGMENT

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

SELECT owner,segment_name,segment_type FROM dba_extents WHERE file_id=&file AND &block_id BETWEEN block_id AND block_id + blocks -1 ;