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