I'm working on troubleshooting an oracle DB that's having some general performance problems. I ran the following query:
SELECT event AS "Event|Name",
total_waits "Total|Waits",
round(time_waited / 100, 0) "Seconds|Waiting",
total_timeouts "Total|Timeouts",
average_wait / 100 "Average|Wait|(in secs)"
FROM sys.v_$system_event e
ORDER BY time_waited DESC;
The first few lines returned as follows. Millions of seconds of wait time! (By comparison our other DBs are < 10 seconds of wait time for the top events.) What do these events do and what could cause these massive wait times? The DB has been up for 30 days so we're seeing aggregation over that much time.
Event Name Waits Seconds Timeouts Avg Wait
---------------------- --------- -------- -------- --------
SQL*Net message from client 488397968 32050594 0 0.0656
rdbms ipc message 91335556 2455744 9529486 0.0269
DIAG idle wait 5214769 347077 5214769 0.0666
Streams AQ: qmn coordinator idle wait 186521 173696 93278 0.9312
Streams AQ: qmn slave idle wait 95359 173692 51 1.8215
Space Manager: slave idle wait 523165 173647 521016 0.3319
pmon timer 968303 173630 870108 0.1793
fbar timer 8770 173403 8713 19.7723
smon timer 14103 173278 7006 12.2866
log file sync 57967889 90402 649458 0.0016
og file parallel write 86618366 39509 0 0.0005
db file sequential read 244286101 11171 0 0
control file parallel write 1274395 3949 0 0.0031
db file scattered read 157316868 1635 0 0
db file parallel read 11948170 1190 0 0.0001
-
"SQL*Net message from client" is the time spent by the database waiting to be asked to do something by a client (I would also interpret this to be an indicator of the number of SQL*Net requests processed by the database). AskTom has more information about the event. It doesn't look like a very long average wait, either, so perhaps you've got an app that's making LOTS of small requests to the server? That's a lot of waits in 30 days (average of 16M per day).
As for the rdbms ipc message, this means (Oracle 10g Reference):
"The background processes (LGWR, DBWR, LMS0) use this event to indicate that they are idle and are waiting for the foreground processes to send them an IPC message to do some work."
This is generally a non event from a tuning perspective. (Burleson)
Gary : Just to add that this may be a 'traditional' client/server type app where people log in at 9:00 and log off at 17:00 and have a database session all that times, mostly doing nothing.DCookie : Except in that case you would see high wait times but relatively few wait events, no?jeffspost : I've done some more research and it appears basically every SQL*Net message from client event is generating a small wait time. It's sort of like the system is working in slow-motion. Periodically through the day we have system wide slowdowns. I suspect at the root this is the cause--slower operations = more operations, eventually filling the capacity of the server. Any idea of why there would be an across the board delay for client responses?DCookie : This message means Oracle is waiting for the client to give it something to do. So, the issue is with the client apps or with the network. Anyone checked the NIC on the server? They do go bad sometimes. Switches and routers can go bad as well. Perhaps changing the port on a switch or router could eliminate that as a possibility.DCookie : Also, cables should be checked.From DCookie
0 comments:
Post a Comment