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

Hi from Paris

regarding the statspack report, most of the wait time for you client session is spent in waiting for an incompatible lock to be released (enqueue)...

this is directly due to a poor application design : a session had locked some object (for example, with a "select for update", or "even update"), and other sessions are waiting for this lock to be released....

have a look a the concurrent processes algorithms, and when you are benchmarking, have a look at V$LOCKED_OBJECT, and V$LOCK, in order to see who is locking objects, and is perturbing other sessions, and mayber why....

"NetComrade" <netcomradeNSPAM_at_bookexchange.net> a écrit dans le message de 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
>
> (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)
>
> 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 - 15:52:03 CDT