Slow Connection to Database using Shared Server
Last updated on MAY 11, 2011
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1Information in this document applies to any platform.
Symptoms
Intermittently Shared Server connections are slow. Taking anything up to 3-5 seconds for connection to establish, when a large amount of connections are attempted in a small time period. (Login Storm)Oracle Net Client trace, shows the delay is seen after the connection handshake
[05-NOV-2008 08:14:58:590] nscon: sending NSPTCN packet
[05-NOV-2008 08:14:58:599] nscon: got NSPTRS packet
[05-NOV-2008 08:14:58:601] nscon: sending NSPTCN packet
[05-NOV-2008 08:14:58:610] nscon: got NSPTAC packet
[05-NOV-2008 08:14:58:610] nscon: connect handshake is completeAnd the delay is then seen on nttrd (read)
[05-NOV-2008 08:14:58:623] nttrd: entry
[05-NOV-2008 08:15:01:029] nttrd: socket 19 had bytes read=180 Matching Strace shows delay on read
2846 08:14:58 write(11, "entry\n", 6) = 6
2846 08:14:58 read(19, "\0\264\0\0\6\0\0\0\0\0\1\6\0x86_64/Linux 2.4.xx"..., 2064) = 180
2846 08:15:01 lseek(11, 0, SEEK_CUR) = 90030Shared server output does not show busy shared servers
SQL> SELECT NAME,requests,
2 To_Char(busy*100/(busy+idle),'09D99') "Busy %",
3 status
4 FROM v$shared_server
5 WHERE status != 'QUIT'
6 ORDER BY 3 desc;
NAME REQUESTS Busy % STATUS
---- ---------- ------ ----------------
S011 0 52,67 TERMINATED
S006 3253 30,04 WAIT(RECEIVE)
S000 3336 13,41 WAIT(RECEIVE)
S005 669 12,38 WAIT(RECEIVE)
S001 2025 10,39 WAIT(RECEIVE)
S003 15137 05,43 WAIT(RECEIVE)
S002 834 04,33 WAIT(RECEIVE)
S007 3619 01,18 WAIT(COMMON)
S008 2311 01,01 EXEC
S004 9127 00,51 WAIT(RECEIVE)
S009 57 00,01 WAIT(COMMON) Shared Server information explained in <Document 1005259.6>
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms