I've been sampling active SQL at a rate of 5 Hz and saving the wait information to a
table. Here is one example.
SELECT RUNDATE, EVENT, SECONDS_IN_WAIT, STATE, SEQ#
FROM STATEMENT_WAIT_INFO WHERE USERNAME = 'ARF' AND
ADDRESS = '8992E2C8' AND HASH_VALUE = 3642756738
RUNDATE EVENT SECONDS_IN_WAIT STATE
SEQ#
-------------------- ------------------------------
--------------- ------------------- ----------
29-AUG-2002:22:41:53 SQL*Net message from dblink 3 WAITING
209
29-AUG-2002:22:41:58 SQL*Net message from dblink 8 WAITING
209
29-AUG-2002:22:42:04 SQL*Net message from dblink 25 WAITING
209
29-AUG-2002:22:42:10 SQL*Net message from dblink 30 WAITING
209
29-AUG-2002:22:42:15 SQL*Net message from dblink 35 WAITING
209
29-AUG-2002:22:42:21 SQL*Net message from dblink 39 WAITING
209
29-AUG-2002:22:42:27 SQL*Net message from dblink 42 WAITING
209
29-AUG-2002:22:42:32 SQL*Net message from dblink 47 WAITING
209
29-AUG-2002:22:42:38 SQL*Net message from dblink 52 WAITING
209
29-AUG-2002:22:42:43 SQL*Net message from dblink 57 WAITING
209
29-AUG-2002:22:42:49 SQL*Net message from dblink 62 WAITING
209
29-AUG-2002:22:42:55 SQL*Net message from dblink 64 WAITING
209
29-AUG-2002:22:43:00 SQL*Net message from dblink 69 WAITING
209
29-AUG-2002:22:43:06 SQL*Net message from dblink 86 WAITING
209
29-AUG-2002:22:43:12 SQL*Net message from dblink 91 WAITING
209
29-AUG-2002:22:43:17 SQL*Net message from dblink 96 WAITING
209
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note that the state is "WAITING" so seconds_in_wait should be the Actual Wait time in
Seconds. See "Oracle Performance Tuning 101" pg 32. Note, also the seq# number. I
am intrigued by the jump from 8 seconds to 25 seconds for the seconds_in_wait column
where the clock time only advanced
six seconds. Also the jump from 69 to 86 seconds of wait_time between 10:43:00 PM
and 10:43:06 PM. Interestingly both of these great leaps are 17 seconds, and
both occurred just after the system time had changed to the next minute. These could
both be coincidences.
Any thoughts about what's going on here.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
