Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g4SIgUw25371 for ; Tue, 28 May 2002 14:42:30 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id DAA35792; Tue, 28 May 2002 03:39:28 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0046C858; Tue, 28 May 2002 03:08:21 -0800 Message-ID: Date: Tue, 28 May 2002 03:08:21 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "VIVEK_SHARMA" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "VIVEK_SHARMA" Subject: RE: Diagnose Slow System Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-02b599b0-341b-45d0-89e4-f90c6fdcdccc" ------=_NextPartTM-000-02b599b0-341b-45d0-89e4-f90c6fdcdccc Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2062E.E715891A" ------_=_NextPart_001_01C2062E.E715891A Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable PERFORMANCE ------------ A Statspack & report.txt Covering the problem period is Very Good Advice = . =20 1) From report.txt paste only the Section below "system-wide waits for = non-background processes"=20 Likewise from statspack paste the "top 5 wait" events section =20 2) v$session_wait is is additionally the most important view for event = son which wait is=20 occuring =20 =20 A Network issue Can sometimes be=20 ----------------------------------------------------------------- =20 1) By Installing & Running the Application DIRECTLY on the DB Server . = If it Runs much better simply on the DB Server ,it could point to a = network issue =20 2) By Comparing CPU Utilizations of APP & DB Servers . If the DB Server = & APP Server Utilization's are Exceedingly Different it may be due to a = Network issue. =20 3) We "ftp" a 100M File (get & put) from APP to DB Server & vice-versa = too . The Last part of the Output in "(kbytes/sec)" is what is needed . = Convert it into MegaBits/sec (MBPS) which is the Unit of Network = Bandwidth . Commonly used network bandwidth is 10/100 MBPS . For heavy = Loaded Systems 10MBPS bandwidth sometimes proves insufficient . This is = used on between Unix=20 systems Though there may be better network tools too. =20 HTH =20 =20 -----Original Message----- Sent: Friday, May 24, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Thanks, Tim.=20 I do have a YAPP report from last week when the problems began -- I'll = get that to you. I'll also grab some more data tomorrow. (If it's = consistent, it'll start slowing down about 9:00 tomorrow morning.)=20 Barb=20 Tim Gorman wrote:=20 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 sampl! ! e. 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"=20 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 ------_=_NextPart_001_01C2062E.E715891A Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
PERFORMANCE
------------
A Statspack & report.txt Covering = the problem=20 period is Very Good Advice .
 
1) From report.txt paste only the = Section below=20 "system-wide waits for non-background processes"
Likewise from statspack paste the "top 5 = wait" events=20 section
 
2) v$session_wait is is additionally the most = important=20 view for event son which wait is
occuring
 
 
A Network issue Can sometimes be 
---------------------------------------------------------= --------
 
1) By Installing & Running the Application DIRECTLY on = the DB=20 Server . If  it Runs much better simply on the DB Server=20 ,it could point to a network = issue
 
2) By Comparing CPU Utilizations of APP & DB Servers . If = the DB=20 Server & APP Server Utilization's are  Exceedingly=20 Different it may be due to a Network=20 issue.
 
3) We "ftp" a 100M File (get & put) from APP to DB Server = &=20 vice-versa too . The Last part of the Output in "(kbytes/sec)"=20 is what is needed . Convert it into = MegaBits/sec (MBPS)=20 which is the Unit of Network Bandwidth .  Commonly=20 used network bandwidth is 10/100 MBPS . For heavy=20 Loaded Systems 10MBPS = bandwidth=20 sometimes  proves = insufficient . This=20 is used on between Unix
systems Though there may be better = network tools=20 too.
 
HTH
 
 
-----Original Message-----
From: Barbara Baker=20 [mailto:barbarabbaker@yahoo.com]
Sent: Friday, May 24, 2002 = 9:43=20 AM
To: Multiple recipients of list = ORACLE-L
Subject: Re:=20 Diagnose Slow System

Thanks, Tim.=20

I do have a YAPP report from last week when the problems began = -- =20 I'll get that to you.  I'll also grab some more data = tomorrow.  (If=20 it's consistent, it'll start slowing down about 9:00 tomorrow = morning.)=20

Barb=20

  Tim Gorman <Tim@SageLogix.com> wrote:=20 Barb,

Can=20 you take a BSTAT/ESTAT while the problem is occurring? Run=20 the
"utlbstat.sql" script from SVRMGRL and then 15-25 mins later=20 run
"utlestat.sql" from SVRMGRL. It's actually pretty important=20 the
"utlestat.sql" be run from SVRMGRL and not SQL*Plus. Please = do this=20 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=20 browse to the
http://www.oraperf.com site. Use the file-selection = browse=20 button at one of
the upload sections to find one of the = "report.txt"=20 files and click
"upload". The YAPP report will be produced=20 automagically...

