Hi All,
Using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
We have a view that it is been use a lot and it takes over a minute, need some performance improvement.
I cut the view in pieces to show you the explain plan
select r.req_id, r.ras_id, r.ras_type, r.ras_status, up.unit_program_id, up.unit_program_name,
case when r.ras_type = 1 then 'RAS'
when r.ras_type = 2 then 'SYSCOM'
when r.ras_type = 3 then 'POM Adjustment'
else '' end ras_type_name,
case when r.ras_status = 0 then 'NEW'
when r.ras_status = 1 then 'DISAPPROVED'
when r.ras_status = 2 then 'APPROVED'
when r.ras_status = 3 then 'PARTIAL APPROVED'
when r.ras_status = 4 then 'NON-VALIDATED'
else '' end ras_status_name,
(CASE WHEN r.ppbe_id_creation is not null THEN (SELECT p.ppbe_id FROM ppbe p WHERE p.ppbe_id = r.ppbe_id_creation) ELSE NULL END) AS ppbe_id_creation,
to_char(r.ppbe_id_expiration,'MM/DD/YYYY') as ppbe_id_expiration, re.ppbe_id,
r.issue_number, r.title, r.bso_id, rs.resource_sponsor_id, rs.resource_sponsor_name, r.pe, r.li_id, r.ACCT_L, r.si_id, s.si_name, r.civper, r.cte, r.cto, r.ctt,
CASE WHEN r.execute_flag = 0 THEN 'NO' ELSE 'YES' END AS execflag, r.execute_flag, r.notes, r.three_year_flag,
r.UNIT_TYPE_ID, ut.unit_type_name, r.DESIGNATION, r.OCO, r.ACT_RES, r.PERF_L, r.PILLAR, r.APPN,
case when r.ppbe_id_expiration iS NULL OR r.ppbe_id_expiration >= to_date('10/01/'||(re.fydp_startyear -1),'MM/DD/YYYY') then 'ACTIVE' else 'EXPIRED' end ras_expired,
min(p.budget_year) AS fydp_startyear, re.fydp_endyear as fydp_endyear, re.fydp_startyear + 2 as currentPOM
FROM ras r
JOIN ras_fy rf on rf.ras_id = r.ras_id
JOIN requirement re on re.req_id = r.req_id
JOIN unit_type ut on ut.unit_type_id = r.unit_type_id
JOIN unit_program up ON ut.unit_program_id = up.unit_program_id
JOIN bso b on r.bso_id = b.bso_id
JOIN vw_get_rs rs on rs.pe = r.pe and ( ( (re.fydp_startyear + 2) < 2023 and rs_table = 'OLD')
or ( (re.fydp_startyear + 2) >= 2023 and rs_table = 'NEW' and rs.unit_program_id = r.unit_program_id and rs.ppbe_id = re.ppbe_id and rs.appn = r.appn and rs.bso_id = r.bso_id and rs.li_id = r.li_id and rs.si_id = r.si_id and rs.civper = r.civper ))
JOIN si s on r.si_id = s.si_id
JOIN ppbe p on r.ppbe_id_creation = p.ppbe_id
GROUP BY r.req_id, r.ras_id, r.ras_type, r.ras_status, up.unit_program_id, up.unit_program_name, r.ppbe_id_creation, r.ppbe_id_expiration, re.ppbe_id, r.issue_number, r.title, r.bso_id,
rs.resource_sponsor_id, rs.resource_sponsor_name, r.pe, r.li_id, r.ACCT_L, r.si_id, s.si_name, r.civper, r.cte, r.cto, r.ctt, r.execute_flag, r.notes, r.three_year_flag,
r.UNIT_TYPE_ID, ut.unit_type_name, r.DESIGNATION, r.OCO, r.ACT_RES, r.PERF_L, r.PILLAR, r.APPN, r.ACCT_L, re.ppbe_id, re.fydp_startyear, re.fydp_endyear
The explain plan
Plan Hash Value :
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61523 | 21840665 | 6831 | 00:00:01 |
| * 1 | INDEX UNIQUE SCAN | PK_PPBE | 1 | 7 | 0 | 00:00:01 |
| 2 | HASH GROUP BY | | 61523 | 21840665 | 6831 | 00:00:01 |
| * 3 | HASH JOIN | | 61523 | 21840665 | 2162 | 00:00:01 |
| 4 | VIEW | index$_join$_075 | 61 | 732 | 2 | 00:00:01 |
| * 5 | HASH JOIN | | | | | |
| 6 | INDEX FAST FULL SCAN | PK_UNIT_PROGRAM_ID | 61 | 732 | 1 | 00:00:01 |
| 7 | INDEX FAST FULL SCAN | UNK_UNIT_PROGRAM_NAME | 61 | 732 | 1 | 00:00:01 |
| * 8 | HASH JOIN | | 61523 | 21102389 | 2159 | 00:00:01 |
| 9 | TABLE ACCESS FULL | PPBE | 16 | 176 | 3 | 00:00:01 |
| 10 | VIEW | VW_JF_SET$B923F877 | 61523 | 20425636 | 2156 | 00:00:01 |
| 11 | UNION-ALL | | | | | |
| 12 | NESTED LOOPS | | 1762 | 657226 | 1040 | 00:00:01 |
| * 13 | HASH JOIN | | 240 | 88320 | 560 | 00:00:01 |
| 14 | TABLE ACCESS FULL | UNIT_TYPE | 314 | 6594 | 4 | 00:00:01 |
| * 15 | HASH JOIN | | 240 | 83280 | 556 | 00:00:01 |
| 16 | VIEW | index$_join$_082 | 66 | 462 | 2 | 00:00:01 |
| * 17 | HASH JOIN | | | | | |
| 18 | INDEX FAST FULL SCAN | PK_SI | 66 | 462 | 1 | 00:00:01 |
| 19 | INDEX FAST FULL SCAN | UNQ_SI | 66 | 462 | 1 | 00:00:01 |
| * 20 | HASH JOIN | | 240 | 81600 | 553 | 00:00:01 |
| 21 | NESTED LOOPS | | 7960 | 1974080 | 450 | 00:00:01 |
| 22 | NESTED LOOPS | | 7960 | 1974080 | 450 | 00:00:01 |
| * 23 | TABLE ACCESS FULL | REQUIREMENT | 11 | 209 | 4 | 00:00:01 |
| * 24 | INDEX RANGE SCAN | FK_RAS_REQ | 714 | | 1 | 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | RAS | 714 | 163506 | 45 | 00:00:01 |
| 26 | VIEW | VW_GET_RS | 51041 | 4695772 | 103 | 00:00:01 |
| 27 | UNION-ALL | | | | | |
| * 28 | HASH JOIN | | 51040 | 2552000 | 103 | 00:00:01 |
| 29 | VIEW | index$_join$_080 | 7 | 56 | 2 | 00:00:01 |
| * 30 | HASH JOIN | | | | | |
| 31 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 32 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 33 | INDEX FAST FULL SCAN | UK_RSPE | 51040 | 2143680 | 101 | 00:00:01 |
| * 34 | FILTER | | | | | |
| * 35 | HASH JOIN | | 49 | 980 | 4 | 00:00:01 |
| 36 | VIEW | index$_join$_078 | 7 | 56 | 2 | 00:00:01 |
| * 37 | HASH JOIN | | | | | |
| 38 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 39 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 40 | VIEW | index$_join$_079 | 49 | 588 | 2 | 00:00:01 |
| * 41 | HASH JOIN | | | | | |
| 42 | INDEX FAST FULL SCAN | IDX_PE_RS | 49 | 588 | 1 | 00:00:01 |
| 43 | INDEX FAST FULL SCAN | PK_PROGRAM_ELEMENT | 49 | 588 | 1 | 00:00:01 |
| * 44 | INDEX RANGE SCAN | FK_RASID | 7 | 35 | 2 | 00:00:01 |
| * 45 | HASH JOIN | | 59761 | 17928300 | 1116 | 00:00:01 |
| 46 | VIEW | VW_GET_RS | 50 | 1150 | 4 | 00:00:01 |
| 47 | UNION-ALL | | | | | |
| * 48 | FILTER | | | | | |
| * 49 | HASH JOIN | | 51040 | 1020800 | 103 | 00:00:01 |
| 50 | VIEW | index$_join$_067 | 7 | 56 | 2 | 00:00:01 |
| * 51 | HASH JOIN | | | | | |
| 52 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 53 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 54 | INDEX FAST FULL SCAN | UK_RSPE | 51040 | 612480 | 101 | 00:00:01 |
| * 55 | HASH JOIN | | 49 | 980 | 4 | 00:00:01 |
| 56 | VIEW | index$_join$_065 | 7 | 56 | 2 | 00:00:01 |
| * 57 | HASH JOIN | | | | | |
| 58 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 59 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 60 | VIEW | index$_join$_066 | 49 | 588 | 2 | 00:00:01 |
| * 61 | HASH JOIN | | | | | |
| 62 | INDEX FAST FULL SCAN | IDX_PE_RS | 49 | 588 | 1 | 00:00:01 |
| 63 | INDEX FAST FULL SCAN | PK_PROGRAM_ELEMENT | 49 | 588 | 1 | 00:00:01 |
| * 64 | HASH JOIN | | 58565 | 16222505 | 1111 | 00:00:01 |
| 65 | TABLE ACCESS FULL | UNIT_TYPE | 314 | 6594 | 4 | 00:00:01 |
| * 66 | HASH JOIN | | 58565 | 14992640 | 1107 | 00:00:01 |
| * 67 | HASH JOIN | | 7960 | 1997960 | 452 | 00:00:01 |
| 68 | VIEW | index$_join$_069 | 66 | 462 | 2 | 00:00:01 |
| * 69 | HASH JOIN | | | | | |
| 70 | INDEX FAST FULL SCAN | PK_SI | 66 | 462 | 1 | 00:00:01 |
| 71 | INDEX FAST FULL SCAN | UNQ_SI | 66 | 462 | 1 | 00:00:01 |
| 72 | NESTED LOOPS | | 7960 | 1942240 | 450 | 00:00:01 |
| 73 | NESTED LOOPS | | 7960 | 1942240 | 450 | 00:00:01 |
| * 74 | TABLE ACCESS FULL | REQUIREMENT | 11 | 209 | 4 | 00:00:01 |
| * 75 | INDEX RANGE SCAN | FK_RAS_REQ | 714 | | 1 | 00:00:01 |
| 76 | TABLE ACCESS BY INDEX ROWID | RAS | 714 | 160650 | 45 | 00:00:01 |
| 77 | INDEX FAST FULL SCAN | FK_RASID | 1044306 | 5221530 | 647 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("P"."PPBE_ID"=:B1)
* 3 - access("ITEM_1"="UP"."UNIT_PROGRAM_ID")
* 5 - access(ROWID=ROWID)
* 8 - access("ITEM_2"="P"."PPBE_ID")
* 13 - access("UT"."UNIT_TYPE_ID"="R"."UNIT_TYPE_ID")
* 15 - access("R"."SI_ID"="S"."SI_ID")
* 17 - access(ROWID=ROWID)
* 20 - access("RS"."CIVPER"="R"."CIVPER" AND "RS"."SI_ID"="R"."SI_ID" AND "RS"."LI_ID"="R"."LI_ID" AND "RS"."BSO_ID"="R"."BSO_ID" AND "RS"."APPN"="R"."APPN" AND "RS"."PPBE_ID"="RE"."PPBE_ID" AND
"RS"."UNIT_PROGRAM_ID"="R"."UNIT_PROGRAM_ID" AND "RS"."PE"="R"."PE")
* 20 - filter(LNNVL("RE"."FYDP_STARTYEAR"+2<2023) OR LNNVL("RS_TABLE"='OLD'))
* 23 - filter("RE"."FYDP_STARTYEAR"+2>=2023)
* 24 - access("RE"."REQ_ID"="R"."REQ_ID")
* 28 - access("RS"."RESOURCE_SPONSOR_ID"="G"."RESOURCE_SPONSOR_ID")
* 30 - access(ROWID=ROWID)
* 34 - filter(NULL IS NOT NULL)
* 35 - access("R"."RESOURCE_SPONSOR_ID"="PE"."RESOURCE_SPONSOR_ID")
* 37 - access(ROWID=ROWID)
* 41 - access(ROWID=ROWID)
* 44 - access("RF"."RAS_ID"="R"."RAS_ID")
* 45 - access("RS"."PE"="R"."PE")
* 48 - filter(NULL IS NOT NULL)
* 49 - access("RS"."RESOURCE_SPONSOR_ID"="G"."RESOURCE_SPONSOR_ID")
* 51 - access(ROWID=ROWID)
* 55 - access("R"."RESOURCE_SPONSOR_ID"="PE"."RESOURCE_SPONSOR_ID")
* 57 - access(ROWID=ROWID)
* 61 - access(ROWID=ROWID)
* 64 - access("UT"."UNIT_TYPE_ID"="R"."UNIT_TYPE_ID")
* 66 - access("RF"."RAS_ID"="R"."RAS_ID")
* 67 - access("R"."SI_ID"="S"."SI_ID")
* 69 - access(ROWID=ROWID)
* 74 - filter("RE"."FYDP_STARTYEAR"+2<2023)
* 75 - access("RE"."REQ_ID"="R"."REQ_ID")
Inside, there is another view vw_get_rs
select 'NEW' as rs_table, g.resource_sponsor_id, resource_sponsor_name, pe, g.unit_program_id, unit_program_name, g.ppbe_id, appn, bso_id, li_id, s.si_id, si_name, civper
from rs_mappings g
join unit_program p on p.unit_program_id = g.unit_program_id
join resource_sponsor rs on rs.resource_sponsor_id = g.resource_sponsor_id
join si s on s.si_id = g.si_id
union all
select 'OLD' as rs_table, pe.resource_sponsor_id, resource_sponsor_name, pe, null, null, null, null, null, null, null, null, null
from program_element pe
join resource_sponsor r on r.resource_sponsor_id = pe.resource_sponsor_id
Explain plan
Plan Hash Value : 2214697675
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51089 | 3522740 | 112 | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| * 2 | HASH JOIN | | 51040 | 3521760 | 108 | 00:00:01 |
| 3 | VIEW | index$_join$_006 | 66 | 462 | 2 | 00:00:01 |
| * 4 | HASH JOIN | | | | | |
| 5 | INDEX FAST FULL SCAN | PK_SI | 66 | 462 | 1 | 00:00:01 |
| 6 | INDEX FAST FULL SCAN | UNQ_SI | 66 | 462 | 1 | 00:00:01 |
| * 7 | HASH JOIN | | 51040 | 3164480 | 106 | 00:00:01 |
| 8 | VIEW | index$_join$_002 | 61 | 732 | 2 | 00:00:01 |
| * 9 | HASH JOIN | | | | | |
| 10 | INDEX FAST FULL SCAN | PK_UNIT_PROGRAM_ID | 61 | 732 | 1 | 00:00:01 |
| 11 | INDEX FAST FULL SCAN | UNK_UNIT_PROGRAM_NAME | 61 | 732 | 1 | 00:00:01 |
| * 12 | HASH JOIN | | 51040 | 2552000 | 103 | 00:00:01 |
| 13 | VIEW | index$_join$_004 | 7 | 56 | 2 | 00:00:01 |
| * 14 | HASH JOIN | | | | | |
| 15 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 16 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 17 | INDEX FAST FULL SCAN | UK_RSPE | 51040 | 2143680 | 101 | 00:00:01 |
| * 18 | HASH JOIN | | 49 | 980 | 4 | 00:00:01 |
| 19 | VIEW | index$_join$_009 | 7 | 56 | 2 | 00:00:01 |
| * 20 | HASH JOIN | | | | | |
| 21 | INDEX FAST FULL SCAN | PK_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 22 | INDEX FAST FULL SCAN | UNQ_RESOURCE_SPONSOR | 7 | 56 | 1 | 00:00:01 |
| 23 | VIEW | index$_join$_008 | 49 | 588 | 2 | 00:00:01 |
| * 24 | HASH JOIN | | | | | |
| 25 | INDEX FAST FULL SCAN | IDX_PE_RS | 49 | 588 | 1 | 00:00:01 |
| 26 | INDEX FAST FULL SCAN | PK_PROGRAM_ELEMENT | 49 | 588 | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("S"."SI_ID"="G"."SI_ID")
* 4 - access(ROWID=ROWID)
* 7 - access("P"."UNIT_PROGRAM_ID"="G"."UNIT_PROGRAM_ID")
* 9 - access(ROWID=ROWID)
* 12 - access("RS"."RESOURCE_SPONSOR_ID"="G"."RESOURCE_SPONSOR_ID")
* 14 - access(ROWID=ROWID)
* 18 - access("R"."RESOURCE_SPONSOR_ID"="PE"."RESOURCE_SPONSOR_ID")
* 20 - access(ROWID=ROWID)
* 24 - access(ROWID=ROWID)
The RAS table indexes
PK_RAS UNIQUE VALID NORMAL N NO NO RAS_ID
FK_RAS_LI NONUNIQUE VALID NORMAL N NO NO LI_ID
FK_RAS_SI NONUNIQUE VALID NORMAL N NO NO SI_ID
FK_RAS_BSO NONUNIQUE VALID NORMAL N NO NO BSO_ID
FK_RAS_REQ NONUNIQUE VALID NORMAL N NO NO REQ_ID
FK_RAS_PPBE NONUNIQUE VALID NORMAL N NO NO PPBE_ID_CREATION
IDX_RAS_REQ NONUNIQUE VALID NORMAL N NO NO REQ_ID, UNIT_PROGRAM_ID, PPBE_ID_CREATION, SI_ID, PE, BSO_ID, LI_ID, ACCT_L, RAS_ID
FK_RAS_USERS NONUNIQUE VALID NORMAL N NO NO MODIFIED_BY
FK_RAS_UNITTYPE NONUNIQUE VALID NORMAL N NO NO UNIT_TYPE_ID
IDX_REQ_ID_RAS_ID NONUNIQUE VALID NORMAL N NO NO REQ_ID, RAS_ID
FK_RAS_COSTACCOUNT NONUNIQUE VALID NORMAL N NO NO ACCT_L
FK_RAS_UNITPROGRAM NONUNIQUE VALID NORMAL N NO NO UNIT_PROGRAM_ID
FK_RAS_PROGRAMELEMENT NONUNIQUE VALID NORMAL N NO NO PE
RAS Table Index Statistics
NAME BLEVEL LEAF_BLKS DST_KEYS NUM_ROWS CLUST_FACT LEAF_PER_KEY DATA_PER_KEY
FK_RAS_UNITTYPE 1 283 105 142778 32015 2 304
FK_RAS_UNITPROGRAM 1 282 42 142778 18383 6 437
FK_RAS_LI 1 318 9 142778 5561 35 617
FK_RAS_PROGRAMELEMENT 2 398 35 142778 23399 11 668
FK_RAS_SI 1 280 22 142778 16646 12 756
FK_RAS_BSO 1 279 8 142778 16849 34 2106
FK_RAS_USERS 2 416 28 142778 8971 14 320
IDX_RAS_REQ 2 1089 142778 142778 34020 1 1
IDX_REQ_ID_RAS_ID 1 397 142778 142778 10826 1 1
FK_RAS_COSTACCOUNT 1 307 5 142778 12606 61 2521
FK_RAS_PPBE 1 358 16 142778 16706 22 1044
FK_RAS_REQ 1 299 200 142778 8637 1 43
PK_RAS 1 298 142778 142778 11268 1 1
How can I analyze my tables to check for a possible missing or extra index?
Best,
Johnny