| 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: Please help, comment required urgently
HELP
(I'm an idiot)
OK, never claimed math was my strong suit.
Why is this 25% of total response time? (Truthfully, I don't really
understand the column heading "of total.."
Here are the top SQL statements ordered by physical reads per execute:
Statement Executes Physical Reads Hash Value Of Total
Reads /Execute
SELECT T5.CONFL 903 549033 608.01 3509998681
47.31 %
Barb
> ----------
> From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Friday, October 18, 2002 7:53 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Please help, comment required urgently
>
> 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" < george.leonard_at_farnell.com>
> To: "Multiple recipients of list ORACLE-L" < ORACLE-L_at_fatcity.com>
> Sent: Friday, October 18, 2002 2:53 AM
> Subject: Please help, comment required urgently
>
>
> > Hi guys, I need a second opinion on the following Statspack output, I
> got my
> > 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:george.leonard_at_za.didata.com
> > 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!
> >
> >
> >
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: bbaker_at_denvernewspaperagency.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Oct 18 2002 - 13:12:36 CDT
 |
 |