Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Capacity Planner from OEM VS Statspack

Re: RE: Capacity Planner from OEM VS Statspack

From: <>
Date: Mon, 2 Feb 2004 11:59:36 -0700 (MST)
Message-ID: <>

Yes, you will resolve performance problems, just by having the high-level perspective and knowing roughly where to drill down, if not identifying the problems directly. Since SQL tuning can take hours or days, it is an awful feeling to invest all that time and effort when there is something much bigger and more important lurking out of sight. It's also nice to know that the resolution for the problem you are trying to resolve may reap additional benefits elsewhere. Having high-level information immediately on-hand allows you to concentrate with confidence. As Cary has pointed out, it makes most sense to have end-users identify and prioritize problem areas, but how often do DBAs actually get to interact with end-users? On my current project, our end-users are in Bangalore and Bombay, and I am held at arms-length by two layers of sub-contracting and red tape. The distance isn't the problem -- it's the time zone shift. While I'm waiting for responses to my questions (next day turnaround), I'm also coming up with leads on my own by analyzing the data captured by STATSPACK. By the time the end-users respond, I can hit the ground running on the problem with the additional information, or sometimes even have the problem identified already. Yes, you can actually identify individual problems with STATSPACK, but it's good to have your suspicions corroborated by end-user testimony. It's also wise to try to get 10046 traces as well...

And with historical information, I can measure the "before" and "after" impact of changes, in terms of I/O generated, or SQL execution elapsed times, or other measures. Nice to be able to quantify the impact of a change...

Return-Path: <> Received: from by

        with ESMTP id 64411075747360; Mon, 02 Feb 2004 11:42:40 -0700 Received: from turing (localhost [])

	by (Avenir Technologies Mail Multiplex) with ESMTP
	id 7B453395323; Mon,  2 Feb 2004 13:43:45 -0500 (EST)
Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 02 Feb 2004 13:42:24 -0500 (EST) X-Original-To:
Received: from ( [])
	by (Avenir Technologies Mail Multiplex) with ESMTP id 5D24F395B3B
	for <>; Mon,  2 Feb 2004 13:42:12 -0500 (EST)
Received: from (localhost [])
	by (8.12.3p2-20030917/8.12.3/LLNL evision: 1.13 $) with ESMTP id i12ImsFd021864
	for <>; Mon, 2 Feb 2004 10:48:55 -0800 (PST)
Received: from (account rivenes1 [] verified)   by (CommuniGate Pro SMTP 4.0.6)   with ESMTP id 35348365 for; Mon, 02 Feb 2004 10:48:54 -0800 Message-Id: <> X-Sender:
X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Mon, 02 Feb 2004 10:50:41 -0800
From: Andy Rivenes <>
Subject: RE: Capacity Planner from OEM VS Statspack In-Reply-To: <26E3EC48949D134C94A1574B2C89466113A6E0_at_exchange2.slac.stan>
Mime-Version: 1.0
Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit
X-archive-position: 570
X-ecartis-version: Ecartis v1.0.0
Errors-To: X-original-sender:
Precedence: normal
X-list: oracle-l

However, what you're really collecting is "workload" information. It's only useful, as Cary stated, for capacity planning, or what you stated, for trends. You're not going to solve a "performance" problem with this level of information.

Andy Rivenes

At 10:00 AM 2/2/2004 -0800, MacGregor, Ian A. wrote:
>I have to disagree here. Sometimes one needs to take a top-down approach
>to tuning. Measuring things such as CPU usage and I/O counts can be
>useful. For instance here's a report for a very lightly loaded system
>based on statspack data.
>---------- -------------------- -------------------- --------------
>--------------- ----------- ---------------
>ORAP 12-JAN-2004:00:00:03
>12-JAN-2004:01:00:05 14725 7697 224.005
>ORAP 12-JAN-2004:01:00:05
>12-JAN-2004:02:00:02 6271 2125 5.03
>ORAP 12-JAN-2004:02:00:02
>12-JAN-2004:03:00:04 66066 1043 6.105
>ORAP 12-JAN-2004:03:00:04
>12-JAN-2004:04:00:02 1496 1125 3.68
>ORAP 12-JAN-2004:04:00:02
>12-JAN-2004:05:00:04 1716 1462 3.995
>ORAP 12-JAN-2004:05:00:04
>12-JAN-2004:06:00:01 961 1721 3.71
>ORAP 12-JAN-2004:06:00:01
>12-JAN-2004:07:00:03 3779 1032 3.985
>ORAP 12-JAN-2004:07:00:03
>12-JAN-2004:08:00:06 16436 2026 5.84
>ORAP 12-JAN-2004:08:00:06
>12-JAN-2004:09:00:03 231051 2634 14.43
>ORAP 12-JAN-2004:09:00:03
>12-JAN-2004:10:00:05 137762 1245 15.605
>ORAP 12-JAN-2004:10:00:05
>12-JAN-2004:11:00:03 183870 1163 15.21
>ORAP 12-JAN-2004:11:00:03
>12-JAN-2004:12:00:05 143757 1166 12.975
>ORAP 12-JAN-2004:12:00:05
>12-JAN-2004:13:00:02 20349 1088 4.705
>ORAP 12-JAN-2004:13:00:02
>12-JAN-2004:14:00:04 195781 1827 13.355
>ORAP 12-JAN-2004:14:00:04
>12-JAN-2004:15:00:02 26901 1538 9.53
>ORAP 12-JAN-2004:15:00:02
>12-JAN-2004:16:00:04 43434 1039 8.205
>ORAP 12-JAN-2004:16:00:04
>12-JAN-2004:17:00:01 61892 1747 8.485
>ORAP 12-JAN-2004:17:00:01
>12-JAN-2004:18:00:04 36268 819 5.99
>ORAP 12-JAN-2004:18:00:04
>12-JAN-2004:19:00:01 87842 1302 8.4
>ORAP 12-JAN-2004:19:00:01
>12-JAN-2004:20:00:03 1256 653 4.625
>ORAP 12-JAN-2004:20:00:03
>12-JAN-2004:21:00:01 197415 1086 99.765
>ORAP 12-JAN-2004:21:00:01
>12-JAN-2004:22:00:03 556 1386 3.235
>ORAP 12-JAN-2004:22:00:03
>12-JAN-2004:23:00:06 1074 697 3.22
>ORAP 12-JAN-2004:23:00:06
>13-JAN-2004:00:00:03 14856 927 4.495
>ORAP 13-JAN-2004:00:00:03
>If I have a report which deviates from this, then I can start
>looking more closely. The statspack information is gathered every ten
>minutes. I can then propely trace the statements which are candidate
>problems. I can also get more information. If the CPU count goes up is
>it due to an increase of logical I/Os. How much of the physical I/O is
>direct and to what tablespaces etc.
>There are many problems with statspack and the virtual views upon which it
>is based. Counters resetting are going negative, the frequency of the
>updates etc. I have made elaborate systems, chucked them out of
>frustration, changed tactics and tried again.
>At one time I was totally against such systems because they were not
>perfect, actually far from it. But I came to realize that though
>statspack is inadequate, it is not useless.
>Ian MacGregor
>Stanford Linear Accelerator Center

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Feb 02 2004 - 12:59:36 CST

Original text of this message