Performance and Database Connections

Various performances related issue is typically caused by large number of database connection and we have to consider following major areas that can be checked and adjusted for the number of oracle database connections in oracle 9i.

If we are using the different server names for the same oracle database (TNS names) that will create additional, unnecessary connections. Therefore server name and TNS names both must match and case sensitive.


Oracle database doesn’t do the sufficient locking to prevent conflicts between connections on the same server. The conflicts can show up under very high database usage and timing, and only from a few application plus major factor about the timer initiated disconnection and dead connection detention feature that allows SQL NET to identify connection that have been left hanging by the abnormal termination of a client and unfortunately this feature is available only after the release of 2.1 and later. And only if the SQL NET can identify dead connections due to the client process or machine being unreachable, the connection will be closed when an error is generated by the send operation and server process will exit and a small probe packet will be sent from the server to client at user defined interval and that will automatically forces a database rollback of uncommitted transactions and locks held by the user of the broken connection and allow further regular continuous transactions. Thus, this feature minimizes the waste of resources by connections that are no longer valid plus we can specify the expire time in SQLNET.ORA of database server side (file usually in $ORACLE_HOME/network/admin) which will direct SQLNET to send a probe to the client through the network and if the client doesn’t respond, it will be killed.


MAX CONNECTION parameter: We have to consider the total number of db connections for dbusers per data source. Additional database connection requests beyond this value will be queued for the next available connection. If this value is exceeded, the user request will fail generating errors in the log file.

minConnection=5
maxConnection=50
initialConnection=5
poolGrowth=5

Run a script on the database server, each node with incoming connections grouped by
a. db user
b. program
c. machine name

Script:

select username, count(*) from v$session
group by username;
select machine count(*)
from v$session
group by machine;

select program, count(*)
from v$session
group by program;

Note: Outcome of this can be send to the developers and they will have to take corrective steps if necessary.