1 Reply Latest reply: May 3, 2012 1:00 AM by Billy~Verreynne RSS

    Connecting SQLPlus / SQL Developer delayed

    910514
      Problem Description: I test connection to 2 node of Exadata Database by SQLPlus / SQL Developer. The connection to the node 1 is delayed.
      - Node 1 (delayed):
      + Local SQLPlus: 10s to connect
      + SQL Developer: 8-10s to connect
      - Node 2:
      + Local SQLPlus: 3s to connect
      + SQL Developer: 3-4s to connect

      I verified the issue from strace outputs, confirmed the timings between connections for Node 1, 2 are slightly slower.

      Node 2
      -------------
      26 seconds to connect !

      Node 1
      -------------
      42 seconds to connect !

      And
      *1*. Information for each node:
      - Node1:
      [oracle@dm01db01 ~]$ oifcfg getif
      bondeth0 10.70.52.0 global public
      bondib0 192.168.8.0 global cluster_interconnect

      [oracle@dm01db01 ~]$ ifconfig -a
      ......
      bondib0 Link encap:InfiniBand HWaddr 80:00:00:48:FE:80:00:00:00:00:00:00:00:00:00:00:00:00:00:00
      inet addr:192.168.10.1 Bcast:192.168.11.255 Mask:255.255.252.0
      inet6 addr: fe80::221:2800:1cf:31d7/64 Scope:Link
      UP BROADCAST RUNNING MASTER MULTICAST MTU:65520 Metric:1
      RX packets:6502981 errors:0 dropped:0 overruns:0 frame:0
      TX packets:6488528 errors:0 dropped:23 overruns:0 carrier:0
      collisions:0 txqueuelen:0
      RX bytes:3805391621 (3.5 GiB) TX bytes:4281266200 (3.9 GiB)

      bondib0:1 Link encap:InfiniBand HWaddr 80:00:00:48:FE:80:00:00:00:00:00:00:00:00:00:00:00:00:00:00
      inet addr:169.254.123.31 Bcast:169.254.255.255 Mask:255.255.0.0
      UP BROADCAST RUNNING MASTER MULTICAST MTU:65520 Metric:1

      SQL> select * from gv$cluster_interconnects;

      INST_ID NAME IP_ADDRESS IS_ SOURCE
      ---------- --------------- ---------------- --- -------------------------------
      1 bondib0:1 169.254.123.31 NO
      2 bondib0:1 169.254.185.50 NO

      - Node2:
      [oracle@dm01db02 ~]$ oifcfg getif
      bondeth0 10.70.52.0 global public
      bondib0 192.168.8.0 global cluster_interconnect

      [oracle@dm01db02 ~]$ ifconfig -a
      .....
      bondib0 Link encap:InfiniBand HWaddr 80:00:00:48:FE:80:00:00:00:00:00:00:00:00:00:00:00:00:00:00
      inet addr:192.168.10.2 Bcast:192.168.11.255 Mask:255.255.252.0
      inet6 addr: fe80::221:2800:1cf:231f/64 Scope:Link
      UP BROADCAST RUNNING MASTER MULTICAST MTU:65520 Metric:1
      RX packets:7397639 errors:0 dropped:0 overruns:0 frame:0
      TX packets:7600110 errors:2 dropped:143 overruns:0 carrier:0
      collisions:0 txqueuelen:0
      RX bytes:4915280920 (4.5 GiB) TX bytes:4496150701 (4.1 GiB)

      bondib0:1 Link encap:InfiniBand HWaddr 80:00:00:48:FE:80:00:00:00:00:00:00:00:00:00:00:00:00:00:00
      inet addr:169.254.185.50 Bcast:169.254.255.255 Mask:255.255.0.0
      UP BROADCAST RUNNING MASTER MULTICAST MTU:65520 Metric:1

      SQL> select * from gv$cluster_interconnects;

      INST_ID NAME IP_ADDRESS IS_ SOURCE
      ---------- --------------- ---------------- --- -------------------------------
      2 bondib0:1 169.254.185.50 NO
      1 bondib0:1 169.254.123.31 NO

      *2*. Will I change the cluster_interconnects parameter to this value? Is it correct?
      - On Node1:
      [oracle@dm01db01 ~] oifcfg delif -global bondib0

      SQL> alter system set cluster_interconnects = '192.168.10.1' scope=spfile sid='ccbs1' ;

      - On Node2:
      [oracle@dm01db02 ~] oifcfg delif -global bondib0

      SQL> alter system set cluster_interconnects = '192.168.10.2' scope=spfile sid='ccbs2' ;


      After setting "cluster_interconnects" parameter, the connections still slow. Please check strace urgently, because this issue impacts into the business operations.

      Thanks!
        • 1. Re: Connecting SQLPlus / SQL Developer delayed
          Billy~Verreynne
          Why post Infiniband device information? IB is used for the Interconnect and storage fabric layer. Not for the public ethernet layer - this is typically Gig Ethernet.

          A client connection connects to a Listener. This Listener can redirect the client to another Listener on another platform and/or port (e.g. SCAN Listener and load balancing behaviour). The Listener that accepts the connection, needs to hand it off to the database.

          If it is a dedicated server connection, the Listener starts a dedicated oracle executable process and hands the connection to it to service.

          If it is a shared server connection, the Listener hands the connection of to one of the dispatcher processes of the database instance, registered with the Listener.

          There are a number of moving parts here and any one of these could be causing a delay in the connection. Then there are issues like firewalls, application proxies, reverse DNS lookup when accepting a client connection, incorrectly configured NICs and switches (eg. half-duplex and not full duplex), and so on.

          So focussing on what seems to be the Interconnect as the reason for the slow client connection, does not make sense.

          Enable SQL*Net tracing on the client. Determine what happens from a client perspective when connecting. Change the client's connection string to test connecting to the SCAN Listener, a RAC Listener, and connecting without any client redirection (using SID instead of service name). Each of these will be handled differently and can potentially isolate the connection performance issue.

          If need be, also enable debug tracing on the Listener to determine how the it deals with the client connection and to determine if there are performance issues.