1 2 Previous Next 17 Replies Latest reply: Apr 19, 2011 1:28 PM by cj RSS

    PHP Oracle connection randomly slow

    528103
      Hello,

      I have a very strange problem concerning the connection to a oracle database with php. Sometimes the same SQL-Statement takes a very long time to load for no obvious reason (more than 5 minutes). But if you try it later it pops up at once. During the waiting time Toad is just showing "SQL*Net message from client" for the session.

      Here the code:

      $db = "<connection string>";

      $c = oci_connect("user", "pwd", $db);

      $s = ociparse($c, "select content from database WHERE CATEGORY = '$category'");

      if(ociexecute($s)) {
      while (ocifetch($s))
      {
      echo ociresult($s, "CONTENT");
      }
      }


      Maybe I should also mention that I'm using a page with frames with a navigation bar on the left which loads the code in the frame on the right with the variable $category. We are using Oracle9i Release 9.0.1 .

      I would really appreciate any help.
        • 1. Re: PHP Oracle connection randomly slow
          392393
          Does it do that with HTML pages as well?

          The reason I ask is because it might have to do with your web server doing reverse-dns lookups for the logging.

          ~Jer
          • 2. Re: PHP Oracle connection randomly slow
            Ivan Kartik
            The reason I ask is because it might have to do with your web server doing reverse-dns lookups for the logging.
            Good point, Jer. Of course you (user525100) can turn off reverse lookups in configuration of webserver.

            Other suggestions:
            1. You can turn on the sqlnet tracing on client (oracle) alon with server sqlnet tracing on Oracle database. Trace files will show you whether is problem with connectivity or not.
            2. I think SQL tracing on webserver will be more relevant than on TOAD.

            Little of topic suggestion:
            Seem you don't using Bind variables. If your application will be most accessed then better use them.
            • 3. Re: PHP Oracle connection randomly slow
              528103
              Thank you for your help!
              I tried to turn off all logging by:

              LogFormat " " common
              CustomLog logs/access.log common

              and DNS lookup was turned off: HostnameLookups Off

              Sadly this didn't resolve my problem and I don't have direct access to the database server. Any other ideas?
              • 4. Re: PHP Oracle connection randomly slow
                392393
                If you do sqlplus to the database server from the webserver, how long does it take to connect?

                Does the webserver take a while to serve requests that are of static pages? What about php scripts that aren't database-driven?

                ~Jer
                • 5. Re: PHP Oracle connection randomly slow
                  528103
                  If you do sqlplus to the database server from the
                  webserver, how long does it take to connect?
                  Almost without any delay. There are also other applications on the server working with the database and they don't have these problems. In addtition I added some debug code to output the execution time of the script. The most time is lost right after the execute statement.
                  Does the webserver take a while to serve requests
                  that are of static pages? What about php scripts
                  that aren't database-driven?
                  There is a wiki installed on the server. It has no connection to a databse and writes directly into test files. I couldn't observe the issue with these pages.

                  Here some information out of the phpinfo, which might be relevant:

                  PHP Version 5.0.2
                  Apache/2.0.52

                  oci8
                  OCI8 Support enabled
                  Revision $Revision: 1.257 $
                  Active Persistent Links 1
                  Active Links 1
                  Temporary Lob support enabled
                  Collections support enabled

                  Message was edited by:
                  user525100
                  • 6. Re: PHP Oracle connection randomly slow
                    392393
                    Wow, if the webserver works perfectly without oci, and Oracle works perfectly without the webserver, it looks like you've narrowed your problem down quite well...

                    I'm not entirely sure what to suggest other than asking you to recompile php with possibly a newer version.

                    ~Jer
                    • 7. Re: PHP Oracle connection randomly slow
                      392393
                      Hello,

                      I tried to update Xampp with apache 2.0.52 and PHP 5.0.2
                      and oci8 rev 1.257 to apache 2.2.2 and PHP 5.1.4 .

                      I uncommentet the line extension=php_oci8.dll and the
                      extension directory is correctly set by xampp.

                      And the first environment Path of the System is still
                      linked to ...\oracle\ora92\bin .

                      Starting the Apache server I get the error message: "The
                      procedure entry point OCILobRead2 could not be located
                      in the dynamic link library OCI.dll"

                      With the old version the only thing I had to do was
                      uncommenting the extension line, but with the new
                      version it simply doesn't want to work.

                      After renaming my backup folder with the old server
                      everything is working again. But not the new version.

                      Thanks for any help!
                      What are your environment variables for ORACLE_HOME and NLS_LANG? I know the newer versions of oci8 require those... is there anything else in the php change log that would point to differences? I don't think Apache is giving you the problems.

                      ~Jer
                      • 8. Re: PHP Oracle connection randomly slow
                        528103
                        I start the apache server with this small batch file:


                        set ORACLE_HOME = D:\oracle\ora92
                        set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

                        apache\bin\apache.exe



                        I added the option NLS_LANG, because you asked about it.
                        • 9. Re: PHP Oracle connection randomly slow
                          528103
                          In the changelog they only mentioned that they hard coded the library path to ORACLE_HOME/lib and this folder my folder D:\oracle\ora92\lib is at the right place. Irconically they said that they fixed a lot of problems. ;)
                          • 10. Re: PHP Oracle connection randomly slow
                            392393
                            I find it really strange that so many people are having trouble with php_oci8.dll on Win32.

                            Have you tried using the PECL extension instead of the one that comes with php?

                            http://pecl4win.php.net/ext.php/php_oci8.dll
                            • 11. Re: PHP Oracle connection randomly slow
                              528103
                              Thank you very much for your help.

                              I had to install the 10g client, because the new php version don't work together with client 9.2 .

                              Now my random performance problems are gone too.

                              I will definetly recommend this board!
                              • 12. Re: PHP Oracle connection randomly slow
                                392393
                                Okay thanks for letting us know what fixed it!

                                I've been using 10g since I started with php, so that's why I never ran into it...

                                ~Jer
                                • 13. Re: PHP Oracle connection randomly slow
                                  707757
                                  Even i am facing the same issue, i have oracle 11i instant client and 10g as my oracle database server, could this also be an issue ?
                                  • 14. Re: PHP Oracle connection randomly slow
                                    164043
                                    Perceived slowness is something that is dealt with by using process called "tuning". Tuning process requires an extensive knowledge because it should go from end to end, starting with the application. There may be an issue with your application, database, operating system, middle tier or network and one has to know all of the above to be able to tune the application properly. It's always the application one tunes, because it's the application that makes the end users unhappy. In your posts, there is not enough information to even begin thinking about tuning, so I will outline the tuning process for you. One always begins the tuning process by blaming the network and by letting the network engineers use sniffer and dig out the slow component. Typical response from the network people is "network is OK but the server XXX is slow with responses
                                    and that's what causing the timeouts"). Now you know where the problem is so you can concentrate on a single server and figure out what the problem is. Whatever the problem is, it is usually solved by improving the cache hit ratio by increasing the SGA. Network engineers are
                                    used to that and they do not mind, but if you want to be mr. nice guy, you can go directly to your boss and ask him for more memory. That is so called modified method C, by Cary Millsap. That is why it's called "method C".
                                    1 2 Previous Next