| 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 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
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
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.
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 - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details...
-----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 = experience. I'm used to tools that work and actually measure what they = purport to. I got all excited about the capacity planner about 6 months = ago and asked the same questions you are asking now. Mostly, nobody is = using it. It becomes a headache itself, the agent fails and causes you = grief. I don't think you'll find much usefulness in it. When you start = troubleshooting it won't give you anything helpful.=20
After reading Carey Milsaps Optimizing Oracle Performance I am less than = thrilled with statspack also. You can't solve (or even determine) a = particular problems origin while looking at aggregate values.
The main value of these things is to provide a comfort level and = distraction to management. Attach your statspack report to an email and = send it to your boss. It should keep him (or her) busy for some time = 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 ... =20 from databases=20
I have two questions:
1- Are a lot of you using it?
2- Does STATSPACK become less usefull? I would keep STATSPACK for the = 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 -----------------------------------------------------------------Received on Mon Feb 02 2004 - 12:00:31 CST
 |
 |