| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Capacity Planner from OEM VS Statspack
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: <oracle-l-bounce_at_freelists.org> Received: from freelists-180.iquest.net by ocs.sagelogix.com
with ESMTP id 64411075747360; Mon, 02 Feb 2004 11:42:40 -0700 Received: from turing (localhost [127.0.0.1])
by turing.freelists.org (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: oracle-l_at_freelists.org
by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5D24F395B3B for <oracle-l_at_freelists.org>; Mon, 2 Feb 2004 13:42:12 -0500 (EST) Received: from poptop.llnl.gov (localhost [127.0.0.1]) by smtp-4.llnl.gov (8.12.3p2-20030917/8.12.3/LLNL evision: 1.13 $) with ESMTP id i12ImsFd021864 for <oracle-l_at_freelists.org>; Mon, 2 Feb 2004 10:48:55 -0800 (PST)Received: from downtown.llnl.gov (account rivenes1 [128.115.223.193] verified) by poptop.llnl.gov (CommuniGate Pro SMTP 4.0.6) with ESMTP id 35348365 for oracle-l_at_freelists.org; Mon, 02 Feb 2004 10:48:54 -0800 Message-Id: <5.1.0.14.2.20040202104710.031b2e90_at_poptop.llnl.gov> X-Sender: e751128_at_poptop.llnl.gov
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
arivenes_at_llnl.gov
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.
>
>
>DATABASE BEGIN_TIME END_TIME Physical Reads
>Physical Writes CPU SECONDS ELAPSED SECONDS
>---------- -------------------- -------------------- --------------
>--------------- ----------- ---------------
>ORAP 12-JAN-2004:00:00:03
>12-JAN-2004:01:00:05 14725 7697 224.005
>3602
>ORAP 12-JAN-2004:01:00:05
>12-JAN-2004:02:00:02 6271 2125 5.03
>3597
>ORAP 12-JAN-2004:02:00:02
>12-JAN-2004:03:00:04 66066 1043 6.105
>3602
>ORAP 12-JAN-2004:03:00:04
>12-JAN-2004:04:00:02 1496 1125 3.68
>3598
>ORAP 12-JAN-2004:04:00:02
>12-JAN-2004:05:00:04 1716 1462 3.995
>3602
>ORAP 12-JAN-2004:05:00:04
>12-JAN-2004:06:00:01 961 1721 3.71
>3597
>ORAP 12-JAN-2004:06:00:01
>12-JAN-2004:07:00:03 3779 1032 3.985
>3602
>ORAP 12-JAN-2004:07:00:03
>12-JAN-2004:08:00:06 16436 2026 5.84
>3603
>ORAP 12-JAN-2004:08:00:06
>12-JAN-2004:09:00:03 231051 2634 14.43
>3597
>ORAP 12-JAN-2004:09:00:03
>12-JAN-2004:10:00:05 137762 1245 15.605
>3602
>ORAP 12-JAN-2004:10:00:05
>12-JAN-2004:11:00:03 183870 1163 15.21
>3598
>ORAP 12-JAN-2004:11:00:03
>12-JAN-2004:12:00:05 143757 1166 12.975
>3602
>ORAP 12-JAN-2004:12:00:05
>12-JAN-2004:13:00:02 20349 1088 4.705
>3597
>ORAP 12-JAN-2004:13:00:02
>12-JAN-2004:14:00:04 195781 1827 13.355
>3602
>ORAP 12-JAN-2004:14:00:04
>12-JAN-2004:15:00:02 26901 1538 9.53
>3598
>ORAP 12-JAN-2004:15:00:02
>12-JAN-2004:16:00:04 43434 1039 8.205
>3602
>ORAP 12-JAN-2004:16:00:04
>12-JAN-2004:17:00:01 61892 1747 8.485
>3597
>ORAP 12-JAN-2004:17:00:01
>12-JAN-2004:18:00:04 36268 819 5.99
>3603
>ORAP 12-JAN-2004:18:00:04
>12-JAN-2004:19:00:01 87842 1302 8.4
>3597
>ORAP 12-JAN-2004:19:00:01
>12-JAN-2004:20:00:03 1256 653 4.625
>3602
>ORAP 12-JAN-2004:20:00:03
>12-JAN-2004:21:00:01 197415 1086 99.765
>3598
>ORAP 12-JAN-2004:21:00:01
>12-JAN-2004:22:00:03 556 1386 3.235
>3602
>ORAP 12-JAN-2004:22:00:03
>12-JAN-2004:23:00:06 1074 697 3.22
>3603
>ORAP 12-JAN-2004:23:00:06
>13-JAN-2004:00:00:03 14856 927 4.495
>3597
>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
>ian_at_SLAC.Stanford.edu
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Feb 02 2004 - 12:59:36 CST
 |
 |