Oracle Database Discussions

We're running Oracle 10g on a Linux machine running a 2.8ghz P4 with 2GB of RAM on a local filesystem (ext3 on IDE).

We have it configured to run as a shared server, because our primary DB usage is from webservers. So, we anticipate approx. 350 to 500 connections at peak.

HOWEVER, whenever the number of connections reaches ~220, suddenly the clients (Apache instances) start getting the following:

ORA-12518: TNS:listener could not hand off client connection

over and over and over.

Here's a sample, from the listener.log:
29-SEP-2004 12:37:46 * (CONNECT_DATA=(SERVICE_NAME=ADS)(CID=(PROGRAM=)(HOST=update)(USER=apache))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=15123)) * establish * ADS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 104: Connection reset by peer


The two webservers (we haven't brought up the others yet, because of this problem) are performing atomic inserts, at a rate of ~25/sec on one, and 1 every 3 seconds on the other. Obviously, the problem is not performance.

Here's a sample of v$session for the connections.
select machine, server, count(*) from v$session group by machine, server ;
DEDICATED 1
update NONE 65 # connections from webserver on update
ads NONE 37 # connections from webserver on ads
ads* SHARED 2 # this never changes
ads* DEDICATED 13 # internal server connections

* (the database runs on the host 'ads').

The error starts occurring when the total of update+ads on "NONE" (idle, but connected) exceeds about 220.

Looking at oracle docs suggests we should tune the listener, but doesn't indicate WHAT to check/tune. The suggestion to trace the listener, which we attempted, gives megs and megs of data that doesn't indicate anything we can decipher to troubleshoot the problem.

# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ads)(PORT = 1521))
)
)
)

# we attempted "(QUEUESIZE=20)", which changed nothing.

The database has the following relevant parameters set (in fact, we haven't changed ANY other parameters except to increase the size of the sahred_pool memory to account for the high number of sessions):

sessions=1800 # yes this is high. we never come even close.
processes=250 # this is default, untouched
shared_servers=3 # I know this is more than necessary, one should be sufficient.
dispatchers=(PROTOCOL=TCP)(DISP=4) # oracle setup supplied (SERVICE=adsXDB) on this string, don't know why.


What should we check? What can we change? Obviously we're not running out of physical resources, but some configuration option or other is acting as a bottleneck.

Thanks in advance for your help!