RE: SQL*Net Connection under-performing
tnsping for tbasun Database (below) 10 ms
whereas tnsping for bby01 Database (below) is 10,000 ms
Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for tbasun ?
NOTE - tnsnames.ora file put on the Database(DB) Server itself & tnsping also being
issued from the DB Server itself .
Thanks
tnsnames.ora being used :-
tbasun =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = tbasun)
(SRVR = DEDICATED)
)
)
bby01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = bby01)
(SRVR = DEDICATED)
)
)
-Original Message-
Sent: Tuesday, February 04, 2003 8:20 PM
To: VIVEK_SHARMA
Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the
IP address for both the databases?
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Ferenec , List
tnsnames.ora has been setup on the Database Server itself & tnsping is
being issued also from the Database Server itself to avoid any network
path.
For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the
2 Databases on the DB Server machine is taking 10,000 ms & 10 ms
respectively
Can there be any overheads / other concerns with usign this parameter , as
respective machines are Production Servers ?
Thanks indeed
-Original Message-
From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 04, 2003 1:10 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: SQL*Net Connection under-performing
Vivek
I had similar issues with a large customer. I solved this by setting
up pre-spawned dedicated processes in the listener. Full in structions
available on Metalink. I brought down 1 minute login times to under 10
seconds, by configuring 20 - 40 prespawned connections. Let me know
how it works. This can be done relatively easily even in production,
configure listener.ora and then simply bounce the listener. Your
initial login after that may take you a minute until all the
prespawned connections are initiated.
HTH. Regards :
Ferenc Mantfeld
Dreaming costs you nothing. Not dreaming costs you everything.
- Original Message -
From: VIVEK_SHARMA
To: Multiple recipients of list ORACLE-L
Sent: Monday, February 03, 2003 6:58 PM
Subject: SQL*Net Connection under-performing
SITUATION - With DB Server CPU Idle being 0 % & load average 60-70
% :-
tnsping Connect to one of the Databases (1) takes about 10,000 ms
truss -fdD tnsping
29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?"..,
2064) = 75
Thus 18 seconds taken as shown above
Whereas to another Database (2) tnsping takes only 10 ms on the Same
machine
DETAILS :-
0) 1 GBPS network path exits from APP Servers to the Database Server.
1) Database 1 Has about 3500 Oracle processes connecting to it
Database 2 Has about 1500 Oracle processes connecting to it
2) Both Databases have Different ORACLE_HOMEs
3) sqlnet.ora of Database 1 :-
AUTOMATIC_IPC=OFF
TRACE_LEVEL_CLIENT=OFF
TRACE_LEVEL_SERVER=OFF
TNSPING.TRACE_LEVEL=OFF
NO sqlnet.ora exists on Database 2
4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir
of ORACLE_HOME for Both Database
5) Multiple listener processes are Running for BOTH Databases
Qs How may the issue be approached ?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
You shouldn't be needing the LOAD_BALANCE for bby01, right ? Try taking it out. Hemant At 06:58 AM 07-02-03 -0800, you wrote: Rajesh , List tnsping for tbasun Database (below) 10 ms whereas tnsping for bby01 Database (below) is 10,000 ms Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for tbasun ? NOTE - tnsnames.ora file put on the Database Server itself & tnsping also being issued from the SAME (DB Server) Thanks tnsnames.ora being used :- tbasun = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = tbasun) (SRVR = DEDICATED) ) ) bby01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = bby01) (SRVR = DEDICATED) ) ) -Original Message- Sent: Tuesday, February 04, 2003 8:20 PM To: VIVEK_SHARMA Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the IP address for both the databases? To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Ferenec , List tnsnames.ora has been setup on the Database Server itself & tnsping is being issued also from the Database Server itself to avoid any network path. For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the 2 Databases on the DB Server machine is taking 10,000 ms & 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message- From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 1:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % & load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFF TRACE_LEVEL_CLIENT=OFF TRACE_LEVEL_SERVER=OFF TNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, Ca
RE: SQL*Net Connection under-performing
Vivek,
I am wondering why do you have load balancing turned on for BBY01, when you
have just one listener? Whats the MTS_DISPATCHER set to?
Raj
"VIVEK_SHARMA"
fosys.com> cc:
Sent by:Subject: RE: SQL*Net Connection
under-performing
[EMAIL PROTECTED]
February 07,
2003 09:58 AM
Please respond
to ORACLE-L
Rajesh , List
tnsping for tbasun Database (below) 10 ms
whereas tnsping for bby01 Database (below) is 10,000 ms
Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for
tbasun ?
NOTE - tnsnames.ora file put on the Database Server itself & tnsping also
being issued from the SAME (DB Server)
Thanks
tnsnames.ora being used :-
tbasun =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = tbasun)
(SRVR = DEDICATED)
)
)
bby01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = bby01)
(SRVR = DEDICATED)
)
)
-Original Message-
Sent: Tuesday, February 04, 2003 8:20 PM
To: VIVEK_SHARMA
Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the
IP address for both the databases?
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Ferenec , List
tnsnames.ora has been setup on the Database Server itself & tnsping is
being issued also from the Database Server itself to avoid any network
path.
For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the
2 Databases on the DB Server machine is taking 10,000 ms & 10 ms
respectively
Can there be any overheads / other concerns with usign this parameter , as
respective machines are Production Servers ?
Thanks indeed
-Original Message-
From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 04, 2003 1:10 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: SQL*Net Connection under-performing
Vivek
I had similar issues with a large customer. I solved this by setting
up pre-spawned dedicated processes in the listener. Full in structions
available on Metalink. I brought down 1 minute login times to under 10
seconds, by configuring 20 - 40 prespawned connections. Let me know
how it works. This can be done relatively easily even in production,
configure listener.ora and then simply bounce the listener. Your
initial login after that may take you a minute until all the
prespawned connections are initiated.
HTH. Regards :
Ferenc Mantfeld
Dreaming costs you nothing. Not dreaming costs you everything.
- Original Message -
From: VIVEK_SHARMA
To: Multiple recipients of list ORACLE-L
Sent: Monday, February 03, 2003 6:58 PM
Subject: SQL*Net Connection under-performing
SITUATION - With DB Server CPU Idle being 0 % & load average 60-70
% :-
tnsping Connect to one of the Databases (1) takes about 10,000 ms
truss -fdD tnsping
29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?"..,
2064) = 75
Thus 18 seconds taken as shown above
Whereas to another Database (2) tnsping takes only 10 ms on the Same
machin
RE: SQL*Net Connection under-performing
Rajesh , List
tnsping for tbasun Database (below) 10 ms
whereas tnsping for bby01 Database (below) is 10,000 ms
Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for tbasun ?
NOTE - tnsnames.ora file put on the Database Server itself & tnsping also being issued
from the SAME (DB Server)
Thanks
tnsnames.ora being used :-
tbasun =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = tbasun)
(SRVR = DEDICATED)
)
)
bby01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = bby01)
(SRVR = DEDICATED)
)
)
-Original Message-
Sent: Tuesday, February 04, 2003 8:20 PM
To: VIVEK_SHARMA
Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the
IP address for both the databases?
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Ferenec , List
tnsnames.ora has been setup on the Database Server itself & tnsping is
being issued also from the Database Server itself to avoid any network
path.
For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the
2 Databases on the DB Server machine is taking 10,000 ms & 10 ms
respectively
Can there be any overheads / other concerns with usign this parameter , as
respective machines are Production Servers ?
Thanks indeed
-Original Message-
From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 04, 2003 1:10 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: SQL*Net Connection under-performing
Vivek
I had similar issues with a large customer. I solved this by setting
up pre-spawned dedicated processes in the listener. Full in structions
available on Metalink. I brought down 1 minute login times to under 10
seconds, by configuring 20 - 40 prespawned connections. Let me know
how it works. This can be done relatively easily even in production,
configure listener.ora and then simply bounce the listener. Your
initial login after that may take you a minute until all the
prespawned connections are initiated.
HTH. Regards :
Ferenc Mantfeld
Dreaming costs you nothing. Not dreaming costs you everything.
- Original Message -
From: VIVEK_SHARMA
To: Multiple recipients of list ORACLE-L
Sent: Monday, February 03, 2003 6:58 PM
Subject: SQL*Net Connection under-performing
SITUATION - With DB Server CPU Idle being 0 % & load average 60-70
% :-
tnsping Connect to one of the Databases (1) takes about 10,000 ms
truss -fdD tnsping
29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?"..,
2064) = 75
Thus 18 seconds taken as shown above
Whereas to another Database (2) tnsping takes only 10 ms on the Same
machine
DETAILS :-
0) 1 GBPS network path exits from APP Servers to the Database Server.
1) Database 1 Has about 3500 Oracle processes connecting to it
Database 2 Has about 1500 Oracle processes connecting to it
2) Both Databases have Different ORACLE_HOMEs
3) sqlnet.ora of Database 1 :-
AUTOMATIC_IPC=OFF
TRACE_LEVEL_CLIENT=OFF
TRACE_LEVEL_SERVER=OFF
TNSPING.TRACE_LEVEL=OFF
NO sqlnet.ora exists on Database 2
4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir
of ORACLE_HOME for Both Database
5) Multiple listener processes are Running for BOTH Databases
Qs How may the issue be approached ?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
"tnsping" has nothing to do with the database -- it just "looks - up" the listener to see if the listener is listening for that service. Therefore, the number of processes connected to the database makes no difference. Could you post the TNSNAMES.ORA file here ? Hemant At 01:16 AM 04-02-03 -0800, you wrote: Ferenec , List tnsnames.ora has been setup on the Database Server itself & tnsping is being issued also from the Database Server itself to avoid any network path. For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the 2 Databases on the DB Server machine is taking 10,000 ms & 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message- From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 1:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % & load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFF TRACE_LEVEL_CLIENT=OFF TRACE_LEVEL_SERVER=OFF TNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ? Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
Title: Details about Tar logged Ferenec , List tnsnames.ora has been setup on the Database Server itself & tnsping is being issued also from the Database Server itself to avoid any network path. For the SAME ORACLE_HOME & using the SAME tnsnames.ora file tnsping to the 2 Databases on the DB Server machine is taking 10,000 ms & 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message-From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 04, 2003 1:10 AMTo: Multiple recipients of list ORACLE-LSubject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc MantfeldDreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % & load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFFTRACE_LEVEL_CLIENT=OFFTRACE_LEVEL_SERVER=OFFTNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ?
Re: SQL*Net Connection under-performing
Title: Details about Tar logged Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc MantfeldDreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % & load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFFTRACE_LEVEL_CLIENT=OFFTRACE_LEVEL_SERVER=OFFTNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ?
Re: SQL*Net Connection under-performing
What does tnsping from other machines to the slow database, database 1, return? Is this a problem only from this one machine or from many machines? I would wager that the problem is that database 1 & 2 are being resolved through different paths at the TNS level. Perhaps one is using a local tnsnames.ora file and another is going to the DNS server? If you're trying to resolve database 1 via DNS, discovering that it's not in the DNS server, then resolving it through another approach (Oracle Names, tnsnames.ora, etc), that could account for the difference. Along the same lines, I'd check to see whether the host name for either database is in /etc/hosts (or the Windows lmhosts file). Defining the values there bypasses the call to the DNS server. At 12:58 AM 2/3/2003, VIVEK_SHARMA wrote: SITUATION - With DB Server CPU Idle being 0 % & load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFF TRACE_LEVEL_CLIENT=OFF TRACE_LEVEL_SERVER=OFF TNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ? Justin Cave Distributed Database Consulting
