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.

Locks in Oracle Database

Locks are held by Oracle Database to maintain the integrity during the concurrent updates. Locks are held until the transaction in a session is committed or rollbacked. Oracle maintains row level lock in the tables that are recently being updated.

In the distributed transaction, the network connection also plays vital role during locking. Once locked transaction in this mode never released if the failure in completion of processing on the remote side. This may also result in the object lock so that complete system may be affected.

So, it is the database administrator job to look after the database as well as to monitor the network passage side by side. Usually Oracle Database administrator use
v$locked_object to monitor the lock objects in the oracle database. One can get the session information and object held in the lock by linking v$locked_object to
v$session and dba_objects. Use folowing query to check the lock held in Oracle Database.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

You may kill the session to release the lock by

alter system kill session 'SID,SERIAL#';

Oracle releases locks during the restart of the database.

How to decrease the size of the datafiles

The dba_data_files view gives information about the physical datafiles in the database whereas dba_free_space view gives information about the free space in the datafiles with their related block id. The query below gives the datafiles free space and total space allocated for the datafiles.

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

With this information one can decrease the size of the datafile by the free space with the command given below:

alter database datafile 'filepath\filename' resize filesize;

Free Space in Percentage Tablespace Wise

select z.TABLESPACE_NAME,sum(z.free_space_in_mb) free_space_in_mb,
sum(z.total_space_allocated_in_mb) total_space_allocated_in_mb,
round(sum(z.free_space_in_mb)/sum(z.total_space_allocated_in_mb)*100,2) free_pct
from (
select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
) z
group by z.tablespace_name
order by z.TABLESPACE_NAME;

Free Space in Percentage At Data File Level

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb, round((free_space/a.BYTES),2)*100 free_pct
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;