RE: Capacity Planner from OEM VS Statspack
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 2 Feb 2004 12:05:06 -0800
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@xxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/02/2004 10:35 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
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.
- Babette
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of MacGregor, Ian A.
Sent: 2004-02-02 1:01 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxxxxxx
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap@xxxxxxxxxx]=20
Sent: Monday, February 02, 2004 7:47 AM
To: oracle-l@xxxxxxxxxxxxx
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
- 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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Freeman, Donald
Sent: Monday, February 02, 2004 8:32 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of =
Luc.Demanche@xxxxxxxxxxxxxxx
Sent: Monday, February 02, 2004 9:19 AM
To: oracledba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
- Response time
- Wait events
- I/O
- Storage=3D20
- ...=3D09
and servers
- CPU
- Memory
- File system
- ...
=3D20
Good Report tool, create graphics and you can even do a trend=3D20 =
analysis...=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@xxxxxxxxxxxxx put =
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put =
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put =
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
