Bug #67002 [NEW]: function oci_fetch_all & similar really low

From: aurelien dot lequoy at esysteme dot com Operating system: Red Hat 6.3 PHP version: 5.5.10 Package: OCI8 related Bug Type: Bug Bug description:function oci_fetch_all & similar really low Description: ------------ I migrate a really big application from PHP 4.4 to PHP 5.5 some screen in PHP 4.4 took 1 sec or less, when I migrate to PHP 5.5 I got some trouble with execution time. The query took about ~1 sec in both case. after : oci_fetch_all php 4.4 => 1 sec oci_fetch_all php 5.5 => 16~18 sec (for the following query) I observed same problem with all other function like : - oci_fetch_array - oci_fetch_object with a loop in PHP About Oracle, I tried all version available of client on website : 10.1 11.2 12.1 .. About my installation : PHP 5.5 (from remi repo) Red hat 6.3 on 64 bit rpm -i client rpm -i devel pecl install oci8 Test script: --------------- select used : (return about 8 000 lines for 29 fields) WITH LISTE_IMPUTATION AS ( SELECT 'COURANTE' TYLIGNE, WPK.CDPRJ, WPK.CDWPK, PRJ.LIPRJ, WPK.LIWPK, NVL( ( SELECT INVALSIM FROM DBASIF.FF_HWP HWP WHERE HWP.CDPRJ =WPK.CDPRJ AND HWP.CDWPK =WPK.CDWPK AND HWP.DTMOISDHD='201402'),WPK.INVALSIM) INVALSIM, ( SELECT INVALWPM FROM DBASIF.FF_HWP HWP WHERE HWP.CDPRJ =WPK.CDPRJ AND HWP.CDWPK =WPK.CDWPK AND HWP.DTMOISDHD='201402') INVALWPM, WPK.CDWPL, ( SELECT LIUSER FROM DBASIF.IC_USER WHERE CDUSER=WPK.CDWPL) LIWPL, WPK.CDWPLD, ( SELECT LIUSER FROM DBASIF.IC_USER WHERE CDUSER=WPK.CDWPLD) LIWPLD, WPO.CDWPLR, IMP.CDORG, IMP.CDIMPU, IMP.LIIMPU, IMP.TYIMPU FROM DBASIF.FD_PRJ PRJ, DBASIF.FD_WPK WPK , DBASIF.FD_IMP IMP, DBASIF.FF_WPO WPO WHERE PRJ.CDORG= 'A0015' AND PRJ.CDPLFM = 'TGS_ELECTRIFICATION' AND WPK.CDPRJ =PRJ.CDPRJ AND IMP.CDPRJ =WPK.CDPRJ AND IMP.CDWPK =WPK.CDWPK AND WPO.CDORG(+) =WPK.CDORG AND WPO.CDWPL(+) =WPK.CDWPL) , LISTE_FORECAST AS ( SELECT IMP.CDORG, IMP.CDPRJ, IMP.CDWPK, IMP.LIPRJ, IMP.LIWPK, IMP.TYIMPU, IMP.CDIMPU, IMP.LIIMPU, IMP.INVALSIM, IMP.INVALWPM, IMP.CDWPL, IMP.LIWPL, IMP.CDWPLD, IMP.LIWPLD, IMP.CDWPLR, TRG.CDRESPM, PLG.TYRESS, PLG.CDDEP, PLG.LIDEP, TRIM(PLG.CDMAT) CDMAT, ( SELECT MAT.LIMAT FROM DBASIF.HD_MAT MAT WHERE MAT.CDORG=PLG.CDORG AND MAT.CDMAT =PLG.CDMAT) LIMAT, PLG.CDPOSTE, PLG.CDCOMP, PLG.CDSECT, PLG.NULIGNE, PLQ.DTMOISCO, PLQ.QTPREV, PLQ.QTHPREV, ( SELECT QTPREV FROM DBASIF.FF_PLQ_MAT_V WHERE CDORG =PLQ.CDORG AND CDMAT =PLG.CDMAT AND DTMOISCO =PLQ.DTMOISCO) QTPREV_TOT FROM LISTE_IMPUTATION IMP, DBASIF.FF_PLG PLG, DBASIF.FF_PLQ PLQ, DBASIF.FD_TRG TRG WHERE PLG.CDORG =IMP.CDORG AND PLG.TYIMPU =IMP.TYIMPU AND PLG.CDIMPU =IMP.CDIMPU AND PLG.TYLIGNE ='3' AND TRIM(PLG.NULIWPDRI) IS NULL AND TRIM(PLG.NULIWPDSA) IS NULL AND PLQ.CDORG =PLG.CDORG AND PLQ.TYIMPU =PLG.TYIMPU AND PLQ.CDIMPU =PLG.CDIMPU AND PLQ.NULIGNE =PLG.NULIGNE AND PLQ.DTMOISCO >'201402' AND TRG.CDORG =PLG.CDORG AND TRG.CDPOSTE =PLG.CDPOSTE AND PLQ.DTMOISCO<('201402'+200)) SELECT CASE WHEN TYLIGNE='DEP' THEN '6' WHEN TYRESS ='3' AND TRIM(CDMAT) IS NOT NULL THEN '5' WHEN TYRESS ='3' AND TRIM(CDMAT) IS NULL THEN '4' WHEN TYRESS IN ('1', '2') AND QTPREV_TOT>1.16 THEN '3' WHEN TYRESS ='2' AND QTPREV_TOT<=1.16 THEN '2' ELSE '1' END TYPE_AFF, CDPRJ, CDWPK, LIPRJ, LIWPK, INVALSIM, INVALWPM, CDWPL, LIWPL, CDWPLD, LIWPLD, CDWPLR, CDRESPM, TYIMPU, CDIMPU, LIIMPU, TYRESS, CDDEP, LIDEP, CDMAT, CASE WHEN TYRESS ='3' AND TRIM(CDMAT) IS NULL THEN CDPOSTE ELSE LIMAT END LIMAT, CDPOSTE, CDCOMP, CDSECT, NULIGNE, DTMOISCO, QTPREV, QTHPREV, QTPREV_TOT FROM ( SELECT 'FOR' TYLIGNE, CDPRJ, CDWPK, LIPRJ, LIWPK, INVALSIM, INVALWPM, CDWPL, LIWPL, CDWPLD, LIWPLD, CDWPLR, CDRESPM, TYIMPU, CDIMPU, LIIMPU, TYRESS, CDDEP, LIDEP, CDMAT, LIMAT, CDPOSTE, CDCOMP, CDSECT, NULIGNE, DTMOISCO, QTPREV, QTHPREV, QTPREV_TOT FROM LISTE_FORECAST) the script : $conn = oci_connect('hr', 'welcome', 'localhost/XE'); $stid = oci_parse($conn, 'see query on top'); oci_execute($stid); $nrows = oci_fetch_all($stid); => take all time Expected result: ---------------- execution time : 1 sec max (like in PHP 4.4) Actual result: -------------- execution time : 16 ~ 18 seconde for this case (can be more) -- Edit bug report at https://bugs.php.net/bug.php?id=67002&edit=1 -- Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=67002&r=trysnapshot54 Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=67002&r=trysnapshot55 Try a snapshot (trunk): https://bugs.php.net/fix.php?id=67002&r=trysnapshottrunk Fixed in SVN: https://bugs.php.net/fix.php?id=67002&r=fixed Fixed in release: https://bugs.php.net/fix.php?id=67002&r=alreadyfixed Need backtrace: https://bugs.php.net/fix.php?id=67002&r=needtrace Need Reproduce Script: https://bugs.php.net/fix.php?id=67002&r=needscript Try newer version: https://bugs.php.net/fix.php?id=67002&r=oldversion Not developer issue: https://bugs.php.net/fix.php?id=67002&r=support Expected behavior: https://bugs.php.net/fix.php?id=67002&r=notwrong Not enough info: https://bugs.php.net/fix.php?id=67002&r=notenoughinfo Submitted twice: https://bugs.php.net/fix.php?id=67002&r=submittedtwice register_globals: https://bugs.php.net/fix.php?id=67002&r=globals PHP 4 support discontinued: https://bugs.php.net/fix.php?id=67002&r=php4 Daylight Savings: https://bugs.php.net/fix.php?id=67002&r=dst IIS Stability: https://bugs.php.net/fix.php?id=67002&r=isapi Install GNU Sed: https://bugs.php.net/fix.php?id=67002&r=gnused Floating point limitations: https://bugs.php.net/fix.php?id=67002&r=float No Zend Extensions: https://bugs.php.net/fix.php?id=67002&r=nozend MySQL Configuration Error: https://bugs.php.net/fix.php?id=67002&r=mysqlcfg