This discussion is archived
4 Replies Latest reply: Apr 1, 2013 5:54 AM by Fabricio_Jorge RSS

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

Fabricio_Jorge Explorer
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points