Slow Connection to Database using Shared Server (Doc ID 837104.1)

Last updated on MAY 11, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 and later   [Release: 9.2 and later ]
Information 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 complete


And 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) = 90030

Shared 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