Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Seconds in Wait

RE: Seconds in Wait

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 30 Aug 2002 16:28:20 -0800
Message-ID: <F001.004C4B3E.20020830162820@fatcity.com> 





I'll say! Here's another example:





-----Original Message-----



Sent: Friday, August 30, 2002 3:18 PM

RUNDATE              ADDRESS  HASH_VALUE EVENT                          SECONDS_IN_WAIT  WAIT_TIME


-------------------- -------- ---------- ------------------------------ --------------- ----------

STATE                     SEQ#


------------------- ----------

29-AUG-2002:23:37:02 8480D570 2197023202 SQL*Net message from dblink                204          0
WAITING                   1839

29-AUG-2002:23:37:08 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:37:14 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839


RUNDATE              ADDRESS  HASH_VALUE EVENT                          SECONDS_IN_WAIT  WAIT_TIME


-------------------- -------- ---------- ------------------------------ --------------- ----------

STATE                     SEQ#


------------------- ----------

29-AUG-2002:23:37:20 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:37:26 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:37:32 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839


RUNDATE              ADDRESS  HASH_VALUE EVENT                          SECONDS_IN_WAIT  WAIT_TIME


-------------------- -------- ---------- ------------------------------ --------------- ----------

STATE                     SEQ#


------------------- ----------

29-AUG-2002:23:37:37 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:37:43 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:37:49 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839


RUNDATE              ADDRESS  HASH_VALUE EVENT                          SECONDS_IN_WAIT  WAIT_TIME


-------------------- -------- ---------- ------------------------------ --------------- ----------

STATE                     SEQ#


------------------- ----------

29-AUG-2002:23:37:55 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:38:01 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:38:07 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839


RUNDATE              ADDRESS  HASH_VALUE EVENT                          SECONDS_IN_WAIT  WAIT_TIME


-------------------- -------- ---------- ------------------------------ --------------- ----------

STATE                     SEQ#


------------------- ----------

29-AUG-2002:23:38:13 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:38:18 8480D570 2197023202 SQL*Net message from dblink                205          0
WAITING                   1839

29-AUG-2002:23:38:24 8480D570 2197023202 SQL*Net message from dblink                283          0
WAITING                   1839



------------------------------------------------------------------------------------------------------------------------






Note mcu time passed  before seconds_in_wait was updated.  Sheesh, strange values from v$session_wait.  Invalid resource usage statistics over time because statements aged out of cache  and the counters were reset is another big problem when trying to  formulate trends.



I had formed a theory that one needs to look at three things in tuning, the waits, the resources used, and the query plan.



One cannot trust the v$/x$ objects for an accurate portrayal of the first two.   There are problems with the third as well but the v$sql_plan table may take care of that.



Ian MacGregor


Stanford Linear Accelerator Center


ian_at_slac.stanford.edu    



To: Multiple recipients of list ORACLE-L




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




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


ian_at_slac.stanford.edu






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com

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: ListGuru_at_fatcity.com (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: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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).


Received on Fri Aug 30 2002 - 19:28:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US