[Script] waitevent.sql
- 05/07/2016/
- Performance, Diagnostics & Tuning
-- Verifica as operações atuais e seus respectivos eventos de espera.
-- Leva em consideração os Waits onde WAIT_CLASS não é IDLE - CUSTOMIZADO PARA O EXADATA (Verifica OFFLOAD - Smart Scan).
-- Ultimas linhas possuem LAST_CALL_ET maior (Esta executando mais tempo).
set timing on
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
SET PAGES 200
SET LINES 200
COL "SID" 9999 Head "Session ID|SID" JUSTIFY center
COL "SERIAL#" 9999 Head "Session |Serial#" JUSTIFY center
COL EVENT FORMAT A30 Head "Wait Event|Evento de Espera" JUSTIFY center
COL "SQL TEXT" FORMAT A65 word_wrapped HEADING " | SQL or PL/SQL TEXT | " JUSTIFY center
COL "SQLID_CHILDNUMBER" FORMAT A15 Head "SQL ID|Child Number" JUSTIFY center
COL "SID SERIAL#" FORMAT A11 Head "SID|SERIAL#" JUSTIFY center
COL "PGA USED MB" FORMAT 99999 Head "PGA|USED|MB" JUSTIFY center
COL "TEMP USED MB" FORMAT 99999 Head "TEMP|USED|MB" JUSTIFY center
COL "Offload" FORMAT A10 Head "EXADATA|Smart SCAN|OFFLOAD" JUSTIFY center
COL "SEC_IN_WAIT" FORMAT 999 Head "Seconds|IN|Wait" JUSTIFY center
COL "LAST_CALL_ET" FORMAT 99999 Head "Last|Call|ET" JUSTIFY center
COL "BLCKSESS" FORMAT 99999 Head "BLCKSESS" JUSTIFY center
COL "OS PID" FORMAT A5 Head "OS|PID" JUSTIFY center
COL "SECONDS WAITED" FORMAT a6 Head "SEC|WAITED" JUSTIFY center
SELECT -- S.SID "SID", S.SERIAL# "SERIAL#" ,
S.SID|| ',' ||S.SERIAL# "SID SERIAL#" ,
to_char(P.SPID) "OS PID" ,
W.EVENT,
S.BLOCKING_SESSION as BLCKSESS,
SQLT.SQL_TEXT "SQL TEXT",
S.SQL_ID|| ':' ||S.SQL_CHILD_NUMBER "SQLID_CHILDNUMBER" ,
ROUND(p.pga_used_mem/(1024*1024), 2) as "PGA USED MB",
round(u.blocks * 8 / 1024) as "TEMP USED MB",
W.SECONDS_IN_WAIT "SEC_IN_WAIT",
LAST_CALL_ET,
decode(SQL.IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload
FROM V$SESSION_WAIT W,
V$PROCESS P,
V$SQLTEXT SQLT ,
-- V$SQLTEXT_WITH_NEWLINES SQLT ,
V$SQL SQL,
v$session_event SE,
V$SESSION S left join v$sort_usage u on s.saddr = u.session_addr -- COM TEMP
-- V$SESSION S -- SEM TEMP
WHERE
-- W.SID = S.SID AND -- Cruzamento V$SESSION_WAIT x V$SESSION
-- W.WAIT_CLASS != 'Idle'
S.PADDR = P.ADDR -- Cruzamento V$SESSION x V$PROCESS
AND (SE.SID = S.SID and SE.SID = W.SID and W.SID = S.SID) -- Cruzamento V$SESSION_EVENT x V$SESSION x V$SESSION_WAIT
AND (SQLT.SQL_ID = SQL.SQL_ID and SQLT.HASH_VALUE = SQL.HASH_VALUE) -- Cruzamento V$SQLTEXT x V$SQL
AND (S.SQL_ID = SQL.SQL_ID and S.SQL_HASH_VALUE = SQL.HASH_VALUE and S.SQL_CHILD_NUMBER = SQL.CHILD_NUMBER) -- Cruzamento V$SESSION x V$SQL
AND (SQLT.SQL_ID = S.SQL_ID and SQLT.HASH_VALUE = S.SQL_HASH_VALUE and SQLT.ADDRESS = S.SQL_ADDRESS) -- Cruzamento V$SQLTEXT x V$SESSION
AND (SQLT.ADDRESS = S.SQL_ADDRESS and SQL.ADDRESS = S.SQL_ADDRESS and SQLT.ADDRESS = SQL.ADDRESS)
AND (SQLT.HASH_VALUE = S.SQL_HASH_VALUE and SQL.HASH_VALUE = S.SQL_HASH_VALUE and SQLT.HASH_VALUE = SQL.HASH_VALUE)
AND (SQLT.SQL_ID = S.SQL_ID and SQLT.HASH_VALUE = S.SQL_HASH_VALUE and SQLT.ADDRESS = S.SQL_ADDRESS)
AND (SE.EVENT=W.EVENT)
AND (W.WAIT_CLASS=SE.WAIT_CLASS AND W.WAIT_CLASS != 'Idle' AND SE.WAIT_CLASS != 'Idle')
AND SQLT.PIECE < 2 -- Pedaco query
--------
-- AND S.PROGRAM='w3wp.exe'
-- AND W.SID=1902 -- <<<<< Especificar SID
-- AND S.SQL_ID='8jqjz14pvkvsx' -- <<<<< Especificar SQL_ID
---------
ORDER BY
S.LAST_CALL_ET , W.SID , SQLT.PIECE ;