buffer busy waits


/* buffer busy waits; */

select *
     from v$system_event where event='buffer busy waits';

SELECT segment_name, object_type, total_buff_busy_waits
  FROM (SELECT   owner || '.' || object_name AS segment_name, object_type,
                 VALUE AS total_buff_busy_waits
            FROM v$segment_statistics
           WHERE statistic_name IN ('buffer busy waits')
        ORDER BY total_buff_busy_waits DESC)
WHERE ROWNUM <= 10;
__________________________________________________________________
  SELECT p1 "File", p2 "Block", p3 "Reason"
    FROM v$session_wait
   WHERE event='buffer busy waits';
___________________________________________________________________
SELECT   s.SID, username, osuser, s.status, event, row_wait_obj#,
         row_wait_file#, row_wait_block#, row_wait_row#, object_type, p3 Reasoncode, owner,
         object_name, data_object_id,
         CASE object_type
            WHEN 'TABLE'
               THEN    'select * from '
                    || owner
                    || '.'
                    || object_name
                    || ' where rowid = DBMS_ROWID.ROWID_CREATE(1,'
                    || data_object_id
                    || ','
                    || row_wait_file#
                    || ','
                    || row_wait_block#
                    || ','
                    || row_wait_row#
                    || ');'
            ELSE ''
         END "Query to find waited row"
    FROM v$session_wait w, v$session s, dba_objects o
   WHERE w.SID = s.SID AND object_id = row_wait_obj# AND event = 'buffer busy waits'
ORDER BY 6, 7, 8, 9;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: