| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Load test; v$session.status=ACTIVE and v$session_wait.event=SQL*Net message from client
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
(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:23SQL*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)
If this is of any help, there were also a bunch of locks, that I didn't get a chance to catch, but which I suspect were due to updating session information in the db, which is not usually a problem in production
From perfstat
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 4,590.25 308.02
Logical reads: 5,587.47 374.93
Block changes: 36.22 2.43
Physical reads: 7.72 0.52
Physical writes: 9.55 0.64
User calls: 744.13 49.93
Parses: 114.04 7.65
Hard parses: 0.83 0.06
Sorts: 59.58 4.00
Logons: 10.03 0.67
Executes: 411.64 27.62
Transactions: 14.90
% Blocks changed per Read: 0.65 Recursive Call %: 35.11
Rollback per transaction %: 86.31 Rows per Sort: 13.79
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.99 Redo NoWait %: 99.99
Buffer Hit %: 99.86 In-memory Sort %: 99.90
Library Hit %: 99.86 Soft Parse %: 99.27
Execute to Parse %: 72.30 Latch Hit %: 99.57
Parse CPU to Parse Elapsd %: 47.63 % Non-Parse CPU: 96.15
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 18.72 24.27
% SQL with executions>1: 82.66 72.57
% Memory for SQL w/exec>1: 91.38 68.38
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------- ------------ ------------ ------- enqueue 17,214 689,823 84.46 SQL*Net break/reset to client 69,175 75,160 9.20 latch free 10,372 15,521 1.90 log file sync 5,520 10,711 1.31 direct path write 6,377 7,122 .87
The machine is a Sun e4500 w/ 8CPUs
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email
Received on Tue Aug 31 2004 - 14:34:01 CDT
 |
 |