Bug #67002 [Opn->Nab]: function oci_fetch_all & similar really low

Edit report at https://bugs.php.net/bug.php?id=67002&edit=1 ID: 67002 Updated by: sixd@php.net Reported by: aurelien dot lequoy at esysteme dot com Summary: function oci_fetch_all & similar really low -Status: Open +Status: Not a bug Type: Bug Package: OCI8 related Operating System: Red Hat 6.3 PHP Version: 5.5.10 -Assigned To: +Assigned To: sixd Block user comment: N Private report: N New Comment: Sorry, but your problem does not imply a bug in PHP itself. For a list of more appropriate places to ask for help using PHP, please visit http://www.php.net/support.php as this bug system is not the appropriate forum for asking support questions. Due to the volume of reports we can not explain in detail here why your report is not a bug. The support channels will be able to provide an explanation for you. Thank you for your interest in PHP. --------- The PHP bug db is not a support forum. Please ask your question on a PHP mail list, or at http://www.oracle.com/technetwork/forums/php/index.html You will need to create a reproducable test case that includes sample data. Provide timing data to show which OCI8 calls are taking time (i.e. to rule out oci_connect as an issue), Also, review tuning options such as oci8.default_prefetch http://www.php.net/manual/en/oci8.configuration.php Previous Comments: ------------------------------------------------------------------------ [2014-04-02 00:36:18] aurelien dot lequoy at esysteme dot com 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 this bug report at https://bugs.php.net/bug.php?id=67002&edit=1