Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i12MTvD22377 for ; Mon, 2 Feb 2004 16:29:57 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i12MTpo22324 for ; Mon, 2 Feb 2004 16:29:52 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86D8A3951AC; Mon, 2 Feb 2004 17:26:07 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 02 Feb 2004 17:24:41 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mx10.radisys.com (mx10.radisys.com [206.102.10.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8DF9C394CE3 for ; Mon, 2 Feb 2004 17:24:32 -0500 (EST) Received: by mx10.radisys.com (Postfix, from userid 5) id E58F0142DEE; Mon, 2 Feb 2004 14:31:16 -0800 (PST) Received: from UNKNOWN(206.103.52.194), claiming to be "dcblackberry.radisys.com" via SMTP by mx10, id smtpdAAA0JXUUX; Mon Feb 2 14:31:15 2004 To: oracle-l@freelists.org Subject: RE: Capacity Planner from OEM VS Statspack MIME-Version: 1.0 X-Mailer: Lotus Notes Release 5.0.10 March 22, 2002 Message-ID: From: Jared.Still@radisys.com Date: Mon, 2 Feb 2004 14:31:11 -0800 X-MIMETrack: Serialize by Router on DCBlackBerry/Radisys_Corporation/US(Release 5.0.12 |February 13, 2003) at 02/02/2004 02:29:11 PM, Serialize complete at 02/02/2004 02:29:11 PM Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 599 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jared.Still@radisys.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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 Sent by: oracle-l-bounce@freelists.org 02/02/2004 12:10 PM Please respond to oracle-l To: "'oracle-l@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@lifetouch.com -----Original Message----- From: Jared.Still@radisys.com [mailto:Jared.Still@radisys.com] Sent: Monday, February 02, 2004 2:05 PM To: oracle-l@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 Sent by: oracle-l-bounce@freelists.org 02/02/2004 10:35 AM Please respond to oracle-l To: 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@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of MacGregor, Ian A. Sent: 2004-02-02 1:01 PM To: 'oracle-l@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@SLAC.Stanford.edu -----Original Message----- From: Cary Millsap [mailto:cary.millsap@hotsos.com]=20 Sent: Monday, February 02, 2004 7:47 AM To: oracle-l@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 - 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@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Freeman, Donald Sent: Monday, February 02, 2004 8:32 AM To: oracle-l@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@freelists.org = [mailto:oracle-l-bounce@freelists.org]On Behalf Of = Luc.Demanche@astrazeneca.com Sent: Monday, February 02, 2004 9:19 AM To: oracledba@lazydba.com; oracle-l@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 - 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@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@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@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@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@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@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@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@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 -----------------------------------------------------------------