What the YAPP report will do is give a great = "top->down" breakdown of where
the system has been spending = the=20 majority of what the end-user community
perceives as "response = time"=20 during the 15-25 mins of your BSTAT/ESTAT
sampl! ! e. In brief, = the=20 database is either working or waiting. If you like,
you could = email me=20 the "report.txt" file and I'll look through the YAPP
report = alongside=20 you...

There are some papers online at=20 www.oraperf.com/whitepapers.htm which should
explain the YAPP = methodology=20 (written by Anjo) and also another paper about
using YAPP with = STATSPACK.=20 The latter paper largely applies to = BSTAT/ESTAT
also...

From these=20 reports, we should be able to get a pretty good idea of what = is
going=20 on...

Thanks!

-Tim

----- Original Message = -----
To:=20 "Multiple recipients of list ORACLE-L" =
Sent:=20 Thursday, May 23, 2002 4:13 PM


> List:
> We've = been=20 fighting problems for several days. I've sort of overwhelmed
> = myself=20 with data, but I don't know what any of it means.
>
> = Solaris=20 2.6, Oracle 8.0.5, MTS
> Users complain of extreme slowness. = No errors=20 in alert, no trace files
>! ! ; generated.
> Database is = bounced=20 every day. I capture wait statistics each day before
> the = database=20 goes down. The statistics from v$system_event for enqueue
> = waits has=20 gone up considerably since the problems started last = Wednesday.
> But=20 when I look at v$lock (I'm using Steve Adams' = enqueue_locks.sql
>=20 scripts), nothing pops up.
>
> Any ideas where I should = start=20 looking? I would appreciate any help.
> (I really believe this = is a=20 connectivity (networking) issue, but don't
know
> how to = confirm=20 this)
> Thanks!
> Barb
>
> (accumulted since = last=20 night at 11:00 pm)
>
>
> EVENT TOTAL_WAITS = TOTAL_TIMEOUTS=20 TIME_WAITED
> AVERAGE_WAIT
> --------------------------- = ----------- -------------- -----------
> ------------
> = latch=20 free 814316 4064 106360
> 130612686
> enqueue 147 26=20 12033
> 81.8571429
> free buffer waits 4 0 23
> = 5.75> buffer busy waits 2959 0 567
> 19161879
> log = file=20 parallel write 68177 0 78788
> 1.155639
> log file sync = 66683 1=20 77517
> 1.16247019
> db file sequential read 1385334 0=20 144617
> 104391432
> db file scattered read 1113301 0=20 142545
> 12803815
>
>
> (The info captured = below is=20 unusual. running this repeatedly normally
shows
> = nothing
>=20 except smon TS resource wait)
>
>
> RESOURCE NSID = SID=20 HOLDING WANTING SECONDS
> -------------------- ----- ---- = -------=20 ------- ----------
> RT-1-0 4 LGWR X 0
> TM-1949-0 46 46 = SX=20 0
> TM-1999-0 423 423 SX 4
> 46 46 SX 0
> = TM-2014-0 46 46=20 SX 0
> TM-2106-0 46 46 SX 0
> TM-2218-0 46 46 SX = 0
>=20 TM-2270-0 423 423 SX 4
> TM-2275-0 423 423 SX 4
> 46 46 = SX=20 0
> TS-1-8388610 6 SMON SX 48069
> TX-1114154-43605 46 = 46 X=20 0
> TX-852064-43554 423 423 X 4
>
>> = (Below is=20 also unusual. Running this repeatedly normally returns no=20 rows)
>
>
> Sess Ser Wait Wait Time W'd So
> = ID No=20 Event State W'd (ms) Far (ms) P1
P2
> P3
> ---- = ------=20 ---------- -------- -------- -------- -------------- = ---------
-
>=20 ----
> 16 19 latch free WAITING 0 0 2147519876
59
> = 0
>=20 92 38 latch free WAITED S -1 0 2147519876
59
> 0
> = HORT=20 TIM
> E
>
> 565 31 latch free WAITING 0 0=20 2147519876
59
> 0
> 636 11604 latch free WAITED S -1 = 0=20 2147519876
59
> 0
> HORT TIM
>=20 E

------_=_NextPart_001_01C2062E.E715891A-- ------=_NextPartTM-000-02b599b0-341b-45d0-89e4-f90c6fdcdccc-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: VIVEK_SHARMA@infosys.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@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).