Oracle PL / SQL Developer,PHP Laravel性能差异

I have a Laravel app that generates reports about ~3k rows from an oracle 11g db server to work on a legacy system. My colleague also happens to have an asp.net app for another report. Both of us sometimes experience crashing when generating our reports but when I generate my report through PL/SQL Developer it only takes 3-10 mins while laravel takes 10m-1hr response time. I tried at the best of my knowledge to resolve it like provisioning 3gb of memory limit but its no use.

How do I find the root cause of this problem?

Laravel 7 x86

OCI 11g

EDIT..

I use the same query to run in my laravel app and in PL/SQL Developer.

UPDATE..

Here's the query I am having trouble with.

SELECT *
    FROM (select 'LOANS' MODULE,
                 a.branchid,
                 a.loanno AGREEMENTID,
                 NULL ACCOUNTID,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINLEA.FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                b,
                 FV_FINLEA.NBFC_VOUCHER_HDR          H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND A.MODULEID = 'LEA'
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'LOANS' MODULE,
                 a.branchid,
                 a.loanno AGREEMENTID,
                 NULL ACCOUNTID,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINLEA.FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                b,
                 FV_FINTLR.NBFC_VOUCHER_HDR          H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND A.MODULEID = 'TLR'
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'SD' MODULE,
                 a.branchid,
                 NULL AGREEMENTID,
                 a.accountid,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINDEP.FD_FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                   b,
                 FV_FINDEP.NBFC_VOUCHER_HDR             H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND EXISTS
           (SELECT 1
                    FROM FV_FINDEP.SD_DEPOSIT_M C
                   WHERE A.ACCOUNTID = C.ACCOUNTID)
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'SD' MODULE,
                 a.branchid,
                 NULL AGREEMENTID,
                 a.accountid,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINDEP.FD_FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                   b,
                 FV_FINTLR.NBFC_VOUCHER_HDR             H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND EXISTS
           (SELECT 1
                    FROM FV_FINDEP.SD_DEPOSIT_M C
                   WHERE A.ACCOUNTID = C.ACCOUNTID)
             AND A.MODULEID = 'TLR'
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'FD' MODULE,
                 a.branchid,
                 NULL AGREEMENTID,
                 a.accountid,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINDEP.FD_FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                   b,
                 FV_FINDEP.NBFC_VOUCHER_HDR             H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND EXISTS
           (SELECT 1
                    FROM FV_FINDEP.FD_DEPOSIT_M C
                   WHERE A.ACCOUNTID = C.ACCOUNTID)
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'FD' MODULE,
                 a.branchid,
                 NULL AGREEMENTID,
                 a.accountid,
                 a.voucherid,
                 a.voucherdate,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 A.NARRATION
            from FV_FINDEP.FD_FINAL_VOUCHER_BREAKUP_DTL a,
                 FV_CASM.fa_groupgl_m                   b,
                 FV_FINTLR.NBFC_VOUCHER_HDR             H
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = H.VOUCHERID
             AND A.BRANCHID = H.BRANCHID
             AND EXISTS
           (SELECT 1
                    FROM FV_FINDEP.FD_DEPOSIT_M C
                   WHERE A.ACCOUNTID = C.ACCOUNTID)
             AND A.MODULEID = 'TLR'
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'TLR' MODULE,
                 a.branchid,
                 NULL AGREEMENTID,
                 NULL ACCOUNTID,
                 A.VOUCHERID,
                 A.VOUCHERDATE,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 F.NARRATION
            from FV_FINTLR.nbfc_voucher_dtl A,
                 FV_CASM.fa_groupgl_m       b,
                 FV_FINTLR.NBFC_VOUCHER_HDR F
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = F.VOUCHERID
             AND A.BRANCHID = F.BRANCHID
             and a.status IN ('A', 'P')
             and not exists (select 1
                    from FV_FINDEP.fd_final_voucher_breakup_dtl E
                   where a.voucherid = E.voucherid
                     AND A.BRANCHID = E.BRANCHID)
             and not exists
           (select 1
                    from FV_FINLEA.final_voucher_breakup_dtl E1
                   where a.voucherid = E1.voucherid
                     AND A.BRANCHID = E1.BRANCHID)
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
          UNION ALL
          select 'FA' MODULE,
                 A.BRANCHID,
                 NULL AGREEMENTID,
                 NULL ACCOUNTID,
                 A.VOUCHERID,
                 A.VOUCHERDATE,
                 b.groupglid,
                 b.groupgldesc,
                 a.dramt,
                 a.cramt,
                 G.NARRATION
            from FV_FINFA.FA_voucher_dtl a,
                 FV_CASM.fa_groupgl_m    b,
                 FV_FINFA.FA_VOUCHER_HDR G
           where a.groupglid = b.groupglid
             AND A.VOUCHERID = G.VOUCHERID
             AND A.BRANCHID = G.BRANCHID
             and a.status IN ('A', 'P')
             AND A.MODULEID = 'FA'
             AND A.VOUCHERDATE =
                 (SELECT CURRENTDATE FROM FV_FINSEC.NBFC_PARAMETER_M)
             AND G.APPROVEDBY IS NOT NULL) XXX
   WHERE XXX.BRANCHID = 2--ENTER BRANCHID HERE
   ORDER BY XXX.BRANCHID, XXX.MODULE, XXX.VOUCHERDATE, XXX.VOUCHERID
dongwang3066
dongwang3066 您不需要添加生产代码-您需要给我们一个最小的可重现的示例来演示问题(或者一个足够类似的问题,答案可以让您解决类似的问题)。通常,削减您的生产代码以提供匿名/通用的最小可重现性示例将帮助您确定问题,因为您不断删除小部件,直到它没有显示问题,然后您可以找出哪个部分是问题并专注于那;但是当它仍然不起作用时,在这里发布代码将允许我们调试它而不是随机猜测。
一年多之前 回复
dqzg62440
dqzg62440 我担心我无法按照主管的指示添加代码atm,但我希望你知道我在laravel和pl/sql开发人员中使用相同的查询。
一年多之前 回复
doushuangai9733
doushuangai9733 这是我在laravel和pl/sql中执行的相同查询
一年多之前 回复
dqd82461
dqd82461 你的意思是在Laravel中执行相同的实际查询需要更长的时间,或者整个端到端流程需要更长的时间吗?
一年多之前 回复
drh78568
drh78568 请提供一个可重复性最小的示例,其中包含足以复制问题的代码。
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问