RE: Computing resource consumption

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 25 Jul 2003 10:20:12 -0500
Message-Id: <26013.339621@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_005A_01C35296.5926E880 Content-Type: text/plain;

charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

You have a good point. I'd expect that most bg workload performed on = behalf
of a session is probably writes, so measuring redo generation might be = an
excellent addition. It also encourages users (and hence, indirectly, applications developers) to be economical with their redo generation, = which
is good too. (One example of an application that generates redo when it doesn't need to is an UPDATE that changes a column to a value it already had.)

=20

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

Upcoming events:
- Hotsos Clinic <http://www.hotsos.com/training/clinic101> 101 in =
Denver,
Sydney
- Hotsos Symposium 2004 <http://www.hotsos.com/events/symposium/2004> =
March
7-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf Of Jamadagni, Rajendra
Sent: Friday, July 25, 2003 8:30 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Computing resource consumption

=20

Thanks Cary,=20

thanks, but here is a question ... you say capturing physical IO is redundant ... what about REDO generated by session? Isn't that part of physical writes? If I just concentrate on LIO (I am currently using = "Logical
IO' (statistic#9 in 9202).

Currently I am concentrating on following statistics to create a matrix=20

  9 -- session logical reads=20
 12 -- CPU used by this session =20
 20 -- session pga memory=20
 21 -- session pga memory max=20
 42 -- physical reads=20
 46 -- physical writes=20
115 -- redo size=20
236 -- bytes sent via SQL*Net to client=20
237 -- bytes received via SQL*Net from client=20

not sure if all of them will matter in the end, but at-least it is a = start
for me.=20

Another problem is capturing the work done by background processes on = behalf
of these sessions ... how does one go about capturing that workload??

Thanks in advance=20
Raj=20

-------------------------------------------------------------------------=

---
----=20
Rajendra dot Jamadagni at nospamespn dot com=20
All Views expressed in this email are strictly personal.=20
QOTD: Any clod can have facts, having an opinion is an art !=20
-----Original Message-----=20
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]=20
Sent: Friday, July 25, 2003 1:24 AM=20
To: Multiple recipients of list ORACLE-L=20
Subject: RE: Computing resource consumption=20

=20

Raj,=20

A pretty common (and actually pretty accurate) charge back unit is the =
LIO.
You can get this information by using the standard AUDIT CONNECT =
feature,
and using the LREAD value as your basis. If you wanted to get fancy, you
might also charge by the parse call (which you're already collecting in =
your
V$SESSTAT query). The reason I'd focus on these two metrics is because =
these
are the two operations on an Oracle system that absolutely prevent the
system from scaling.

You could count physical I/Os as well, but that would be redundant if =
you're
already catching LIO call counts.=20

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

Upcoming events:=20


- Hotsos Clinic 101 in Denver, Sydney=20

- Hotsos Symposium 2004 March 7-10 Dallas=20

- Visit www.hotsos.com for schedule details...=20

-----Original Message-----=20
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf Of
Jamadagni, Rajendra=20
Sent: Thursday, July 24, 2003 12:04 PM=20
To: Multiple recipients of list ORACLE-L=20
Subject: Computing resource consumption=20

Does anyone know any papers or techniques to compute resource =
consumption by
users in a DB systems? This may or may not be for computing charge-back =
to
the client, but my questions are=20

1. What do you compute?=20
2. are there any standard methods and or standard formula?=20
I am collecting v$sesstat when a session exits, but is data alone from
session stats sufficient? How about the work done by background =
processes on
user's behalf?=20

Do you do anything like this at your workplace? This is something that =
might
be coming down the line, so I have been asked to start looking for =
related
stuff.

Thanks in advance=20
Raj=20
-------------------------------------------------------------------------=
---
----=20
Rajendra dot Jamadagni at nospamespn dot com=20
All Views expressed in this email are strictly personal.=20
QOTD: Any clod can have facts, having an opinion is an art !=20


