Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Capacity Planner from OEM VS Statspack

From: <Jared.Still_at_radisys.com>
Date: Mon, 2 Feb 2004 14:31:11 -0800
Message-ID: <OFB7F7A309.13A08A7E-ON88256E2E.007B6B3A-88256E2E.007BA2EB@radisys.com>


Data from statspack can be aggregated to give average response times. This is what YAPPPACK is for, courtesy Mogens Norgaard, et al.

Check www.miracleas.dk

I've put what I use to generate charts in a zip at http://www.cybcon.com/~jkstill/util/zips/yapp_chart.tgz

What I have done is not documented, but shouldn't be too hard to figure out.

I'll document it when I have time if anyone needs it.

It works as is on 9i, requires a minor tweak on 8i.

The yapp_ins.sql pkg needs to have 'time_waited_micro' to simply 'time_waited' for 8i IIRC.

Jared

DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> Sent by: oracle-l-bounce_at_freelists.org
 02/02/2004 12:10 PM
 Please respond to oracle-l  

        To:     "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
        cc: 
        Subject:        RE: Capacity Planner from OEM VS Statspack


Jared

   Can you explain how you use STATSPACK to find response time averages?

   I don't see where anyone has mentioned it, but to me a key aspect of capacity planning is to see what is happening at the server level (iostat /
vmstat). Perhaps OEM includes that, but I haven't used that tool.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Monday, February 02, 2004 2:05 PM
To: oracle-l_at_freelists.org
Subject: RE: Capacity Planner from OEM VS Statspack

So, what you and Ian are saying is actually in agreement with Cary. Statspack is useful for detecting problems, useless for solving them.

I find it most useful for capacity planning and response time avgs.

Jared

<babette.turnerunderwood_at_hrdc-drhc.gc.ca> Sent by: oracle-l-bounce_at_freelists.org
 02/02/2004 10:35 AM
 Please respond to oracle-l  

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        RE: Capacity Planner from OEM VS Statspack


I agree with Ian.... Sometimes Statspack is VERY useful..

In our case the Statspack reports shows ave read times of 1-10ms. However we occasionally see read times of 300-700 ms.

We are currently investigating what is on the slower disks, What systems are sharing them, and whether oracle is=20 chaining I/O requests and giving false stats or if there really is a = problem.
(Hey, on OS/390 mainframe system we don't get iostat / sar / vmstat / = top)

This top-down approach doesn't address any SPECIFIC performance proble. BUT ... if we didn't have Statspack running periodically, we might have = missed this.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MacGregor, Ian A. Sent: 2004-02-02 1:01 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Capacity Planner from OEM VS Statspack

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        =20
---------- -------------------- -------------------- -------------- =
--------------- ----------- ---------------        =20
ORAP       12-JAN-2004:00:00:03 12-JAN-2004:01:00:05          14725      =
      7697     224.005            3602        =20
ORAP       12-JAN-2004:01:00:05 12-JAN-2004:02:00:02           6271      =
      2125        5.03            3597        =20
ORAP       12-JAN-2004:02:00:02 12-JAN-2004:03:00:04          66066      =
      1043       6.105            3602        =20
ORAP       12-JAN-2004:03:00:04 12-JAN-2004:04:00:02           1496      =
      1125        3.68            3598        =20
ORAP       12-JAN-2004:04:00:02 12-JAN-2004:05:00:04           1716      =
      1462       3.995            3602        =20
ORAP       12-JAN-2004:05:00:04 12-JAN-2004:06:00:01            961      =
      1721        3.71            3597        =20
ORAP       12-JAN-2004:06:00:01 12-JAN-2004:07:00:03           3779      =
      1032       3.985            3602        =20
ORAP       12-JAN-2004:07:00:03 12-JAN-2004:08:00:06          16436      =
      2026        5.84            3603        =20
ORAP       12-JAN-2004:08:00:06 12-JAN-2004:09:00:03         231051      =
      2634       14.43            3597        =20
ORAP       12-JAN-2004:09:00:03 12-JAN-2004:10:00:05         137762      =
      1245      15.605            3602        =20
ORAP       12-JAN-2004:10:00:05 12-JAN-2004:11:00:03         183870      =
      1163       15.21            3598        =20
ORAP       12-JAN-2004:11:00:03 12-JAN-2004:12:00:05         143757      =
      1166      12.975            3602        =20
ORAP       12-JAN-2004:12:00:05 12-JAN-2004:13:00:02          20349      =
      1088       4.705            3597        =20
ORAP       12-JAN-2004:13:00:02 12-JAN-2004:14:00:04         195781      =
      1827      13.355            3602        =20
ORAP       12-JAN-2004:14:00:04 12-JAN-2004:15:00:02          26901      =
      1538        9.53            3598        =20
ORAP       12-JAN-2004:15:00:02 12-JAN-2004:16:00:04          43434      =
      1039       8.205            3602        =20
ORAP       12-JAN-2004:16:00:04 12-JAN-2004:17:00:01          61892      =
      1747       8.485            3597        =20
ORAP       12-JAN-2004:17:00:01 12-JAN-2004:18:00:04          36268      =
       819        5.99            3603        =20
ORAP       12-JAN-2004:18:00:04 12-JAN-2004:19:00:01          87842      =
      1302         8.4            3597        =20
ORAP       12-JAN-2004:19:00:01 12-JAN-2004:20:00:03           1256      =
       653       4.625            3602        =20
ORAP       12-JAN-2004:20:00:03 12-JAN-2004:21:00:01         197415      =
      1086      99.765            3598        =20
ORAP       12-JAN-2004:21:00:01 12-JAN-2004:22:00:03            556      =
      1386       3.235            3602        =20
ORAP       12-JAN-2004:22:00:03 12-JAN-2004:23:00:06           1074      =
       697        3.22            3603        =20
ORAP       12-JAN-2004:23:00:06 13-JAN-2004:00:00:03          14856      =
       927       4.495            3597        =20
ORAP       13-JAN-2004:00:00:03                                          =
                                              =20


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. =20

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
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]=20 Sent: Monday, February 02, 2004 7:47 AM
To: oracle-l_at_freelists.org
Subject: RE: Capacity Planner from OEM VS Statspack

