Database Software

Hello:

We are investigating a response time issue in our 10g RAC environment. I have written a small script which records the number of current sessions in a tracking table. The query is an INSERT INTO based on a SELECT. In running this, I noticed that most runs complete in 1/2 sec, but then some runs take over 15 secs to complete. Here are the TNSNAMES entries we use:

RMLPRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.137.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmlprac)
(INSTANCE_NAME = rmlprac2)
)
)

RMLPRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.137.8)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmlprac)
(INSTANCE_NAME = rmlprac1)
)
)

RMLPRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.137.8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.137.9)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmls_rmlp)
)
)

I use the time command to run the script:

rmlpdb01-bash $ time ./conn_log.sh

5 rows created.


real 0m15.554s
user 0m0.022s
sys 0m0.045s

In this case, it took 15 secs.. The next run:

rmlpdb01-bash $ time ./conn_log.sh

5 rows created.


real 0m0.541s
user 0m0.023s
sys 0m0.045s

took only 1/2 sec....

Here is the job:

#!/usr/bin/bash

# Count the number of application server processing running in Oracle database

echo "Script starting at `date`" >> /tmp/conn_log.log
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
export ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -S IADMIN/TEST123@RMLPRAC < conn_log.sql
echo "Script ending at `date`" >> /tmp/conn_log.log

and the SQL file:

insert into RMLS_APP_CONN_LOG
select sysdate,terminal,count(*) from gv$session where osuser='IUSR_RMLS'
group by terminal order by terminal;


If I change the job to use a particular instance in the rAC, like RMLPRAC1 or RMLPRAC2, the job only takes 1/4 sec CONSISTENTLY.. As soon as I change it back to the RAC configuration, RMLPRAC, the 15 sec runs return.

Could there be something in the service or in the tnsnames that is not configured properly? Is there a database parameter that needs to be adjusted? I would like to see consistent 1/2 second connects.. Our application is also experiencing intermittent "clockings" using the RAC connection string.. Is there possibly a listener registration issue?

Thanks.

Paul