Re: Diagnose Slow System

There is always a flaw somewhere, but that "tuning advise" section at the bottom of the report is useful because it is a simple arithmetic summary based on the percentage numbers laid out earlier in the report. The whole "YAPP" methodology is based on identifying the portions of "total response time" used by each processing or wait operation, so that the final summary "tuning advise" section is merely a listing of the subtotals of each individually identified component of "total response time".

One of the major flaws in the existing YAPP report involves the example that Cary Millsap covered in great detail in an earlier email (last week? week before? time just flies!), describing the flaws of dismissing certain wait-events as "idle" and therefore not of interest...

The "SQL*Net message from client" wait-event is the classic example:

• In the situation where you are monitoring a single session that is a connection from SQL*Plus and the client process is simply displaying the "SQL>" prompt while the end-user is chatting on the phone, then the resulting "SQL*Net message from client" time on that session is truly "idle" and not significant to a tuning effort.
• On the other hand, if the situation is a batch program or report which is pounding away on the database, the presence of the "SQL*Net message from client" wait-event is very significant to a tuning effort. This is because the client batch program or report itself is part of "total response time" experienced by the end-user. If the database server-side session is constantly waiting for the client-side program to give it a command (which is what "SQL*Net message from client" means), then it implies that a lot of time is being spent in the client-side program. Not necessarily a tuning problem for Oracle, but certainly something good to know...

---

I recently had a similar situation on an Oracle database supporting
Peoplesoft.  They have their own import/export program which was being used
to copy data from one database to another.  Of course, it was taking 36-40
hours and the DBAs were asked what was wrong, because obviously the problem
lies with the database, right?  We looked at the timing statistics in both
the V$SESSION_EVENT and V$SESSTAT views on both the source and target
databases and found that 99% of the time was being spent in wait event
"SQL*Net message from client".  It occurred to us to ask where the
Peoplesoft program was running.  It was running on one of the Peoplesoft
consultant's laptop...

Since Peoplesoft has not implemented this program in UNIX (i.e. NT/2000
only), we were able to request that the program be installed on one of the
larger W2K servers in the data center with four much-larger CPUs.  The
export/import program completed in 3-4 hours.  What's even better is that we
were able to predict that the improvement would be that magnitude, and we
were pretty much dead on (we had a pool, I said 8 hours, someone else said 4
hours and won).  Very impressive to management, though they recently stiffed
us on our invoices anyway (jerks!).  Funny how management forgets how
impressed they were a few months before when it comes time to pay invoices
after the completion of the project...  :-(

So, the YAPP analysis report, in filtering out whichever wait-events are
considered un-important, can be misleading.  It is up to you to bear this in
mind when assessing the report.  But still, it is a wonderful thing and the
best automated analysis package going!

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Friday, May 24, 2002 3:03 PM




> > the YAPP analyzer at www.oraperf.com anyways..

>

> The last part of the oraperf report has suggestions for items to

investigate


> and tune.  Is oraperf really good at spotting the key tuning

opportunities?


>

>

>

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: Greg Moore

>   INET: sqlgreg_at_pacbell.net

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> 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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).