Re: Diagnose Slow System

Really, it's just the "utlestat.sql" part -- the BSTAT/ESTAT scripts contain formatting commands for SVRMGRL, not SQL*Plus. Formatting really only matters with "utlestat.sql" when it is generating the "report.txt" file...

It's perfectly OK to run SQL*Plus for "utlbstat.sql" and you can modify "utlestat.sql" to utilize SQL*Plus formatting commands in place of the SVRMGRL ones, but why bother? I only use BSTAT/ESTAT these days to send to the YAPP analyzer at www.oraperf.com anyways... :-)

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 24, 2002 6:53 AM

>
> Tim,
>
> I'm curious why you say that utlbstat/utlestat should be run from svrmgrl
> and not sqlplus.
> I've not heard/read that before.
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
>
>
> "Tim Gorman"
> <Tim_at_SageLogix To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> .com> cc:
> Sent by: Subject: Re: Diagnose Slow
System
> root_at_fatcity.c
> om
>
>
> 05/23/02 09:38
> PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Barb,
>
> Can you take a BSTAT/ESTAT while the problem is occurring? Run the
> "utlbstat.sql" script from SVRMGRL and then 15-25 mins later run
> "utlestat.sql" from SVRMGRL. It's actually pretty important the
> "utlestat.sql" be run from SVRMGRL and not SQL*Plus. Please do this at
> least once during the periods of slowness -- more than once if possible...
>
> Then, FTP the "report.txt" file(s) up to your PC and then browse to the
> http://www.oraperf.com site. Use the file-selection browse button at one
> of
> the upload sections to find one of the "report.txt" files and click
> "upload". The YAPP report will be produced automagically...
>
> What the YAPP report will do is give a great "top->down" breakdown of
where
> the system has been spending the majority of what the end-user community
> perceives as "response time" during the 15-25 mins of your BSTAT/ESTAT
> sample. In brief, the database is either working or waiting. If you
like,
> you could email me the "report.txt" file and I'll look through the YAPP
> report alongside you...
>
> There are some papers online at www.oraperf.com/whitepapers.htm which
> should
> explain the YAPP methodology (written by Anjo) and also another paper
about
> using YAPP with STATSPACK. The latter paper largely applies to
BSTAT/ESTAT
> also...
>
> From these reports, we should be able to get a pretty good idea of what is
> going on...
>
> Thanks!
>
> -Tim
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, May 23, 2002 4:13 PM
>
>
> > List:
> > We've been fighting problems for several days. I've sort of overwhelmed
> > myself with data, but I don't know what any of it means.
> >
> > Solaris 2.6, Oracle 8.0.5, MTS
> > Users complain of extreme slowness. No errors in alert, no trace files
> > generated.
> > Database is bounced every day. I capture wait statistics each day
before
> > the database goes down. The statistics from v$system_event for enqueue
> > waits has gone up considerably since the problems started last
Wednesday.
> > But when I look at v$lock (I'm using Steve Adams' enqueue_locks.sql
> > scripts), nothing pops up.
> >
> > Any ideas where I should start looking? I would appreciate any help.
> > (I really believe this is a connectivity (networking) issue, but don't
> know
> > how to confirm this)
> > Thanks!
> > Barb
> >
> > (accumulted since last night at 11:00 pm)
> >
> >
> > EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
> > AVERAGE_WAIT
> > --------------------------- ----------- -------------- -----------
> > ------------
> > latch free 814316 4064 106360
> > 130612686
> > enqueue 147 26 12033
> > 81.8571429
> > free buffer waits 4 0 23
> > 5.75
> > buffer busy waits 2959 0 567
> > 19161879
> > log file parallel write 68177 0 78788
> > 1.155639
> > log file sync 66683 1 77517
> > 1.16247019
> > db file sequential read 1385334 0 144617
> > 104391432
> > db file scattered read 1113301 0 142545
> > 12803815
> >
> >
> > (The info captured below is unusual. running this repeatedly normally
> shows
> > nothing
> > except smon TS resource wait)
> >
> >
> > RESOURCE NSID SID HOLDING WANTING SECONDS
> > -------------------- ----- ---- ------- ------- ----------
> > RT-1-0 4 LGWR X 0
> > TM-1949-0 46 46 SX 0
> > TM-1999-0 423 423 SX 4
> > 46 46 SX 0
> > TM-2014-0 46 46 SX 0
> > TM-2106-0 46 46 SX 0
> > TM-2218-0 46 46 SX 0
> > TM-2270-0 423 423 SX 4
> > TM-2275-0 423 423 SX 4
> > 46 46 SX 0
> > TS-1-8388610 6 SMON SX 48069
> > TX-1114154-43605 46 46 X 0
> > TX-852064-43554 423 423 X 4
> >
> >
> > (Below is also unusual. Running this repeatedly normally returns no
> rows)
> >
> >
> > Sess Ser Wait Wait Time W'd So
> > ID No Event State W'd (ms) Far (ms) P1
> P2
> > P3
> > ---- ------ ---------- -------- -------- -------- --------------
> ---------
> -
> > ----
> > 16 19 latch free WAITING 0 0 2147519876
> 59
> > 0
> > 92 38 latch free WAITED S -1 0 2147519876
> 59
> > 0
> > HORT TIM
> > E
> >
> > 565 31 latch free WAITING 0 0 2147519876
> 59
> > 0
> > 636 11604 latch free WAITED S -1 0 2147519876
> 59
> > 0
> > HORT TIM
> > E
> >
> >
> > --
> > 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 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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.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).

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