6 Replies Latest reply: Jan 30, 2013 8:25 AM by 984246 RSS

    PHP Query Hangs

    894574
      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
          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
            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
              - 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
                -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
                  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
                    Try oci8.default_prefetch = 0 (or 10, which was default value before PHP 5.3)

                    -- Frosty