RE: waits vs. logons

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 04 Sep 2002 16:23:29 -0800
Message-ID: <F001.004C7FAB.20020904162329@fatcity.com>

Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

First, you're missing user-mode CPU time consumed by the Oracle session. The statistic that is supposed to hold this number is 'CPU used by this session' from v$sesstat. But there are bugs in that statistic. Second, some of the time that an Oracle process spends sleeping (because of context switches imposed by a timesharing operating system) isn't counted either. See
www.hotsos.com/dnloads/1/kevents/unaccounted-for.html for a start. Our Hotsos Clinic explains in complete detail.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic <http://www.hotsos.com/training/clinic> , Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium> on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum <http://www.miracleas.dk> , Sep 20-22 Middlefart Denmark

-----Original Message-----
Tom
Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L

I run the following query to compare the total waits for a session verses the seconds logged on:

select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from
  (select sid, (sysdate - logon_time)*24*60*60 ontime    from v$session ) a,
  (select sid, sum(time_waited)/100 waittime    from v$session_event
   group by sid) b
where a.sid = b.sid;

I get the following results:

SID LONGON_SECS WAIT_SECS
---------- ----------- ----------

         1      595995     595989
         2      595994     471204
         3      595994     595585
         4      595994     594580
         5      595994     595492
         6      595994     593639
         9      595993     577157
        14        4943       1303
        24      595844     588441
        26        1733       1728
        27        5308       2478
        29      517269          0
        32          53          0
        35      415158         13
        50      140478     140371
        51         719        507
        56       14507       8706
        59        2269        221

I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what?

Tom

------=_NextPart_000_01A2_01C2543F.741BB180 Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">

<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">
<title>Message</title>

<style>
<!--

/* Font Definitions */
@font-face

	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
span.EmailStyle17
	{font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}

-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

First, you’re missing = user-mode CPU time
consumed by the Oracle session. The statistic that is supposed to hold = this
number is ‘CPU used by this session’ from v$sesstat. But = there are
bugs in that statistic. Second, some of the time that an Oracle process = spends
sleeping (because of context switches imposed by a timesharing operating = system)
isn’t counted either. See <a
href=3D"http://www.hotsos.com/dnloads/1/kevents/unaccounted-for.html">www= .hotsos.com/dnloads/1/kevents/unaccounted-for.html</a> for a start. Our Hotsos Clinic explains in complete = detail.

<div>

Cary = Millsap 
Hotsos Enterprises, Ltd. 
<a href=3D"http://www.hotsos.com">http://www.hotsos.com</a> 
 

Upcoming events: 
- <a href=3D"http://www.hotsos.com/training/clinic">Hotsos Clinic</a>, = Oct
1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 = Honolulu 
- <a href=3D"http://www.hotsos.com/events/symposium">2003 Hotsos = Symposium</a> on
Oracle® System Performance, Feb 9–12 Dallas - Next event: <a href=3D"http://www.miracleas.dk">Miracle Database = Forum</a>, Sep
20–22 Middlefart Denmark

</div>

-----Original Message----- 
From: root_at_fatcity.com
[mailto:root_at_fatcity.com] On Behalf =

Of </span></b>Terrian,
Tom<br>

Sent: Wednesday, =
September 04,
2002 4:00 PM 
To: Multiple recipients =
of list
ORACLE-L 
Subject: waits vs. =
logons

<div>

I run the following query to compare the total waits = for a
session verses the seconds logged on:

</div>

<div>

</div>

<div>

select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs 
from

&nbsp; (select sid, (sysdate - logon_time)*24*60*60&nbsp; ontime<br>
&nbsp;&nbsp; from&nbsp;&nbsp; v$session ) a,<br>
&nbsp; (select sid, sum(time_waited)/100 waittime<br>
&nbsp;&nbsp; from v$session_event <br>
&nbsp;&nbsp; group by sid) b<br>

where a.sid =3D b.sid;

</div>

<div>

I get the following results:

</div>

<div>

</div>

<div>

      
SID LONGON_SECS  WAIT_SECS 
---------- ----------- ---------- 
         1      595995     = 595989 
         2      595994     = 471204 
         3      595994     = 595585 
         4      595994     = 594580 
         5      595994     = 595492 
         6      595994     = 593639 
         9      595993     = 577157 
        14        4943       1303         = 24     
595844     588441         26        1733       1728         27        5308       2478         = 29     
517269          0         32          53          0         = 35     
415158         13         = 50     
140478     140371         51         719        507         56      
14507       8706         59        2269        221

</div>

<div>

</div>

<div>

I am very much a novice when it comes to wait statistics.  When a session, for example #51, has been logged on = for 719
seconds but has experienced only 507 wait seconds, what did it do the = rest of
the time?  I am assuming some kind of work?  Any way to = determine
what?

</div>

<div>

</div>

<div>

Tom

</div>

<div>

</div>

<div>

</div>

<div>

</div>

</body>

</html>

------=_NextPart_000_01A2_01C2543F.741BB180--

-- 
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).

Received on Wed Sep 04 2002 - 19:23:29 CDT