• 13

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191


File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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

"SQLNet 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 SQLNet 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)

  • 4
Reply Report
    • 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.
      • 1
    • 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?
    • 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.

selecting from v$system_event alone is of limited use. The view contains total wait times and counts for all wait events since the database was last started. The last start time the database was started could be a year ago or a a few minutes ago but either way I'm generally only interested what is happening now with my sql statement, batch job or end users. End users wait for non-idle non-background waits but v$system_event contains all the events including over 50 idle events and many waits for background, non-user, processes that take care of the database. Rather than running this query, try running and AWR report or statspack report or looking at the performance or top activity screen in Enterprise Manager. a quick way to see what is happening right now on the database is to run the following select statement:

select  nvl(s.username,s.program) username
,   s.sid sid
,   s.serial# serial
,   s.sql_hash_value sql_hash_value
,   substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event
,   w.p1 p1
,   w.p2 p2
,   w.p3 p3
from    v$session s
,   v$session_wait w
where   w.sid=s.sid
and s.status='ACTIVE'
and s.type='USER';

which will tell you who is waiting and who is on or requesting CPU. By running the query over and over you can get a sense of what bottlenecks there are on a system. Oracle's 10g and higher view v$active_session_history contains this information sampled once a second. Its much more powerful data than v$system_event but the data can be hard to mine. To mine the data you can use Enterprise Manager's top activity screen but if you don't have OEM there is a cool free tool


  • 0
Reply Report

Trending Tags