------=_NextPart_000_005A_01C35296.5926E880
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//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>RE: Computing resource consumption</title>

<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Times;
	panose-1:2 2 6 3 5 4 5 2 3 4;}
@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";}
h2
	{margin-top:12.0pt;
	margin-right:0in;
	margin-bottom:3.0pt;
	margin-left:0in;
	page-break-after:avoid;
	font-size:12.0pt;
	font-family:Arial;
	color:#333333;
	font-weight:bold;}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:blue;
	text-decoration:underline;}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
p.SidebarBody, li.SidebarBody, div.SidebarBody
	{margin-top:4.0pt;
	margin-right:.25in;
	margin-bottom:4.0pt;
	margin-left:.25in;
	text-align:justify;
	line-height:12.0pt;
	border:none;
	padding:0in;
	font-size:10.0pt;
	font-family:Times;
	color:teal;}
p.Body, li.Body, div.Body
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:6.0pt;
	margin-left:0in;
	font-size:9.0pt;
	font-family:Arial;}
span.EmailStyle20
	{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=3Dblue>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>You have a good point. I&#8217;d =
expect
that most bg workload performed on behalf of a session is probably =
writes, so measuring
redo generation might be an excellent addition. It also encourages users =
(and
hence, indirectly, applications developers) to be economical with their =
redo generation,
which is good too. (One example of an application that generates redo =
when it
doesn&#8217;t need to is an UPDATE that changes a column to a value it =
already
had.)</span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>&nbsp;</span></font></p>

<div>

<p><b><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:10.0pt;
 font-family:Arial;color:navy;font-weight:bold'>Cary =
Millsap</span></font></b><font
size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial;
color:navy'><br>
Hotsos Enterprises, Ltd.<br>
<a href=3D"http://www.hotsos.com">http://www.hotsos.com</a><br>
<br>
Upcoming events:<br>


- <a href=3D"http://www.hotsos.com/training/clinic101">Hotsos =

Clinic&nbsp;101</a>
in Denver, Sydney<br>


- <a href=3D"http://www.hotsos.com/events/symposium/2004">Hotsos =

Symposium 2004</a>
March 7&#8211;10 Dallas<br>


- Visit <a href=3D"http://www.hotsos.com">www.hotsos.com</a> for =

schedule
details...</span></font></p>

</div>

<p class=3DMsoNormal><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma'>-----Original Message-----<br>
<b><span style=3D'font-weight:bold'>From:</span></b> =
ml-errors_at_fatcity.com
[mailto:ml-errors_at_fatcity.com] <b><span style=3D'font-weight:bold'>On =
Behalf Of </span></b></span></font><font
 size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;font-family:Tahoma'>Jamadagni,
 Rajendra</span></font><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma'><br>
<b><span style=3D'font-weight:bold'>Sent:</span></b> Friday, July 25, =
2003 8:30
AM<br>
<b><span style=3D'font-weight:bold'>To:</span></b> Multiple recipients =
of list
ORACLE-L<br>
<b><span style=3D'font-weight:bold'>Subject:</span></b> RE: Computing =
resource
consumption</span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Thanks
Cary,</span></font> </p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>thanks,
but here is a question ... you say capturing physical IO is redundant =
... what
about REDO&nbsp; generated by session? Isn't that part of physical =
writes? If I
just concentrate on LIO (I am currently using &quot;Logical IO' =
(statistic#9 in
9202).</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Currently
I am concentrating on following statistics to create a =
matrix</span></font> </p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>&nbsp; 9
-- session logical reads </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>&nbsp;12 -- CPU used by =
this
session&nbsp; </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>&nbsp;20 -- session pga =
memory </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>&nbsp;21 -- session pga =
memory max </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>&nbsp;42 -- physical =
reads </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>&nbsp;46 -- physical =
writes </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>115 -- redo size =
</span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>236 -- bytes sent via =
SQL*Net to
client </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>237 -- bytes received =
via SQL*Net
from client </span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>not sure
if all of them will matter in the end, but at-least it is a start for =
me. </span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Another
problem is capturing the work done by background processes on behalf of =
these
sessions ... how does one go about capturing that =
workload??</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Thanks in
advance</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>Raj</span></font> <br>
<font size=3D2><span =
style=3D'font-size:10.0pt'>----------------------------------------------=
----------------------------------
</span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>Rajendra dot Jamadagni =
at
nospamespn dot com </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>All Views expressed in =
this email
are strictly personal. </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>QOTD: Any clod can have =
facts,
having an opinion is an art ! </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>-----Original =
Message-----</span></font>
<br>
<font size=3D2><span style=3D'font-size:10.0pt'>From: Cary Millsap [<a
href=3D"mailto:cary.millsap_at_hotsos.com">mailto:cary.millsap_at_hotsos.com</a=
>]</span></font>
<br>
<font size=3D2><span style=3D'font-size:10.0pt'>Sent: Friday, July 25, =
2003 1:24 AM</span></font>
<br>
<font size=3D2><span style=3D'font-size:10.0pt'>To: Multiple recipients =
of list
ORACLE-L</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>Subject: RE: Computing =
resource
consumption</span></font> </p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Raj,</span></font>
</p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>A pretty
common (and actually pretty accurate) charge back unit is the LIO. You =
can get
this information by using the standard AUDIT CONNECT feature, and using =
the
LREAD value as your basis. If you wanted to get fancy, you might also =
charge by
the parse call (which you're already collecting in your V$SESSTAT =
query). The
reason I'd focus on these two metrics is because these are the two =
operations
on an Oracle system that absolutely prevent the system from =
scaling.</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>You could
count physical I/Os as well, but that would be redundant if you're =
already
catching LIO call counts.</span></font> </p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Cary
Millsap</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>Hotsos Enterprises, =
Ltd.</span></font>
<br>
<font size=3D2><span style=3D'font-size:10.0pt'><a =
href=3D"http://www.hotsos.com"
target=3D"_blank">http://www.hotsos.com</a></span></font> </p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Upcoming
events:</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>- Hotsos Clinic 101 in =
Denver,
Sydney</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>- Hotsos Symposium 2004 =
March 7-10
Dallas</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>- Visit www.hotsos.com =
for schedule
details...</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>-----Original =
Message-----</span></font>
<br>
<font size=3D2><span style=3D'font-size:10.0pt'>From: =
ml-errors_at_fatcity.com [<a
href=3D"mailto:ml-errors_at_fatcity.com">mailto:ml-errors_at_fatcity.com</a>] =
On Behalf
Of Jamadagni, Rajendra</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>Sent: Thursday, July 24, =
2003 12:04
PM</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>To: Multiple recipients =
of list
ORACLE-L</span></font> <br>
<font size=3D2><span style=3D'font-size:10.0pt'>Subject: Computing =
resource
consumption</span></font> </p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Does
anyone know any papers or techniques to compute resource consumption by =
users
in a DB systems? This may or may not be for computing charge-back to the
client, but my questions are </span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>1. What
do you compute? </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>2. are there any =
standard methods
and or standard formula? </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>I am collecting =
v$sesstat when a
session exits, but is data alone from session stats sufficient? How =
about the
work done by background processes on user's behalf? </span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Do you do
anything like this at your workplace? This is something that might be =
coming
down the line, so I have been asked to start looking for related =
stuff.</span></font></p>

<p><font size=3D2 face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Thanks in
advance </span></font><br>
<font size=3D2><span style=3D'font-size:10.0pt'>Raj </span></font><br>
<font size=3D2><span =
style=3D'font-size:10.0pt'>----------------------------------------------=
----------------------------------

Received on Fri Jul 25 2003 - 10:20:12 CDT