Hi there is a performance issues, upon analyzing the explain plan, the explain plan is as below.
Anyone please help me how to analyze the explain plan and any indexes will help to have better performance.
Currently using version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Arpp_Decisions – 28M records
Arpp_Issues - 251M Records
Arpp_Exceptions – 28M
Arpp_Balance_PriorDay – 1B records
The tables containing :
Plan hash value: 2652797408
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44808 | 22M| | 284K (1)| 00:00:12 | | |
| 1 | SORT GROUP BY | | 44808 | 22M| | 284K (1)| 00:00:12 | | |
| 2 | NESTED LOOPS SEMI | | 44808 | 22M| | 283K (1)| 00:00:12 | | |
| 3 | VIEW | VW_GBF_17 | 44808 | 21M| | 258K (1)| 00:00:11 | | |
| 4 | HASH GROUP BY | | 44808 | 23M| 26M| 258K (1)| 00:00:11 | | |
|* 5 | FILTER | | | | | | | | |
| 6 | NESTED LOOPS OUTER | | 44808 | 23M| | 258K (1)| 00:00:11 | | |
| 7 | NESTED LOOPS | | 44808 | 14M| | 252K (1)| 00:00:10 | | |
| 8 | NESTED LOOPS | | 44808 | 8095K| | 117K (1)| 00:00:05 | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 13905 | 203K| | 8815 (1)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | IND_SPONSOR_BANK_ID | 13905 | | | 49 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID BATCHED| ARPP_DECISIONS | 3 | 510 | | 175 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | IDX_ARPPDECSIONS_COMPID | 658 | | | 5 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE ITERATOR | | 1 | 150 | | 3 (0)| 00:00:01 | KEY | KEY |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID | BALANCE_PRIORDAY | 1 | 150 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 15 | INDEX UNIQUE SCAN | PK_BALPRDAY_ID | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 16 | TABLE ACCESS BY INDEX ROWID | ARPP_ISSUES | 1 | 215 | | 3 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | PK_ARPPISSUES_ID | 1 | | | 2 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | ARPP_EXCEPTIONS | 28M| 983M| | 3 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | IDX_ARPP_EXC_DECID | 1 | | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(TO_DATE('01-JUN-23','DD-MON-YY hh24:mi:ss')<=TO_DATE('01-JUN-23 23:59:59','DD-MON-YY hh24:mi:ss'))
10 - access("B"."SPONSOR_BANK_ID"='100099')
11 - filter("A"."DECISION_DATE">=TO_DATE('01-JUN-23','DD-MON-YY hh24:mi:ss') AND "A"."DECISION_DATE"<=TO_DATE('01-JUN-23
23:59:59','DD-MON-YY hh24:mi:ss'))
12 - access("B"."BE_CLIENT_ID"="A"."COMPANY_ID")
15 - access("BP"."ID"="A"."BAI_ID" AND "BP"."AS_OF_DATE"="A"."BAI_AS_OF_DATE")
17 - access("A"."ISSUE_ID"="I"."ISSUE_ID"(+))
18 - filter("AE"."EXCEPTION_REASON"=0 OR "AE"."EXCEPTION_REASON"=1 OR "AE"."EXCEPTION_REASON"=2 OR "AE"."EXCEPTION_REASON"=4 OR
"AE"."EXCEPTION_REASON"=6 OR "AE"."EXCEPTION_REASON"=7 OR "AE"."EXCEPTION_REASON"=8 OR "AE"."EXCEPTION_REASON"=9 OR
"AE"."EXCEPTION_REASON"=10 OR "AE"."EXCEPTION_REASON"=13)
19 - access("ITEM_1"="AE"."DECISION_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=8)