| 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
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!
>-----Original Message-----
>From: Andy Rivenes [mailto:arivenes_at_llnl.gov]
>Sent: Monday, February 02, 2004 4:34 PM
>To: oracle-l_at_freelists.org
>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_at_llnl.gov
>
>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_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
>-----------------------------------------------------------------
>
-- 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 - 19:40:59 CST
 |
 |