4 Replies Latest reply: Apr 1, 2013 7:54 AM by Fabricio_Jorge RSS

    Connecting to the db is taking too long with Shared Servers connection

    Fabricio_Jorge
      Hi all

      OS: Solaris 11 SPARC 64 bits
      DB: 2-node RAC Oracle 11.2.0.3.5

      After doing a migration of a 40GBs database from a 9i database to a 11g database, it's takin too long to connect to the db, which is using shared server. In the moment, there aren't any applications using the db, just myself. Doing some tests, i saw that when i use a dedicated connection, i can connect almost instantly, but using the shared connection, it takes almost 4 seconds to connect, via sqlplus. Here are some shared server parameters:
      shared_server_sessions               integer
      shared_servers                          integer     1
      max_shared_servers                   integer     20
      dispatchers                               string      (protocol=TCP)(disp=5)
      max_dispatchers                        integer     5
      
      lsnrctl service
      
      
        Instance "INSTANCE", status READY, has 6 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:5 refused:0 state:ready
               LOCAL SERVER
            "D004" established:93 refused:0 current:0 max:1022 state:ready
               DISPATCHER <machine: orahml01, pid: 4073>
               (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=65402))
            "D003" established:85 refused:0 current:0 max:1022 state:ready
               DISPATCHER <machine: orahml01, pid: 4070>
               (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=44055))
            "D002" established:90 refused:0 current:2 max:1022 state:ready
               DISPATCHER <machine: orahml01, pid: 4066>
               (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=41277))
            "D001" established:89 refused:0 current:1 max:1022 state:ready
               DISPATCHER <machine: orahml01, pid: 4062>
               (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=61887))
            "D000" established:91 refused:0 current:0 max:1022 state:ready
               DISPATCHER <machine: orahml01, pid: 4060>
               (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=44222))
      
        INSTANCE =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = hostname-scan)(PORT = 1521))
            (CONNECT_DATA =
              (SERVER = SHARED)
              (SERVICE_NAME = INSTANCE)
            )
        )
        
      INSTANCE1 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = hostname-vip)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = INSTANCE)
            (INSTANCE_NAME = INSTANCE1)
          )
        )  
        
      
      INSTANCE2 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = hostname-vip)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = INSTANCE)
            (INSTANCE_NAME = INSTANCE2)
          )
        ) 
      The alert.log doesn't show anything wrong.

      What can be causing this issue with shared servers?

      Thanks in advance.

      Edited by: Fabricio_Jorge on 27/03/2013 06:14