connect string causes query to run very slow

[dharani]

Hi,

Oracle Version 8.1.6
OS Solaris 8

Whenever we telnet and execute a job without connect string, the job runs within 15 minutes. However if the same job is executed with connect string (@dbname), then the same job takes 2 hours. I am enclosing a zip file which contains listener.ora and tnsnames.ora. How to solve this problem ?

Thanks in advance.

Dharan

The following are entries of listener.ora and tnsnames.ora
# LISTENER.ORA Configuration File:/oracle/app/oracle/product/8.1.6/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.2.15)(PORT = 1521)(queuesize=5))
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(key = V816_extproc))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test.com)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)
(SID_NAME = utigs2)
)
(SID_DESC =
(GLOBAL_DBNAME = test.com)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)
(SID_NAME = test.com)
)
(SID_DESC =
(GLOBAL_DBNAME = test.com)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)
(SID_NAME = utigs1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1.com)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)
(SID_NAME = ugdm)
)
(SID_DESC =
(SID_NAME = V816)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)
(PROGRAM=extproc)
)
)


TNSNAMES.ORA

# TNSNAMES.ORA Configuration File:/oracle/app/oracle/product/8.1.6/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEMP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.1.2.15)(PORT = 1521))
)
(CONNECT_DATA =
(SID = temp)
)
)

TRASH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.1.2.15)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TRASH)
)
)

test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.2.15)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = test.com)
(instance_name=test)
)
)

extproc_connection_data =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC )(key = V816_extproc))
)
(CONNECT_DATA =
(SID = V816)
)
)

[nealh]

Your problem may not have anything to do with the network configuration. For example, if you're doing lots of row by row fetches across a slow network, you will get quite poor response time. Possible solutions would be to do more array fetches or to move most of the work into a stored procedure on the target machine. What type of job are you running exactly ?