DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=tLiY3Y0lApOimXcszJxxCXt64/MGp+a98tKpAxsSytT0lbLnKQSacUflSWcRzYAeHAh6HPgyS+7AKz0jj607dagOH3JnEbqh4HFON8eZpzHpuRmnJ5UdX4udZ6rmmeOEc4ZTKXPz6giq8+A+CeFhJQ1p9Uz2e0ZlZ8Rh0r4DBZA=
Message-ID: <de807caa0711220729j75d296f1lfffa70a3ce6fa4f1@mail.gmail.com>
Date: Thu, 22 Nov 2007 09:29:52 -0600
From: "Dennis Williams" <oracledba.williams@gmail.com>
To: rjamya <rjamya@gmail.com>
Subject: Re: CPU load per db instance
Cc: "Oracle Discussion List" <oracle-l@freelists.org>
In-Reply-To: <9177895d0711211928w1b9c3f76gf9cadf9b01b2ee97@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_28785_9039698.1195745392556"
References: <9177895d0711211207m3f024d34ua8fc57aac2b51f33@mail.gmail.com>
	 <de807caa0711211327x150e3168jf0af2f1046b9a206@mail.gmail.com>
	 <9177895d0711211928w1b9c3f76gf9cadf9b01b2ee97@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: oracledba.williams@gmail.com
------=_Part_28785_9039698.1195745392556
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Raj,

For this situation, what I've done is:
   - Use Unix tools such as top to locate the processes that are consuming
the most CPU.
   - Figure out which Oracle instance these processes relate to. Often the
instance name will be in the process name. If not, look to the parent
process.
   - Once you have the instance, you can use STATSPACK, V$ tables, etc. to
diagnose the problem.
   - Punish the miscreant (this is how DBAs get their pleasure).

Have a Happy Thanksgiving (assuming you are in the U.S.).
Dennis Williams


On 11/21/07, rjamya <rjamya@gmail.com> wrote:
>
> Thanks Dennis ...
>
> No, the aim is not for capacity planning ... when i get called to look at
> a server that run at 100% CPU for more than an hour, first thing i want to
> see which of the instances _could_ be culprit. (it happened and i managed to
> bring down CPU usage after tuning a couple of queries and adding an index on
> one of the instances, so server is about 60-80% loaded now, still working on
> other parts).
>
> But just wanted to see if something like this existed. I was planning to
> install LTOM, but unable to do so until java on server is upgraded. To get
> that java upgrade quickly has less chances than Congress passing the AMT
> concession bill in the next week. :)
>
> Yes, statspack is taking snapshots, and will probably hack a script to
> collect the information that puts together info from ps/vmstat/v$session
> etc, eventually. Zones isn't a possibility as it is sol8, 4dual core cpus,
> runs 3 (normally 6) 9206 instances.
>
> So, yes will eventually write the script, but wanted to see if anyone had
> already invented the wheel.
>
> Thanks and regards
> Raj
>
> On Nov 21, 2007 4:27 PM, Dennis Williams < oracledba.williams@gmail.com>
> wrote:
>
> > Raj,
> >
> > A couple of thoughts for you and hopefully someone else has better
> > suggestions. One question is what is your goal. Are you just wanting a rough
> > idea for capacity planning or if you are going to bill customers based on
> > this. If the latter, you'll want a more bulletproof solution whose integrity
> > you can defend.
> >    I'm not sure if STATSPACK will help much because it runs within the
> > Oracle environment. I think you need something at the Solaris level.
> >     O.S. tools often track usage by O.S. user, so you could install each
> > Oracle instance in different Unix username, but most of us DBAs consider
> > that nonstandard. In Solaris 10, you could investigate Zones, but I'm not
> > sure if Oracle 10g supports Zones yet.
> >     Solaris 10 includes DTrace and I'm guessing that might be able to do
> > what you want. It is a very powerful tool for this type of work. But you'll
> > probably have to ask a forum for Solaris like the Usenet Newsgroup
> > comp.unix.solaris.
> >     A simple-minded approach would be to look at your processes
> > associated with each instance and see if there is anything unique about
> > them, using the "ps" command. Then you could write a Unix script that would
> > execute ps and categorize the processes by Oracle instance and sum the CPU
> > load for that instance. Run that every few minutes as a cron job and
> > accumulate an estimate of usage over time. Crude but I think doable.
> >
> > Dennis Williams
> >
>
>
>
> --
> -----
> Best regards
> RJamya

