Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28948 invoked from network); 23 Nov 2007 09:45:10 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 23 Nov 2007 09:45:10 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8F0EA7C0A6E; Fri, 23 Nov 2007 10:45:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29739-10; Fri, 23 Nov 2007 10:45:10 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 086E37C0A55; Fri, 23 Nov 2007 10:45:09 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Nov 2007 09:58:43 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E64DD7C079F for ; Fri, 23 Nov 2007 09:58:42 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22808-03 for ; Fri, 23 Nov 2007 09:58:42 -0500 (EST) Received: from smtp-vbr8.xs4all.nl (smtp-vbr8.xs4all.nl [194.109.24.28]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3A5337BFA92 for ; Fri, 23 Nov 2007 09:58:41 -0500 (EST) Received: from lagavulin.dbalert.eu (dbalert.xs4all.nl [213.84.185.230]) (authenticated bits=0) by smtp-vbr8.xs4all.nl (8.13.8/8.13.8) with ESMTP id lANEwegF055506; Fri, 23 Nov 2007 15:58:41 +0100 (CET) (envelope-from careljan@dbalert.eu) Subject: Re: CPU load per db instance From: Carel-Jan Engel To: john.kanagaraj@gmail.com Cc: rjamya@gmail.com, Oracle Discussion List In-Reply-To: <2ead3a60711222317w5f6dd359j656183c387cd807c@mail.gmail.com> References: <9177895d0711211207m3f024d34ua8fc57aac2b51f33@mail.gmail.com> <2ead3a60711222317w5f6dd359j656183c387cd807c@mail.gmail.com> Content-Type: multipart/alternative; boundary="=-rcTEtdsmPs/MSVXylYDA" Date: Fri, 23 Nov 2007 15:58:27 +0100 Message-Id: <1195829907.24249.29.camel@lagavulin.dbalert.eu> Mime-Version: 1.0 X-archive-position: 3451 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: careljan@dbalert.eu Precedence: normal Reply-to: careljan@dbalert.eu List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --=-rcTEtdsmPs/MSVXylYDA Content-Type: text/plain Content-Transfer-Encoding: 7bit At a CT site we're amidst the development of such a tool. We combine statspack, ashpack (Kyle Hailey's 'Money for nothing, Ash for free' thingy) and nmon (yes it's AIX) reports together in one performance warehouse. All servers upload to the same DB. With the tool (build in APEX) we can provide insight in resource usage at all the levels, in a coherent way. looking at a given time window, you can switch between various sources like statspack and nmon. nmon takes snapshots every 5 seconds, ashpack every 3, IIRC. When it reaches a level of sufficient matureness the thing might get open-sourced. At this site every instance (and if there are more applications running on that instance, every application) has its own WLM class, assigning 'enough' CPU to that instance/app. The apps are split by running separate listeners for every instance and every app. Each listener gets its own WLM class, the server processes will inherit this class. Took some time to set up, but all the maintenance and management tools are scripted out pretty well now, and it works quite well! Best regards, Carel-Jan Engel === If you think education is expensive, try ignorance. (Derek Bok) === On Thu, 2007-11-22 at 23:17 -0800, John Kanagaraj wrote: > > I understand this this information changes few milliseconds, but like we > > capture using statspack ... can we do something on the server itself and > > show it by each db? Has anyone already done this before? OS is solaris8 and > > 10, db version is 9ir2 or 10g. > > Raj, > > If you are using 10g, then you are in luck. V$OSSTAT will provide > system level CPU usage and V$SYS_TIME_MODEL will provide Instance > level CPU usage (add "DB CPU" for foreground and "background cpu time" > for background together). For explanation, see the STATSPACK packages > and the sprepins.sql script. > > Happy Thanksgiving, > -- > John Kanagaraj <>< > DB Soft Inc > http://www.linkedin.com/in/johnkanagaraj > http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) > ** The opinions and facts contained in this message are entirely mine > and do not reflect those of my employer or customers ** > -- > http://www.freelists.org/webpage/oracle-l > > --=-rcTEtdsmPs/MSVXylYDA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit At a CT site we're amidst the development of such a tool.
We combine statspack, ashpack (Kyle Hailey's 'Money for nothing, Ash for free' thingy) and nmon (yes it's AIX) reports together in one performance warehouse.
All servers upload to the same DB. With the tool (build in APEX) we can provide insight in resource usage at all the levels, in a coherent way. looking at a given time window, you can switch between various sources like statspack and nmon. nmon takes snapshots every 5 seconds, ashpack every 3, IIRC. When it reaches a level of sufficient matureness the thing might get open-sourced.

At this site every instance (and if there are more applications running on that instance, every application) has its own WLM class, assigning 'enough' CPU to that instance/app. The apps are split by running separate listeners for every instance and every app. Each listener gets its own WLM class, the server processes will inherit this class. Took some time to set up, but all the maintenance and management tools are scripted out pretty well now, and it works quite well!

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok)
===


On Thu, 2007-11-22 at 23:17 -0800, John Kanagaraj wrote: 
> I understand this this information changes few milliseconds, but like we
> capture using statspack ... can we do something on the server itself and
> show it by each db? Has anyone already done this before? OS is solaris8 and
> 10, db version is 9ir2 or 10g.

Raj,

If you are using 10g, then you are in luck. V$OSSTAT will provide
system level CPU usage and V$SYS_TIME_MODEL will provide Instance
level CPU usage (add "DB CPU" for foreground and "background cpu time"
for background together). For explanation, see the STATSPACK packages
and the sprepins.sql script.

Happy Thanksgiving,
-- 
John Kanagaraj <><
DB Soft Inc
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l

--=-rcTEtdsmPs/MSVXylYDA-- -- http://www.freelists.org/webpage/oracle-l