RE: Capacity Planner from OEM VS Statspack
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Feb 2004 20:42:24 -0600
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:
- With enough re$earch inve$tment, you can harvest system-wide
10046-like data directly from your SGA, which obviates the need to
predict when you'll need to activate the feature.
- With the DBMS_MONITOR specification, Oracle Database 10g appears to
provide an elegant solution to the collecting while multiplexing
problem.
He is incorrect, however, in stating that only USA west-coasters still
care at this time of night. :)
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 John Kanagaraj
Sent: Monday, February 02, 2004 7:41 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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,
etc.)
(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!
** The opinions and facts contained in this message are entirely mine
and do
not reflect those of my employer or customers **
>-----Original Message-----
>From: Andy Rivenes [mailto:arivenes@xxxxxxxx]
>Sent: Monday, February 02, 2004 4:34 PM
>To: oracle-l@xxxxxxxxxxxxx
>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 appsdba.com that you could try running the trace
>file against
>if you're interested.
>
>Andy Rivenes
>arivenes@xxxxxxxx
>
>At 03:51 PM 2/2/2004 -0800, MacGregor, Ian A. wrote:
>>Andy, you have parroted my warnings about running the trace
>immediately
>>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.
>>
>>Ian
>
>----------------------------------------------------------------
>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:
