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"                                                                                     
                    <VIVEK_SHARMA_at_in        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    fosys.com>              cc:                                                                        
                    Sent by:                Subject:     RE: SQL*Net Connection under-performing                       
                    root_at_fatcity.com                                                                                   
                                                                                                                       
                                                                                                                       
                    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 <ORACLE-L_at_fatcity.com> 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:mantfield_at_connexus.net.au]
     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 <connect string>

          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: 
  INET: Rajesh.Rao_at_jpmchase.com

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: ListGuru_at_fatcity.com (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).