George,
Two things jump out together:
- The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period...
- Waits on the "cache buffers chains" are consuming another 16% of total response-time
With these two things consuming
41% of everything consumed by the database instance
during this time period, there is no chance that anything else is more
important...
Chances are excellent that these two things are
related. Since the SQL statement has over 329m buffer gets and about 0.5m
buffer cache-misses (i.e. physical reads) to it's credit, this indicates a
buffer-cache hit-ratio of over 99.7%, which is sure proof that something is
seriously wrong! :-) My guess is that the query is using an
inappropriate and/or inefficient index for a long, long, long range-scan
operation, which is racking up all of those buffer gets. What do you
expect from the rule-based optimizer? If you were running CBO and this
happened, I'd suggest gathering column-level "histogram" statistics on the
table. My guess also is that many concurrent users are running this
statement during the course of the sample period, causing the latch contention
for cache buffers in the Buffer Cache, thus the relationship between the two
symptoms?
I can see that DB_CACHE_LRU_LATCHES has been pushed
up to 48; don't know what CPU_COUNT is, but obviously this change has had
zero impact on the latch contention problem. Tuning the SQL will fix the
problem; accomodating the problem by configuring more latches has no
impact.
Tuning that one SQL statement (plus a few of it's
look-alikes, also listed in the report) will resolve the major performance
issues you are experiencing. In fact, it will have a miraculous
impact...
Hope this helps...
-Tim
----- Original Message -----
From: "Leonard, George" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 2:53 AM
Subject: Please help, comment required
urgently
> suspicions but my manager and the client is not buying what I am say,
>
> Not knowing anything of the system architecture please look at the output
> and say what would concern you. What assumptions/recommendations you would
> make.
>
> Thx
>
>
>
> George
> ________________________________________________
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:[EMAIL PROTECTED]
> Web: http://www.didata.co.za
>
> You Have The Obligation to Inform One Honestly of the risk, And As a Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
> or Injure Themselves as They See Fit!
>
>
>
