Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated July 27th
"NetComrade" <netcomradeNSPAM_at_bookexchange.net> wrote in message
news:4134caec.520297307_at_localhost...
> Hi,
>
> We made an attempt to load test our site today, and at around 1600
> concurrent users the pages started showing problems. The App servers
> were all busy, indicating a 'waiting for db', however the db server,
> even though generally busy (on OS level idle time <10%), didn't seem
> to have any major standing out issues. What perplexed me is the output
> of the following query:
>
> select s.sid||','||serial#
> sidc,username||'@'||machine||'('||osuser||'|'||program||')' username,
> event||' '||p1||','||p2||','||p3 event, seconds_in_wait secs,
> sql_hash_value sqlhashval,/*row_wait_obj#,*/logon_time
> from v$session s, v$session_wait sw
> where
> status='ACTIVE'
> and s.sid=sw.sid
> order by logon_time
>
You haven't restricted your query to v$session_wait where
state = 'WAITING'. I would guess that the status on the
rows showing the message from client are all showing a
state of 'WAITED KNOWN TIME' - which makes the
wait the last thing that the session waited for, not the thing
it is currently waiting for: at present it is consuming CPU.
> (username,sid infodeleted for space saving)
> EVENT SECS SQLHASHVAL LOGON_TIME
> ------------------------------ ---- ---------- --------------------
> pmon timer 300,0,0 39 0 31-aug-2004 13:59:04
> rdbms ipc message 300,0,0 1 0 31-aug-2004 13:59:04
> rdbms ipc message 184,0,0 0 0 31-aug-2004 13:59:04
> rdbms ipc message 300,0,0 0 0 31-aug-2004 13:59:04
> mon timer 300,0,0 889 1714733582 31-aug-2004 13:59:04
> rdbms ipc message 180000,0,0 839 189272129 31-aug-2004 13:59:04
> SQL*Net message from client 12 0 1702236704 31-aug-2004 14:11:20
> SQL*Net message from client 12 0 2873088112 31-aug-2004 14:11:24
> SQL*Net message to client 1297 0 194973690 31-aug-2004 14:11:28
> SQL*Net message from client 12 0 1702236704 31-aug-2004 14:11:41
> SQL*Net message from client 12 0 3385580940 31-aug-2004 14:12:16
> SQL*Net message from client 12 0 1556824455 31-aug-2004 14:12:17
> SQL*Net message from client 12 0 2039124575 31-aug-2004 14:12:19
> SQL*Net message from client 12 0 2686954451 31-aug-2004 14:12:23
> SQL*Net message from client 12 0 1702236704 31-aug-2004 14:13:21
> SQL*Net message from client 12 0 2819921362 31-aug-2004 14:14:00
>
>
> The first 6 is pmon/dbwr, and the like
> The rest are web server/app server sessions, minus the 'to client'
> one, which was the current session.
>
> The question is, in what cases what a session be 'ACTIVE' and waiting
> for something from a client? During high load, I'd waits such as on
> IO, or memory ('buffer busy'), or log switching or latch contention
> (which happenned a little here, since this is MTS, plus there are some
> hard parses)
>
Received on Wed Sep 01 2004 - 14:45:34 CDT
