Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capacity Planner from OEM VS Statspack

RE: Capacity Planner from OEM VS Statspack

From: Cary Millsap <>
Date: Mon, 2 Feb 2004 20:42:24 -0600
Message-ID: <00aa01c3e9ff$64d3cbc0$6701a8c0@CVMLAP02>

All good points. Just please don't confuse Method R with 10046 data. Method R says *nothing* about *how* you should collect your data. Method R says only that you need to collect a detailed account of your response time (note that Method R doesn't even specifically mention that what we're trying to optimize is a "database"). It just happens that extended SQL trace data (10046) is the best we have today for an Oracle system. But Method R is far older--and will last far longer--than Oracle pseudoerror debugging event 10046.

John is right: the extended SQL trace mechanism does suffer from limitations like the requirement to predict performance in the near future (so you can activate the trace before the problem starts), and the difficulties of tracing an individual user action in a complicated multiplexing environment. However, there are mitigating tools and techniques for both problems:

He is incorrect, however, in stating that only USA west-coasters still care at this time of night. :)

Cary Millsap
Hotsos Enterprises, Ltd.
* 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 for schedule details...

-----Original Message-----
[] On Behalf Of John Kanagaraj Sent: Monday, February 02, 2004 7:41 PM
To: ''
Subject: RE: Capacity Planner from OEM VS Statspack

At this time, this discussion is limited to the West Coast people (and those
still in front of their email elsewhere!), so here's mine!

I think Statspack is a step in a postive direction (Those of you who used
the old BSTAT/ESTAT know what I am talking about!). Unfortunately, the default config generates so much bulk in both data collected/stored as well
in Reporting. The generic problems are:

(a) The storage, processing and report analysis overhead is significant, especially when you use an 'out-of-the-box' configuration (b) There is no way of capturing only "areas of interest" (Granted Level 0
skips SQL only, etc. - but what I mean is the ability to capture only targeted information, say snapshots of only V$SYSSTAT and V$SYSTEM_EVENT,
(c) This results in DBAs configuring snapshots that are too far apart to be
of any use
(d) There is no simple way of generating reports that can trend or report on
only a particular event or activity (unless you code it yourself) (e) The 'Top 5 Events' section is most misleading, as it displays a Ratio
without taking into account the time period over which this was collected
(and this is probably the biggest issue I have with Statspack, as it has started off another myth). And I am not even mentioning the other ratios so
prominently displayed in the beginning of the report. (f) To look at a 'performance problem', I have seen DBAs generate a report
specifying a begin snapshot that is _way_ back upto the last snapshot. Wake
up, people!
(g) The snapshot of the SQL does not (and cannot) take the difference between the last snapshot and this one. This has mislead many a DBA who is
looking at a resource-intensive SQL that started/ended way before the snapshot was taken.

I have simply upped the SQL Limits to a high value so that I can snapshot
only the _really_ bad ones, and continue to use the Stats as a Capacity palnning tool (still crude, but Yappack is the way to go).

At the same time, a 10046 Method R approach also suffers from the fact that
it cannot 'go back in history'. As well, the other issue is actually relating a Business Objective to an actual session that can be traced, particularly on a very Heterogenous, Complex Application such as Oracle Apps
(or PeopleSoft HR for that matter). In other words, there are 500 different
geographically distributed users using 1000 different Forms (and Reports) in
20 different ways and operating on a 10,000 table, 30,000 index database using 20,000 packages (you Apps DBAs know what I am talking about). And a
patch comes around and upsets everything and you start all over again...

At the end of the day, for a simple DBA like myself, I need a trigger from a
tool such as Statspack that can say 'Something's going wrong' that will prompt me to look at current V$ and X$ views to see what's going on NOW and
later trace down a session or activity that can then be targeted with a Method R 10046 bazooka... (and No - I cannot afford to suffer from CTD aka
Compulsive Tuning Disorder!)

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

>-----Original Message-----
>From: Andy Rivenes []
>Sent: Monday, February 02, 2004 4:34 PM
>Subject: RE: Capacity Planner from OEM VS Statspack
>Interesting problem. Perhaps if you broke out the timing in
>the event calls
>with a min and a max you would see a skew the average time
>hides? I assume
>it's not the number of calls then, but the total time that's
>much longer
>when this happens?
>I have an interval resource profile perl script that I've just made
>available on that you could try running the trace
>file against
>if you're interested.
>Andy Rivenes
>At 03:51 PM 2/2/2004 -0800, MacGregor, Ian A. wrote:
>>Andy, you have parroted my warnings about running the trace
>>vs. tracing it when the job normally runs. Well at least we
>agree on
>>that. Let's take another look at the profile
>>Call Duration Calls
> Duration/Call
>>direct path write 95.28s 81.1% 5707 0.02s
>>direct path read 21.47s 18.3% 7632 0.00s
>>SQL*Net message from client 0.59s 0.5% 4 0.15s
>>db file scattered read 0.17s 0.1% 652 0.00s
>>SQL*Net message to client 0.00s 0.0% 4 0.00s
>>db file sequential read 0.00s 0.0% 1 0.00s
>>Total cpu time: 30.5 seconds
>>Again. I know direct path I/O's are the major wait, and the
>actual trace
>>will tell me the file in question. But even a 10046 trace
>doesn't tell me
>>why it was happening. In this case it was being caused by another
>>instance which was beating up the disk which held the file.
>>Oracle has methods of lowering a session's priority if it is CPU
>>intensive. I'd like it to be able to do the same for I/O intensive
>>operations as well.
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Feb 02 2004 - 20:42:24 CST

Original text of this message