RE: Diagnose Slow System

[VIVEK_SHARMA]

PERFORMANCE

------------

A Statspack & report.txt Covering the problem period is Very Good Advice .

 

1) From report.txt paste only the Section below "system-wide waits for non-background processes"

Likewise from statspack paste the "top 5 wait" events section

 

2) v$session_wait is is additionally the most important view for event son which wait is

occuring

 

 

A Network issue Can sometimes be 

-----------------------------------------------------------------

 

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

 

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.

 

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

systems Though there may be better network tools too.

 

HTH

 

 

-----Original Message-----
From: Barbara Baker [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 24, 2002 9:43 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Diagnose Slow System

Thanks, Tim.

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

Barb

  Tim Gorman <[EMAIL PROTECTED]> wrote:

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" <[EMAIL PROTECTED]>
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