Hello Team,
Request your help/analysis to tune the below mentioned query, query is part of a function which is taking a lot of time.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Input parameters : p_foliono, p_applno, v_from_date, v_from_date
SELECT foliono, trxndate, trxntypcod, brokcode, amount, units, schcode ,entrydate
FROM PROCESSED_TRAN
WHERE (foliono is null or foliono = :p_foliono)
AND (applno IS NULL OR applno = :p_applno)
AND 'true' = DECODE (TRUNC (trxndate),TRUNC (entrydate),
CASE WHEN (TRUNC (trxndate) >= TO_DATE (:v_from_date, 'dd/mm/yyyy') AND TRUNC (trxndate) <= TO_DATE (:v_to_date, 'dd/mm/yyyy'))
THEN 'true' ELSE 'false' END,
CASE WHEN (TRUNC (entrydate) >= TO_DATE (:v_from_date, 'dd/mm/yyyy') AND TRUNC (entrydate) <= TO_DATE (:v_to_date, 'dd/mm/yyyy'))
THEN 'true' ELSE 'false' END)
Plan hash value: 3908309003
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5414 | 333K| 26348 (3)| 00:05:17 |
|* 1 | TABLE ACCESS FULL| PROCESSED_TRAN | 5414 | 333K| 26348 (3)| 00:05:17 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("FOLIONO" IS NULL OR "FOLIONO"=:P_FOLIONO) AND ("APPLNO" IS NULL
OR "APPLNO"=:P_APPLNO) AND DECODE(TRUNC(INTERNAL_FUNCTION("TRXNDATE")),TRUNC(INTE
RNAL_FUNCTION("ENTRYDATE")),CASE WHEN (TRUNC(INTERNAL_FUNCTION("TRXNDATE"))>=TO_
DATE(:V_FROM_DATE,'dd/mm/yyyy') AND TRUNC(INTERNAL_FUNCTION("TRXNDATE"))<=TO_DATE
(:V_TO_DATE,'dd/mm/yyyy')) THEN 'true' ELSE 'false' END ,CASE WHEN
(TRUNC(INTERNAL_FUNCTION("ENTRYDATE"))>=TO_DATE(:V_FROM_DATE,'dd/mm/yyyy') AND
TRUNC(INTERNAL_FUNCTION("ENTRYDATE"))<=TO_DATE(:V_TO_DATE,'dd/mm/yyyy')) THEN
'true' ELSE 'false' END )='true')
4 below mentioned Indexes are present on the table:
foliono
applno
trunc(trxndate) -- function based index
trunc(entrydate) -- function based index
Thanks & Regards,
Bhavin.