RE: Diagnose Slow System

[Johnson, Michael]

Title: RE: Diagnose Slow System

You need to take a slice of the database when the

performance degrades.   Capturing statistics at the end of the

day before the database goes down is meaningless.

Consider setting up a 10046 event trace and

find out what session and what wait event is boggin down

through this information.  

 

Consider buying Kirti and Gaja's book on

Oracle Performance Tuning 101.   Also, www.hotsos.com

just showed me some techniques on 10046

trace data that I think you would find interesting.

 

To be sure , there is alot of information that must

be understood up front.    Not the easiest thing to do.

To say you think you have a network problem

is like saying I need to increase my hit ratio

so I will increase my performance.  It is based

on meaningless mumbo jumbo.   Not a very

good feeeling when one rally does not understand

what is going on behind the scenes.

 

Not a simple answer to give to you here.

Mike

-----Original Message-----
From: Aponte, Tony [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 11, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Diagnose Slow System

I apologize if I missed the final post.  Did you discovered the cause of the problem?

Tony Aponte

-----Original Message-----
From: Baker, Barbara [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Diagnose Slow System

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

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: [EMAIL PROTECTED] (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).