Oracle waiting SQL Tips

Question:  I have an SQL statement that is waiting for resources and the SQL is "hung" waiting.  How can I identify Oracle waiting SQL?

Answer: Oracle SQL can be waiting for a variety of reasons, and SQL Wait event analysis for Oracle cane be broken down into three areas:

• Time-based SQL waiting event analysis - The Oracle AWR/STATSPACK utility can be used to show trends in wait event over long time periods, and fluctuations in waits can often provide useful information.

• System-wide waiting SQL analysis - You can start at a high level and show scripts that can be used to track system wide events that cause SQL waiting.

• Session-level SQL wait events - These are real-time SQL's waiting at the exact moment that you run the script. The v$session_wait view displays information about wait events for which active SQL is are currently waiting.

If you want to display Top I/O SQL waits, the script below joins v$active_session_history to v$event_name to display the SQL ID for all waiting Oracle SQL statements that are waiting on User I/O.

Here is a script for Oracle waiting SQL statements, showing the waits associated with the top SQL:

select
   WAIT_CLASS,
   TOTAL_WAITS,
   round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,
   TIME_WAITED,
   round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from
(select WAIT_CLASS,
   TOTAL_WAITS,
   TIME_WAITED
 from
see code depot for full script
   V$SYSTEM_WAIT_CLASS
where
   WAIT_CLASS != 'Idle'),
(select
   sum(TOTAL_WAITS) SUM_WAITS,
   sum(TIME_WAITED) SUM_TIME
from
   V$SYSTEM_WAIT_CLASS
where
   WAIT_CLASS != 'Idle')
order by 5 desc;

This SQL waiting script below is tricky because it must be running at the exact moment of a SQL wait. Some Oracle professionals run this script every 60 seconds, sending an e-mail when an important SQL wait occurs:

clear columns
set pages 999
set lines 100

col c1 heading 'SID' format 999
col c2 heading 'User|Name' format a16
col c3 heading 'Event|Name' format a10
col c4 heading 'Secs|Wait' format 9.99
col c5 heading 'Wait|Time' format 9.99
col c6 heading 'state' format a10
col c7 heading 'P1 text' format a10
col c8 heading 'P1|Val' format 999,999,999
col c9 heading 'P1|Raw' format a10
col c10 heading 'P2|Text' format a10
col c11 heading 'P2|Val' format 999,999,999
col c12 heading 'P2|Raw' format a10
col c13 heading 'P3|Text' format a10
col c14 heading 'P3|Val' format 999,999,999
col c15 heading 'P3|Raw' format a10


ttitle 'Individual process wait times'

SELECT
a.sid c1,
decode(
b.username,
NULL,c.name,
b.username) c2,
a.event c3,
a.seconds_in_wait c4,
a.wait_time c5,
a.state c6,
a.p1text c7,
a.p1 c8,
-- a.p1raw c9,
a.p2text c10,
a.p2 c11,
-- a.p2raw c12,
a.p3text c13,
a.p3 c14
-- a.p3raw c15
FROM
sys.v_$session_wait a,
sys.v_$session b,
sys.v_$bgprocess c
where
SEE CODE DEPOT FOR LICENSED SCRIPT DOWNLOADS
and
event NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by
4 desc
;

For the full Oracle SQL waiting scripts, see the see code depot download for Oracle.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.