6 Replies Latest reply on Feb 12, 2013 5:00 PM by 989582

    Instant client connection time

      Hello everyone,

      We were connecting to a Oracle 10g server via Instant Client on Linux (from PHP) for many months without any issues. For 1 week now, there is a very slow connection time (oci_connect) to the server.

      $dbstr ="(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =$ip)(PORT = 1521))
      (SERVICE_NAME = platon.darwin.int)
      $conn = oci_connect($username,$password, $dbstr,'AL32UTF8');

      Connection time vary from 28 to 35 seconds. So everytime we close the connection and reopen it, we have to wait 30 seconds or so for the connection to be established.

      I was wondering if anyone out there had experienced the same issue so we can have a clue? I think it's an issue with firewall or the listener on the server, but not on our end (the PHP programmers).

      Thanks for your help!
        • 1. Re: Instant client connection time
          Laurenz Albe
          If you run your program on a different machine, does it have the same problem?
          Do other Oracle client programs on the problematic machine show the same symptoms?
          Do other TCP connection attempts between client machine and server machine have similar problems?

          You could turn on Oracle Net tracing on the client and see where the time is spent.

          Laurenz Albe
          • 2. Re: Instant client connection time
            Thanks for your tip.

            - We tried to setup the oci8 extension + instant client (over Linux) and we are facing other issues... :-( "Permission denied", so it's related to Linux, not Oracle. We will success sooner or later. :-)
            - It's the only Oracle program on this machine
            - I will try to test other TCP connections, this is a very good idea.

            Also, thanks for sharing the Oracle Net tracing tool, we will try and let you know.

            Cheers !
            • 3. Re: Instant client connection time
              Laurenz Albe
              Another thing that came to my mind is that you should also experiment with the command line client sqlplus.
              That is maybe easier than using PHP for connection tests, and it helps to differentiate whether it is
              a PHP/web server problem or in Oracle and below.

              Laurenz Albe
              • 4. Re: Instant client connection time
                Laurenz suggestion of using SQL*Plus is good. Try it from the machine with the database, and also from the machine with PHP. Grab the SQL*Plus Instant Client bundle for the latter.

                Are you really using the machine's IP address? If not, try it because a fast connection with the IP and a slow connection with the hostname might indicate a DNS issue.

                If you're running a web applications, can you use oci_pconnect() to help alleviate the problem in the short term? Or do you end up overloading the DB server (because Oracle DB 10g doesn't have 11g's DRCP pooled connections)?

                You can simplify your connection code to be:
                $conn = oci_connect($username,$password, "${ip}/platon.darwin.int" ,'AL32UTF8');
                See slide 24 of http://www.oracle.com/technetwork/database/enterprise-edition/oow11-14345-net-services-514484.pdf
                Changing this won't affect your issue but will make the code more obvious and less error prone.
                • 5. Re: Instant client connection time
                  Hello everyone,

                  Thanks for your very useful replies.

                  As Laurenz suggested, I succesfully installed the application on another server (same code). The queries are very fast. So the issue is ONLY on the LIVE server. I will install SQL*Plus and use this tool to test more the connection between the 2 machines. Also, I made sure the IP address is used. I will use also your suggestion, cj. Thanks.

                  I will get back with some results soon.
                  • 6. Re: Instant client connection time
                    Hi !

                    I have installed sqlplus on both test and production server and the results are same as with PHP: very fast on test server and very slow on prod server. The connection takes again 30 sec. (less or more) and each query (DESC table_name) takes 20-30 sec.
                    Unix prompt# sqlplus username/password@ip.ip.ip.ip:1521/service_name = 30 sec.
                    Connected to Oracle database 10g Release - Production
                    desc jbd_f_aux = 30 sec.
                    AUX_ADR1 VARCHAR2(60)
                    desc jbd_f_dossier = 30 sec.
                    DOS_AUX VARCHAR2(20)
                    select aux_adr1 from jbd_f_aux; = very quick
                    select dos_aux from jbd_f_dossier; = very quick
                    It seems the DESCRIBE command and the first connection take very long, but not the queries... What could it be then?

                    Thanks for your highly appreciated comments and help.

                    Edited by: 986579 on Feb 12, 2013 9:00 AM