| 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: 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
-- 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 - 12:35:43 CST
 |
 |