Tuning sql
Dear Experts,
Our db version 12.1.0.2
Need your help if we can tune this sql
tkprof
SQL ID: 99csrrfq0hrpf Plan Hash: 2591147470
SELECT SUM(L_COUNT)
FROM
( SELECT COUNT(*) L_COUNT FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID
= :B2 AND NVL(MATCH_STATUS_FLAG, 'Z') = :B1 UNION SELECT COUNT(*) L_COUNT
FROM AP_SELF_ASSESSED_TAX_DIST_ALL WHERE INVOICE_ID = :B2 AND
NVL(MATCH_STATUS_FLAG, 'Z') = :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 461252 28.37 28.47 0 0 0 0
Fetch 461252 5.07 4.95 0 1855396 0 461252
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 922504 33.44 33.43 0 1855396 0 461252
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
cursor: pin S 8 0.00 0.00
********************************************************************************
Ran sql tuning advisor
DBMS_SQLTUNE.REPORT_TUNING_TASK('99CSRRFQ0HRPF_TTDINVOICE1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 99csrrfq0hrpf_ttdinvoice1
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 05/23/2022 11:19:42
Completed at : 05/23/2022 11:19:44
-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID : 99csrrfq0hrpf
SQL Text : SELECT SUM(L_COUNT) FROM ( 0