------=_Part_28785_9039698.1195745392556
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>Raj,</div>
<div>&nbsp;</div>
<div>For this situation, what I&#39;ve done is:</div>
<div>&nbsp;&nbsp; - Use Unix tools such as top to locate the processes that are consuming the most CPU.</div>
<div>&nbsp;&nbsp; - Figure out which Oracle instance these processes relate to. Often the instance name will be in the process name. If not, look to the parent process.</div>
<div>&nbsp;&nbsp; - Once you have the instance, you can use STATSPACK, V$ tables, etc. to diagnose the problem.</div>
<div>&nbsp;&nbsp; - Punish the miscreant (this is how DBAs get their pleasure).</div>
<div>&nbsp;</div>
<div>Have a Happy Thanksgiving (assuming you are in the U.S.).</div>
<div>Dennis Williams<br><br>&nbsp;</div>
<div><span class="gmail_quote">On 11/21/07, <b class="gmail_sendername">rjamya</b> &lt;<a href="mailto:rjamya@gmail.com">rjamya@gmail.com</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Thanks Dennis ...<br><br>No, the aim is not for capacity planning ... when i get called to look at a server that run at 100% CPU for more than an hour, first thing i want to see which of the instances _could_ be culprit. (it happened and i managed to bring down CPU usage after tuning a couple of queries and adding an index on one of the instances, so server is about 60-80% loaded now, still working on other parts). 
<br><br>But just wanted to see if something like this existed. I was planning to install LTOM, but unable to do so until java on server is upgraded. To get that java upgrade quickly has less chances than Congress passing the AMT concession bill in the next week. :) 
<br><br>Yes, statspack is taking snapshots, and will probably hack a script to collect the information that puts together info from ps/vmstat/v$session etc, eventually. Zones isn&#39;t a possibility as it is sol8, 4dual core cpus, runs 3 (normally 6) 9206 instances. 
<br><br>So, yes will eventually write the script, but wanted to see if anyone had already invented the wheel.<br><br>Thanks and regards<br>Raj 
<div><span class="e" id="q_1166566298708efc_1"><br><br>
<div class="gmail_quote">On Nov 21, 2007 4:27 PM, Dennis Williams &lt;<a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:oracledba.williams@gmail.com" target="_blank"> oracledba.williams@gmail.com</a>
&gt; wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<div>Raj,<br>&nbsp;</div>
<div>A couple of thoughts for you and hopefully someone else has better suggestions. One question is what is your goal. Are you just wanting a rough idea for capacity planning or if you are going to bill customers based on this. If the latter, you&#39;ll want a more bulletproof solution whose integrity you can defend. 
</div>
<div>&nbsp;&nbsp; I&#39;m not sure if STATSPACK will help much because it runs within the Oracle environment. I think you need something at the Solaris level.</div>
<div>&nbsp;&nbsp;&nbsp; O.S. tools often track usage by O.S. user, so you could install each Oracle instance in different Unix username, but most of us DBAs consider that nonstandard. In Solaris 10, you could investigate Zones, but I&#39;m not sure if Oracle 10g supports Zones yet. 
</div>
<div>&nbsp;&nbsp;&nbsp; Solaris 10 includes DTrace and I&#39;m guessing that might be able to do what you want. It is a very powerful tool for this type of work. But you&#39;ll probably have to ask a forum for Solaris like the&nbsp;Usenet Newsgroup&nbsp; 
comp.unix.solaris.</div>
<div>&nbsp;&nbsp;&nbsp; A simple-minded approach would be to look at your processes associated with each instance and see if there is anything unique about them, using the &quot;ps&quot; command. Then you could write a Unix script that would execute ps and categorize the processes by Oracle instance and sum the CPU load for that instance. Run that every few minutes as a cron job and accumulate&nbsp;an estimate of usage over time.&nbsp;Crude but I think doable. 
</div><font color="#888888">
<div>&nbsp;</div>
<div>Dennis Williams</div></font></blockquote></div><br><br clear="all"><br></span></div>-- <br>-----<br>Best regards<br><span class="sg">RJamya </span></blockquote></div><br>

------=_Part_28785_9039698.1195745392556--
--
http://www.freelists.org/webpage/oracle-l