Seconds in Wait
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 RUNDATEEVENT 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).
RE: Seconds in Wait
Ian, We looked at this statistic for several days of dedicated research. The statistic is updated by LGWR. As you've noticed, the numbers are difficult to use for two reasons: 1. It's difficult to predict when seconds_in_wait will be updated. From an individual session's perspective, the update occurs at random times. 2. The granularity is so poor (generally 3s or more) that even if you could predict the time of the next update, you wouldn't have very much information anyway. It's stuff like this that drove us away from v$ and x$ data analysis and toward the 10046 data. There are a lot of other things wrong with v$ data too; it's just that they're so hard to test that not many people notice them. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- Ian A. Sent: Friday, August 30, 2002 1:19 PM To: Multiple recipients of list ORACLE-L 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 RUNDATEEVENT 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 WAITING209 29-AUG-2002:22:42:10 SQL*Net message from dblink 30 WAITING209 29-AUG-2002:22:42:15 SQL*Net message from dblink 35 WAITING209 29-AUG-2002:22:42:21 SQL*Net message from dblink 39 WAITING209 29-AUG-2002:22:42:27 SQL*Net message from dblink 42 WAITING209 29-AUG-2002:22:42:32 SQL*Net message from dblink 47 WAITING209 29-AUG-2002:22:42:38 SQL*Net message from dblink 52 WAITING209 29-AUG-2002:22:42:43 SQL*Net message from dblink 57 WAITING209 29-AUG-2002:22:42:49 SQL*Net message from dblink 62 WAITING209 29-AUG-2002:22:42:55 SQL*Net message from dblink 64 WAITING209 29-AUG-2002:22:43:00 SQL*Net message from dblink 69 WAITING209 29-AUG-2002:22:43:06 SQL*Net message from dblink 86 WAITING209 29-AUG-2002:22:43:12 SQL*Net message from dblink 91 WAITING209 29-AUG-2002:22:43:17 SQL*Net message from dblink 96 WAITING209 --- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
RE: Seconds in Wait
: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- Ian A. Sent: Friday, August 30, 2002 1:19 PM To: Multiple recipients of list ORACLE-L 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 RUNDATEEVENT 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 WAITING209 29-AUG-2002:22:42:10 SQL*Net message from dblink 30 WAITING209 29-AUG-2002:22:42:15 SQL*Net message from dblink 35 WAITING209 29-AUG-2002:22:42:21 SQL*Net message from dblink 39 WAITING209 29-AUG-2002:22:42:27 SQL*Net message from dblink 42 WAITING209 29-AUG-2002:22:42:32 SQL*Net message from dblink 47 WAITING209 29-AUG-2002:22:42:38 SQL*Net message from dblink 52 WAITING209 29-AUG-2002:22:42:43 SQL*Net message from dblink 57 WAITING209 29-AUG-2002:22:42:49 SQL*Net message from dblink 62 WAITING209 29-AUG-2002:22:42:55 SQL*Net message from dblink 64 WAITING209 29-AUG-2002:22:43:00 SQL*Net message from dblink 69 WAITING209 29-AUG-2002:22:43:06 SQL*Net message from dblink 86 WAITING209 29-AUG-2002:22:43:12 SQL*Net message from dblink 91 WAITING209 29-AUG-2002:22:43:17 SQL*Net message from dblink 96 WAITING209 --- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- 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
