RE: Capacity Planner from OEM VS Statspack
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Feb 2004 14:20:49 -0600
Andy and Jared have saved me some typing. I agree that Statspack is
useful in doing what Ian and Babette describe. But what they're
describing is not performance problem diagnosis.
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 Andy Rivenes
Sent: Monday, February 02, 2004 12:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
However, what you're really collecting is "workload" information. It's
only
useful, as Cary stated, for capacity planning, or what you stated, for
trends. You're not going to solve a "performance" problem with this
level
of information.
Andy Rivenes
arivenes@xxxxxxxx
At 10:00 AM 2/2/2004 -0800, MacGregor, Ian A. wrote:
>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@xxxxxxxxxxxxxxxxx
----------------------------------------------------------------
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:
