| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
<http://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-----
Sent: Tuesday, June 11, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L
I apologize if I missed the final post. Did you discovered the cause of the problem?
Tony Aponte
-----Original Message-----
<mailto:bbaker_at_denvernewspaperagency.com> ]
Sent: Thursday, May 23, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
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_WAITEDAVERAGE_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 P2P3
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 <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-LReceived on Sat Jun 15 2002 - 00:13:37 CDT
(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: Johnson, Michael INET: Michael.Johnson_at_oln-afmc.af.mil 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).
 |
 |