This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 19, 2011 11:28 AM by cj RSS

PHP Oracle connection randomly slow

528103 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
    IvanKartik Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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