Re: Load test; v$session.status=ACTIVE and v$session_wait.event=SQL*Net message from client

Jonathan,

Thank you for pointing this out.
I've taken a look at the 'STATE' column, and it's 'WAITING' state. What do you think this would indicate? It only showed 'WAITED KNOWN TIME' for the current session (session running this query).

On Wed, 1 Sep 2004 19:45:34 +0000 (UTC), "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Sep 01 2004 - 18:16:52 CDT