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
