Resources waits

REM # Creator: Vincent Fenoll – DBA Oracle Montreal

REM ################################################
REM # Created: 2005/01/01
REM # Name: resources_waits.sql
REM  ################################################
REM #
REM # Compatible: Oracle 7 8i 9i 10g 11g
REM#
REM ################################################
REM #
REM # Display importants waits events in our Oracle databases in Montreal
REM #
REM ################################################
column "Average wait cs" format 9999990.00 heading "Attente moyenne|en cent-secondes"
column "Time waited seconds" format 999,999,999,990.00 heading "Temps attendu|en secondes"
colum event format a35 heading "Evenement" truncate
select
  substr(e.event, 1, 40)  event,
  e.time_waited/100 "Time waited seconds",
  e.time_waited / decode(
    e.event,
    'latch free', e.total_waits,
    decode(
      e.total_waits - e.total_timeouts,
      0, 1,
      e.total_waits - e.total_timeouts
    )
  ) "Average wait cs"
from
  sys.v_$system_event  e,
  sys.v_$instance  i
where
  e.event = 'buffer busy waits' or
  e.event = 'enqueue' or
  e.event = 'free buffer waits' or
  e.event = 'global cache freelist wait' or
  e.event = 'latch free' or
  e.event = 'log buffer space' or
  e.event = 'parallel query qref latch' or
  e.event = 'pipe put' or
  e.event = 'write complete waits' or
  e.event like 'library cache%' or
  e.event like 'log file switch%' or
  e.event = 'log file sync' or
  ( e.event = 'row cache lock' and
    i.parallel = 'NO'
  )
order by "Time waited seconds" desc
/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.