This discussion is archived
6 Replies Latest reply: Jan 30, 2013 6:25 AM by 984246 RSS

PHP Query Hangs

894574 Newbie
Currently Being Moderated
Hardware: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
CodeIgniter: 2.0.3
Using the OCI8 diver.
PHP Version: 5.1.6

I'm running in to a problem where one of the columns types that we are selecting seems to be hanging the script. The type is a VARCHAR2(700). I'm not sure if its a memory problem or what. But for example WHERE rownum <= 30 may cause the browser to hang and sit there and never time out. But WHERE rownum <=29 may return all 29 rows. Now when I convert it TO_CLOB(column), this actually works and it wont hang, but of course takes longer the more rows I have to return.

Also, when the window hangs and I do a 'top' I see these sessions hagning also by apache:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13549 apache 15 0 178m 19m 9.9m S 0.0 0.5 0:01.45 httpd
16031 apache 15 0 178m 19m 9876 S 0.0 0.5 0:00.15 httpd
16464 apache 25 0 724m 7740 1444 S 0.0 0.2 0:00.01 httpd
17948 apache 15 0 178m 19m 9m S 0.0 0.5 0:00.37 httpd
22127 apache 18 0 177m 16m 7948 S 0.0 0.4 0:00.08 httpd
24141 apache 15 0 177m 16m 7948 S 0.0 0.4 0:00.08 httpd
24864 apache 18 0 175m 15m 8232 S 0.0 0.4 0:00.03 httpd
25873 apache 15 0 178m 20m 10m S 0.0 0.5 0:01.70 httpd
25895 apache 18 0 177m 18m 9612 S 0.0 0.4 0:01.31 httpd
26665 apache 15 0 177m 16m 8360 S 0.0 0.4 0:00.08 httpd

And it not only hangs in PHP but a PERL script using the OCI8 dll also hangs when trying to select data from that same column type.

So what is it about this VARCHAR2(700) thats killing my scripts? Im new to Oracle so thanks for any advice.
  • 1. Re: PHP Query Hangs
    cj Employee ACE
    Currently Being Moderated
    Try the query in SQL*Plus and try and narrow down the problem - is it data related? Are there trace files being generated by Oracle?

    If you use TO_CLOB in PHP you may not be actually fetching data unless you use the OCI-Lob methods.

    Regarding PHP, version 5.1.6 is awfully old. I hope you have updated the OCI8 driver using http://pecl.php.net/package/oci8
  • 2. Re: PHP Query Hangs
    894574 Newbie
    Currently Being Moderated
    Yes the query runs fine in SLQPLUS Im not sure how to check for trace files? I dont have admin access to the server. Im it does fetch the data when I do a ->read() on the object it displays just fine. I know the PHP is old. I dont have the auth to update it lol but the OCI8 driver is up-to-date I believe.
  • 3. Re: PHP Query Hangs
    cj Employee ACE
    Currently Being Moderated
    - Make sure you are using the PHP OCI8 extension versions 1.4.5 or 1.4.6. The phpinfo() command will show this
    - With the Oracle 11g client, look for 'oradia_*' directories and see if there is any usable Oracle info being reported
    - Try to get a reproducible test case complete with CREATE TABLE & INSERT statements to set up the environment
    - Try running your script in command-line PHP
    - It wouldn't hurt triple checking that SQL*Plus is using the same Oracle libraries and environment settings that PHP is using.
  • 4. Re: PHP Query Hangs
    894574 Newbie
    Currently Being Moderated
    -Using oci8 1.46
    -I have no access to the Oracle DB so I can't see any configuration data.
    -Script hangs in command line also and the point of the oci_execute or oci_parse.
    -How do I see how SQL*Plus is configed?

    Also before I thought I was having a problem with the VARCHAR2(700) but now seems to be any of the fields seem to be able to hang the query.

    Our schema data types. Even if I cut out the VARCHAR2(700) it still hangs. If the row amount is below a certain number it might work but if it gets above a certain number it hangs.

    NOT NULL NUMBER
    NOT NULL VARCHAR2(100)
    NOT NULL VARCHAR2(20)
    NOT NULL VARCHAR2(20)
    NOT NULL VARCHAR2(700)
    NOT NULL NUMBER
    NOT NULL NUMBER
    NOT NULL NUMBER

    Oh and I was wrong on the PHP type. I belive we are running 5.3.x

    Our PHP OCI phpinfo:
    OCI8 Support      enabled
    Version      1.4.6
    Revision      $Revision: 313688 $
    Active Persistent Connections      0
    Active Connections      0
    Oracle Run-time Client Library Version      11.2.0.2.0
    Oracle Instant Client Version      11.2
    Temporary Lob support      enabled
    Collections support      enabled

    Directive     Local Value     Master Value
    oci8.connection_class     no value     no value
    oci8.default_prefetch     100     100
    oci8.events     Off     Off
    oci8.max_persistent     -1     -1
    oci8.old_oci_close_semantics     Off     Off
    oci8.persistent_timeout     -1     -1
    oci8.ping_interval     60     60
    oci8.privileged_connect     Off     Off
    oci8.statement_cache_size     20     20

    Edited by: iczar1 on Oct 25, 2011 9:26 AM
  • 5. Re: PHP Query Hangs
    cj Employee ACE
    Currently Being Moderated
    Did you find any oradiag_* directories?

    Does the issue reproduce using command line PHP?

    What's special about the data? Is there a particular row that causes an issue?
    What does and doesn't work?

    Does it reproduce on another machine with PHP?

    Have you created a standalone test case (complete with CREATE TABLE and INSERT statements) so that other people (i.e. me) can see if it reproduces?
  • 6. Re: PHP Query Hangs
    984246 Newbie
    Currently Being Moderated
    Try oci8.default_prefetch = 0 (or 10, which was default value before PHP 5.3)

    -- Frosty

Legend

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