Connection pool,sqlnet,listener related issues/questions

Advise required on inbound paramters , prespawn servers and db resident connection

Question:

The application does not support persistent connection. currently we find time out errors. In order to fix the issue we seek your advise on the following:
currently all of the following are default values
SQLNET.INBOUND_CONNECT_TIMEOUT, — can we make it 10 seconds ? or what value you suggest

SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT

INBOUND_CONNECT_TIMEOUT_listenernam — planning to make 5 seconds; what are your suggestios.

2. ours is 11.2.0.1 ; whether prspawn server is still there in 11g R2. Will it be helpful for application supporting persistent connection or not

3. Regarding DRCP for those applications not supporting persistent connection , similarly do we have any answer for applications having persistent connections but at the same time avoiding logon storms.

Answer:

Well it sounds like you need to increase the processes parameters to allow the logon storms. Or setup a connection pool from the apache side to prevent the number of connections from hitting the server to increase.

Otherwise from the server side any values you set other then increasing the processes value to handle the load will report errors to the client trying to connect.
For example if you set the INBOUND_CONNECT_TIMEOUT then the client has to authenticate in that timeframe or it will error and report the 3136 error. But that defaults to 60 seconds to prevent any good connections from being booted just for being slow.

Now if you hit the processes value it will start reporting the 12518 and should trigger a connection error so apache should stop trying to force the connections at that point. If it continues then it will get errors and there is not much you can do. From the Oracle side you cannot really control how the client connects. If they cause a storm you either configure to handle the load or accept the fact the system will generate errors and not service the storm.
From the server side most of the parameters we have are to limit or control the amount of connections that are allowed to the server itself. If the client keeps trying to connect in a storm fashion there is not much you can do to prevent it on the DB you simply need to make it possible for those processes to connect.

the only real option from the server is to bump up the processes parameter to make sure you can handle the full load and have them setup a connection pool on the apache side to limit the total number of connections to the database from the web server to prevent them from hitting the processes value.