I'd summarize this way:

Statspack is "worse than useless" for diagnosing performance problems. = Something is useless if it doesn't work. It is worse than useless if it = inspires confidence while not working.=20

I regularly meet clients who learn the inadequacies of Statspack data = only after months--sometimes years--of pain. The problem with Statspack = in the problem diagnosis application is that it's *unreliable*. It works = sometimes, but not always. Being unreliably correct is even worse than = being reliably incorrect, because unreliably correct tools inspire false = confidence.

That said, Statspack is an excellent tool in the capacity planning = application, where you *need* aggregated data.

So, please don't take me wrong. Statspack is a tool. A tool itself is = neither good nor bad; it's the *application* of a tool that is good or = bad. A screwdriver is lousy at driving a nail but good at turning a = screw.

Statspack is a lousy tool for performance problem diagnosis. It's a fine = tool for some other applications.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Freeman, Donald Sent: Monday, February 02, 2004 8:32 AM
To: oracle-l_at_freelists.org
Subject: RE: Capacity Planner from OEM VS Statspack

I'm a relatively new DBA but I have 30 years electronic engineering =3D = experience. I'm used to tools that work and actually measure what they = =3D purport to. I got all excited about the capacity planner about 6 = months =3D ago and asked the same questions you are asking now. Mostly, = nobody is =3D using it. It becomes a headache itself, the agent fails = and causes you =3D grief. I don't think you'll find much usefulness in = it. When you start =3D troubleshooting it won't give you anything = helpful.=3D20

After reading Carey Milsaps Optimizing Oracle Performance I am less than = =3D thrilled with statspack also. You can't solve (or even determine) a = =3D particular problems origin while looking at aggregate values.

The main value of these things is to provide a comfort level and =3D = distraction to management. Attach your statspack report to an email and = =3D send it to your boss. It should keep him (or her) busy for some = time =3D while you work on the database.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of = Luc.Demanche_at_astrazeneca.com
Sent: Monday, February 02, 2004 9:19 AM
To: oracledba_at_lazydba.com; oracle-l_at_freelists.org Subject: Capacity Planner from OEM VS Statspack

Hi DBA,

I'm starting to take a look at the "Capacity Planner" tool from the = Diagnostics Pack. Great tool, collects info on lots of interesting = statistics ... =3D20 from databases=3D20

I have two questions:
1- Are a lot of you using it?
2- Does STATSPACK become less usefull? I would keep STATSPACK for the = =3D SQL level. Capacity Planner doesn't seem to handler that level. = Right?

Thanks
Luc



Luc Demanche
AstraZeneca R&D Montreal
Oracle Database Administrator
514.832.3200 x2356

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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------

----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------




----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------




----------------------------------------------------------------
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 - 16:31:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US