Hello,
In Oracle 19c I have the below query which executes immediate , but the moment i add the join to TPC_SEC_ALLOCATION_V and it stops working ( I have to cancel the long running request). I've tried to add that view as a WITH , inline view directly in the select or join to the main but with no luck. Any hints what can I improve, please? The view itself return data in few seconds.
Here is the query :
--explain plan for
with
secondary_runs as (select * from vbcs_app.TPC_SECONDARY_RUNS)
,fv as ( select * from tpc_fnd_lookup_values)
,custom_form as
(select
sum(case when EXTN_ATTRIBUTE_CHAR007 = 'COST_NOT_ALLOC' then EXTN_ATTRIBUTE_NUMBER006 else 0 end ) as cost_not_allocated_tp
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'COST_OTHER_CL' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as cost_related_to_lc
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'DEEM_REV_OP_FEE' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as deemed_rev_op_fee
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'OTHER_REV' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as other_rev_no_tp
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'RO_OTHER_REIMB_UK' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as ro_other_reimb_uk
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'RO_OTHER_REIMB_PLC' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as ro_other_reimb_plc
,sum(case when EXTN_ATTRIBUTE_CHAR007 = 'RO_OTHER_REIMB_PLC_SOFT' then EXTN_ATTRIBUTE_NUMBER006 else 0 end) as ro_other_reimb_plc_soft
,legal_entity_cf
,cf.oic_run_id
from vbcs_app.TPC_CUSTOM_FORM cf
,secondary_runs sr
where 1=1
and cf.oic_run_id = sr.oic_run_id
and to_date('01-'|| EXTN_ATTRIBUTE_CHAR009 || '-' || EXTN_ATTRIBUTE_CHAR010 , 'DD-MM-YYYY') between sr.from_date and sr.to_date
group by legal_entity_cf, cf.oic_run_id
)
,allocation as (select /*+ MATERIALIZE */
sum(case when cost_category = 'Sales' then allocation_of_overhead else 0 end) allocation_of_overhead
,oic_run_id
from TPC_SEC_ALLOCATION_V
group by oic_run_id)
,operating_fee_main as (
select
pla.oic_run_id
,pla.ledger_name
,pla.legal_entity_name
,pla.legal_entity
,pla.period_name
,sum(case when
(select 1
from fv
where fv.oic_run_id = pla.oic_run_id
and substr(pla.natural_account, 1, length(fv.tag)) = fv.tag
and fv.lookup_type = 'ENDAVA_TPC_19'
and pla.legal_entity = fv.description
and rownum = 1) = 1 and
pla.spare_1 in ( select tag
from fv
where fv.lookup_type = 'ENDAVA_TPC_20'
)
then
gl_amount
else 0 end) as external_sales_op_fee
,cf.deemed_rev_op_fee
,case when pla.legal_entity_name = 'Endava Inc' then
(select lookup_code
from fv
where fv.lookup_type = 'ENDAVA_TPC_8'
and fv.tag = 'Endava Inc'
and rownum = 1)
else
(select lookup_code
from fv
where fv.lookup_type = 'ENDAVA_TPC_8'
and fv.tag = 'Other'
and rownum = 1
)
end as target_operating_margin_perc
from
tpc_sec_p_l_analysis pla
,custom_form cf
where pla.oic_run_id = 29601962
and pla.oic_run_id = cf.oic_run_id(+)
group by
pla.oic_run_id
,pla.ledger_name
,pla.legal_entity_name
,pla.legal_entity
,pla.period_name
,cf.deemed_rev_op_fee
)
select
main.oic_run_id
,main.ledger_name
,main.legal_entity_name
,main.legal_entity
,main.period_name
,main.external_sales_op_fee
,main.deemed_rev_op_fee
,main.target_operating_margin_perc
,main.target_operating_margin_perc * (external_sales_op_fee + deemed_rev_op_fee ) as target_operating_margin
,(external_sales_op_fee + deemed_rev_op_fee ) as external_sales
,(select sum(case when bu_classification = 'Sales' then total_payroll_cost else 0 end) from tpc_sec_payroll_costs where oic_run_id = main.oic_run_id ) as External_Costs_Sales
,a.allocation_of_overhead as allocation_ext_ind_costs
from operating_fee_main main
,allocation a
where a.oic_run_id = main.oic_run_id
;
Here is the explain plan without the allocation_of_overhead column :
Plan hash value: 2143588120
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 137 | 848 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
|* 2 | TABLE ACCESS STORAGE FULL FIRST ROWS| TPC_FND_LOOKUP_VALUES | 1 | 34 | 5 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_FND_LOOKUP_VALUES | 1 | 23 | 5 (0)| 00:00:01 | | |
|* 4 | COUNT STOPKEY | | | | | | | |
|* 5 | TABLE ACCESS STORAGE FULL FIRST ROWS| TPC_FND_LOOKUP_VALUES | 1 | 27 | 5 (0)| 00:00:01 | | |
|* 6 | COUNT STOPKEY | | | | | | | |
|* 7 | TABLE ACCESS STORAGE FULL FIRST ROWS| TPC_FND_LOOKUP_VALUES | 1 | 27 | 5 (0)| 00:00:01 | | |
| 8 | HASH GROUP BY | | 1 | 137 | 848 (1)| 00:00:01 | | |
|* 9 | HASH JOIN RIGHT OUTER | | 1220 | 163K| 71 (2)| 00:00:01 | | |
| 10 | VIEW | | 1 | 19 | 5 (20)| 00:00:01 | | |
| 11 | HASH GROUP BY | | 1 | 57 | 5 (20)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 57 | 4 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TPC_SECONDARY_RUNS | 1 | 22 | 1 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS STORAGE FULL | TPC_CUSTOM_FORM | 1 | 35 | 3 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 1220 | 140K| 66 (0)| 00:00:01 | | |
| 17 | VIEW | VW_SSQ_1 | 1 | 19 | 6 (0)| 00:00:01 | | |
| 18 | HASH GROUP BY | | 1 | 30 | 6 (0)| 00:00:01 | | |
| 19 | PARTITION HASH SINGLE | | 48 | 1440 | 6 (0)| 00:00:01 | 4 | 4 |
|* 20 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 48 | 1440 | 6 (0)| 00:00:01 | 4 | 4 |
| 21 | PARTITION HASH SINGLE | | 1220 | 117K| 60 (0)| 00:00:01 | 4 | 4 |
|* 22 | TABLE ACCESS STORAGE FULL | TPC_SEC_P_L_ANALYSIS | 1220 | 117K| 60 (0)| 00:00:01 | 4 | 4 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - storage("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_19')
filter("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_19' AND "TPC_FND_LOOKUP_VALUES"."DESCRIPTION"=:B1 AND
"TPC_FND_LOOKUP_VALUES"."TAG"=SUBSTR(:B2,1,LENGTH("TPC_FND_LOOKUP_VALUES"."TAG")) AND
"TPC_FND_LOOKUP_VALUES"."OIC_RUN_ID"=:B3)
3 - filter("TPC_FND_LOOKUP_VALUES"."TAG"=:B1 AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_20')
4 - filter(ROWNUM=1)
5 - storage("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8' AND "TPC_FND_LOOKUP_VALUES"."TAG"='Endava Inc')
filter("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8' AND "TPC_FND_LOOKUP_VALUES"."TAG"='Endava Inc')
6 - filter(ROWNUM=1)
7 - storage("TPC_FND_LOOKUP_VALUES"."TAG"='Other' AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8')
filter("TPC_FND_LOOKUP_VALUES"."TAG"='Other' AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8')
9 - access("PLA"."OIC_RUN_ID"="CF"."OIC_RUN_ID"(+))
14 - access("TPC_SECONDARY_RUNS"."OIC_RUN_ID"=29601962)
15 - storage("CF"."OIC_RUN_ID"=29601962 AND "TPC_SECONDARY_RUNS"."FROM_DATE"<=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"
||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY') AND "TPC_SECONDARY_RUNS"."TO_DATE">=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR00
9"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY'))
filter("CF"."OIC_RUN_ID"=29601962 AND "TPC_SECONDARY_RUNS"."FROM_DATE"<=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"|
|'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY') AND "TPC_SECONDARY_RUNS"."TO_DATE">=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009
"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY'))
16 - access("ITEM_1"(+)="PLA"."OIC_RUN_ID")
20 - storage("OIC_RUN_ID"=29601962)
filter("OIC_RUN_ID"=29601962)
22 - storage("PLA"."OIC_RUN_ID"=29601962)
filter("PLA"."OIC_RUN_ID"=29601962)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
…and the explain plan with that column in question:
Plan hash value: 3071749789
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 265 | | 1886M (1)| 20:28:14 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
|* 2 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_FND_LOOKUP_VALUES | 1 | 34 | | 5 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_FND_LOOKUP_VALUES | 1 | 23 | | 5 (0)| 00:00:01 | | |
|* 4 | COUNT STOPKEY | | | | | | | | |
|* 5 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_FND_LOOKUP_VALUES | 1 | 27 | | 5 (0)| 00:00:01 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
|* 7 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_FND_LOOKUP_VALUES | 1 | 27 | | 5 (0)| 00:00:01 | | |
| 8 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 9 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_BFD9ECD84_FF0EB19B | | | | | | | |
|* 10 | VIEW | | 1 | 154 | | 2 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS STORAGE FULL | SYS_TEMP_BFD9ECD85_FF0EB19B | 1 | 113 | | 2 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 199 | | 3 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 199 | | 3 (0)| 00:00:01 | | |
|* 14 | VIEW | | 1 | 185 | | 2 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS STORAGE FULL | SYS_TEMP_BFD9ECD85_FF0EB19B | 1 | 113 | | 2 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | TPC_SECONDARY_RUNS | 1 | 14 | | 1 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS STORAGE FULL FIRST ROWS | TPC_LEGAL_ENTITIES | 1 | 30 | | 3 (0)| 00:00:01 | | |
| 19 | NESTED LOOPS | | 1 | 321 | | 3 (0)| 00:00:01 | | |
| 20 | NESTED LOOPS | | 1 | 321 | | 3 (0)| 00:00:01 | | |
|* 21 | VIEW | | 1 | 307 | | 2 (0)| 00:00:01 | | |
| 22 | TABLE ACCESS STORAGE FULL | SYS_TEMP_BFD9ECD85_FF0EB19B | 1 | 113 | | 2 (0)| 00:00:01 | | |
|* 23 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | TPC_SECONDARY_RUNS | 1 | 14 | | 1 (0)| 00:00:01 | | |
|* 25 | VIEW | | 1 | 154 | | 2 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS STORAGE FULL | SYS_TEMP_BFD9ECD85_FF0EB19B | 1 | 113 | | 2 (0)| 00:00:01 | | |
| 27 | SORT AGGREGATE | | 1 | 41 | | | | | |
| 28 | PARTITION HASH SINGLE | | 96 | 3936 | | 6 (0)| 00:00:01 | KEY | KEY |
|* 29 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 96 | 3936 | | 6 (0)| 00:00:01 | KEY | KEY |
| 30 | SORT AGGREGATE | | 1 | 30 | | | | | |
| 31 | PARTITION HASH SINGLE | | 1 | 30 | | 6 (0)| 00:00:01 | 3 | 3 |
|* 32 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 1 | 30 | | 6 (0)| 00:00:01 | 3 | 3 |
| 33 | SORT GROUP BY | | 1 | 39 | | 7 (15)| 00:00:01 | | |
| 34 | PARTITION HASH SINGLE | | 96 | 3744 | | 6 (0)| 00:00:01 | KEY | KEY |
|* 35 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 96 | 3744 | | 6 (0)| 00:00:01 | KEY | KEY |
| 36 | HASH GROUP BY | | 1 | 28 | | 1886M (1)| 20:28:14 | | |
| 37 | VIEW | | 2 | 56 | | 1886M (1)| 20:28:14 | | |
| 38 | WINDOW BUFFER | | 2 | 392 | | 1886M (1)| 20:28:14 | | |
| 39 | HASH GROUP BY | | 2 | 392 | | 1886M (1)| 20:28:14 | | |
| 40 | VIEW | | 2 | 392 | | 1886M (1)| 20:28:14 | | |
| 41 | HASH GROUP BY | | 2 | 388 | | 1886M (1)| 20:28:14 | | |
|* 42 | HASH JOIN | | 30525 | 5783K| | 1886M (1)| 20:28:14 | | |
| 43 | PART JOIN FILTER CREATE | :BF0000 | 255 | 23715 | | 47172 (1)| 00:00:02 | | |
| 44 | VIEW | | 255 | 23715 | | 47172 (1)| 00:00:02 | | |
| 45 | HASH GROUP BY | | 255 | 26520 | | 47172 (1)| 00:00:02 | | |
| 46 | VIEW | | 346K| 34M| | 47163 (1)| 00:00:02 | | |
| 47 | WINDOW BUFFER | | 346K| 100M| | 47163 (1)| 00:00:02 | | |
| 48 | HASH GROUP BY | | 346K| 100M| 128M| 47163 (1)| 00:00:02 | | |
| 49 | VIEW | | 346K| 100M| | 26039 (1)| 00:00:02 | | |
| 50 | HASH GROUP BY | | 346K| 85M| 93M| 26039 (1)| 00:00:02 | | |
|* 51 | HASH JOIN RIGHT OUTER | | 346K| 85M| | 7966 (1)| 00:00:01 | | |
| 52 | PARTITION HASH ALL | | 1536 | 61440 | | 30 (0)| 00:00:01 | 1 | 8 |
| 53 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 1536 | 61440 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 54 | HASH JOIN | | 36114 | 7758K| | 7935 (1)| 00:00:01 | | |
| 55 | VIEW | | 1 | 29 | | 31 (4)| 00:00:01 | | |
| 56 | HASH GROUP BY | | 1 | 41 | | 31 (4)| 00:00:01 | | |
| 57 | PARTITION HASH ALL | | 1408 | 57728 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 58 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 1408 | 57728 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 59 | HASH JOIN | | 36114 | 6736K| | 7904 (1)| 00:00:01 | | |
| 60 | PART JOIN FILTER CREATE | :BF0001 | 19 | 114 | | 1 (0)| 00:00:01 | | |
| 61 | INDEX FULL SCAN | TPC_SECONDARY_RUNS_PK | 19 | 114 | | 1 (0)| 00:00:01 | | |
| 62 | VIEW | | 216K| 38M| | 7903 (1)| 00:00:01 | | |
| 63 | WINDOW SORT | | 216K| 29M| 39M| 7903 (1)| 00:00:01 | | |
| 64 | PARTITION HASH JOIN-FILTER | | 216K| 29M| | 1561 (1)| 00:00:01 |:BF0001|:BF0001|
| 65 | TABLE ACCESS STORAGE FULL | TPC_SEC_TIMECARDS_MAIN | 216K| 29M| | 1561 (1)| 00:00:01 |:BF0001|:BF0001|
| 66 | NESTED LOOPS | | 2155 | 212K| | 1886M (1)| 20:28:12 | | |
| 67 | PARTITION HASH JOIN-FILTER | | 39072 | 2289K| | 470 (1)| 00:00:01 |:BF0000|:BF0000|
| 68 | TABLE ACCESS STORAGE FULL | TPC_SEC_P_L_ANALYSIS | 39072 | 2289K| | 470 (1)| 00:00:01 |:BF0000|:BF0000|
| 69 | VIEW PUSHED PREDICATE | TPC_SECONDARY_OVERHEAD_V | 1 | 41 | | 48283 (1)| 00:00:02 | | |
| 70 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 71 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_BFD9ECD85_FF0EB19B | | | | | | | |
|* 72 | HASH JOIN | | 1 | 113 | | 8 (0)| 00:00:01 | | |
| 73 | TABLE ACCESS STORAGE FULL | TPC_SECONDARY_RUNS | 19 | 266 | | 3 (0)| 00:00:01 | | |
|* 74 | TABLE ACCESS STORAGE FULL | TPC_FND_LOOKUP_VALUES | 106 | 10494 | | 5 (0)| 00:00:01 | | |
| 75 | SORT GROUP BY | | 1 | 262 | | 48275 (1)| 00:00:02 | | |
|* 76 | HASH JOIN RIGHT OUTER | | 2415 | 617K| | 46861 (1)| 00:00:02 | | |
| 77 | VIEW | | 552 | 38088 | | 46795 (1)| 00:00:02 | | |
| 78 | SORT GROUP BY | | 552 | 61824 | | 46795 (1)| 00:00:02 | | |
|* 79 | VIEW | | 346K| 37M| | 46795 (1)| 00:00:02 | | |
| 80 | WINDOW BUFFER | | 346K| 99M| | 46795 (1)| 00:00:02 | | |
| 81 | HASH GROUP BY | | 346K| 99M| 123M| 46795 (1)| 00:00:02 | | |
| 82 | VIEW | | 346K| 99M| | 25916 (1)| 00:00:02 | | |
| 83 | HASH GROUP BY | | 346K| 85M| 90M| 25916 (1)| 00:00:02 | | |
|* 84 | HASH JOIN RIGHT OUTER | | 346K| 85M| | 7966 (1)| 00:00:01 | | |
| 85 | PARTITION HASH ALL | | 1536 | 61440 | | 30 (0)| 00:00:01 | 1 | 8 |
| 86 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 1536 | 61440 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 87 | HASH JOIN | | 36114 | 7688K| | 7935 (1)| 00:00:01 | | |
| 88 | VIEW | | 1 | 29 | | 31 (4)| 00:00:01 | | |
| 89 | SORT GROUP BY | | 1 | 41 | | 31 (4)| 00:00:01 | | |
| 90 | PARTITION HASH ALL | | 1408 | 57728 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 91 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 1408 | 57728 | | 30 (0)| 00:00:01 | 1 | 8 |
|* 92 | HASH JOIN | | 36114 | 6665K| | 7904 (1)| 00:00:01 | | |
| 93 | INDEX FULL SCAN | TPC_SECONDARY_RUNS_PK | 19 | 114 | | 1 (0)| 00:00:01 | | |
| 94 | VIEW | | 216K| 37M| | 7903 (1)| 00:00:01 | | |
| 95 | WINDOW SORT | | 216K| 29M| 39M| 7903 (1)| 00:00:01 | | |
| 96 | PARTITION HASH ALL | | 216K| 29M| | 1561 (1)| 00:00:01 | 1 | 8 |
| 97 | TABLE ACCESS STORAGE FULL| TPC_SEC_TIMECARDS_MAIN | 216K| 29M| | 1561 (1)| 00:00:01 | 1 | 8 |
|* 98 | HASH JOIN RIGHT OUTER | | 2415 | 455K| | 65 (2)| 00:00:01 | | |
| 99 | VIEW | | 1 | 99 | | 5 (20)| 00:00:01 | | |
| 100 | SORT GROUP BY | | 1 | 57 | | 5 (20)| 00:00:01 | | |
| 101 | NESTED LOOPS | | 1 | 57 | | 4 (0)| 00:00:01 | | |
| 102 | TABLE ACCESS BY INDEX ROWID | TPC_SECONDARY_RUNS | 1 | 22 | | 1 (0)| 00:00:01 | | |
|*103 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | | | 0 (0)| 00:00:01 | | |
|*104 | TABLE ACCESS STORAGE FULL | TPC_CUSTOM_FORM | 1 | 35 | | 3 (0)| 00:00:01 | | |
| 105 | NESTED LOOPS | | 2415 | 221K| | 60 (0)| 00:00:01 | | |
|*106 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | 6 | | 0 (0)| 00:00:01 | | |
| 107 | PARTITION HASH SINGLE | | 2415 | 207K| | 60 (0)| 00:00:01 | KEY | KEY |
|*108 | TABLE ACCESS STORAGE FULL | TPC_SEC_P_L_ANALYSIS | 2415 | 207K| | 60 (0)| 00:00:01 | KEY | KEY |
|*109 | HASH JOIN | | 1 | 265 | | 850 (1)| 00:00:01 | | |
| 110 | NESTED LOOPS OUTER | | 1 | 239 | | 848 (1)| 00:00:01 | | |
| 111 | VIEW | | 1 | 224 | | 842 (1)| 00:00:01 | | |
| 112 | HASH GROUP BY | | 1 | 118 | | 842 (1)| 00:00:01 | | |
|*113 | HASH JOIN RIGHT OUTER | | 1220 | 140K| | 65 (2)| 00:00:01 | | |
| 114 | VIEW | | 1 | 19 | | 5 (20)| 00:00:01 | | |
| 115 | HASH GROUP BY | | 1 | 57 | | 5 (20)| 00:00:01 | | |
| 116 | NESTED LOOPS | | 1 | 57 | | 4 (0)| 00:00:01 | | |
| 117 | TABLE ACCESS BY INDEX ROWID | TPC_SECONDARY_RUNS | 1 | 22 | | 1 (0)| 00:00:01 | | |
|*118 | INDEX UNIQUE SCAN | TPC_SECONDARY_RUNS_PK | 1 | | | 0 (0)| 00:00:01 | | |
|*119 | TABLE ACCESS STORAGE FULL | TPC_CUSTOM_FORM | 1 | 35 | | 3 (0)| 00:00:01 | | |
| 120 | PARTITION HASH SINGLE | | 1220 | 117K| | 60 (0)| 00:00:01 | 4 | 4 |
|*121 | TABLE ACCESS STORAGE FULL | TPC_SEC_P_L_ANALYSIS | 1220 | 117K| | 60 (0)| 00:00:01 | 4 | 4 |
| 122 | VIEW PUSHED PREDICATE | VW_SSQ_1 | 1 | 15 | | 6 (0)| 00:00:01 | | |
| 123 | SORT GROUP BY | | 1 | 30 | | 6 (0)| 00:00:01 | | |
| 124 | PARTITION HASH SINGLE | | 96 | 2880 | | 6 (0)| 00:00:01 | KEY | KEY |
|*125 | TABLE ACCESS STORAGE FULL | TPC_SEC_PAYROLL_COSTS | 96 | 2880 | | 6 (0)| 00:00:01 | KEY | KEY |
| 126 | VIEW | | 1 | 26 | | 2 (0)| 00:00:01 | | |
| 127 | TABLE ACCESS STORAGE FULL | SYS_TEMP_BFD9ECD84_FF0EB19B | 1 | 19 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - storage("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_19')
filter("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_19' AND "TPC_FND_LOOKUP_VALUES"."DESCRIPTION"=:B1 AND
"TPC_FND_LOOKUP_VALUES"."TAG"=SUBSTR(:B2,1,LENGTH("TPC_FND_LOOKUP_VALUES"."TAG")) AND "TPC_FND_LOOKUP_VALUES"."OIC_RUN_ID"=:B3)
3 - filter("TPC_FND_LOOKUP_VALUES"."TAG"=:B1 AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_20')
4 - filter(ROWNUM=1)
5 - storage("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8' AND "TPC_FND_LOOKUP_VALUES"."TAG"='Endava Inc')
filter("TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8' AND "TPC_FND_LOOKUP_VALUES"."TAG"='Endava Inc')
6 - filter(ROWNUM=1)
7 - storage("TPC_FND_LOOKUP_VALUES"."TAG"='Other' AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8')
filter("TPC_FND_LOOKUP_VALUES"."TAG"='Other' AND "TPC_FND_LOOKUP_VALUES"."LOOKUP_TYPE"='ENDAVA_TPC_8')
10 - filter("LOOKUP_CODE"=:B1 AND "LOOKUP_TYPE"='ENDAVA_TPC_5')
14 - filter("TAG"=:B1 AND "LOOKUP_TYPE"='ENDAVA_TPC_27')
16 - access("FV"."OIC_RUN_ID"="TPC_SECONDARY_RUNS"."OIC_RUN_ID")
17 - filter("TPC_SECONDARY_RUNS"."FROM_DATE">=NVL("START_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")-1) AND
"TPC_SECONDARY_RUNS"."FROM_DATE"<=NVL("END_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")+1))
18 - filter("FLEX_VALUE"=:B1 AND "DESCRIPTION"=:B2)
21 - filter("LOOKUP_TYPE"='ENDAVA_TPC_4' AND "FV"."DESCRIPTION"=:B1 AND LNNVL("TAG"<>:B2))
23 - access("FV"."OIC_RUN_ID"="TPC_SECONDARY_RUNS"."OIC_RUN_ID")
24 - filter("TPC_SECONDARY_RUNS"."FROM_DATE">=NVL("START_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")-1) AND
"TPC_SECONDARY_RUNS"."FROM_DATE"<=NVL("END_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")+1))
25 - filter("LOOKUP_CODE"=:B1 AND "LOOKUP_TYPE"='ENDAVA_TPC_1')
29 - filter("PC"."OIC_RUN_ID"=:B1 AND "PC"."LEGAL_ENTITY"=:B2)
32 - storage("TPC"."BU_CLASSIFICATION"='Sales' AND "TPC"."OIC_RUN_ID"=29201492)
filter("TPC"."BU_CLASSIFICATION"='Sales' AND "TPC"."OIC_RUN_ID"=29201492)
35 - filter("OIC_RUN_ID"=:B1)
42 - access("TA"."OIC_RUN_ID"="PLA"."OIC_RUN_ID" AND "TA"."LEGAL_ENTITY"="PLA"."LEGAL_ENTITY")
51 - access("TA"."LEGAL_ENTITY"="PC"."LEGAL_ENTITY"(+) AND "TA"."BUSINESS_UNIT"="PC"."BUSINESS_UNIT"(+) AND
"TA"."COST_CENTRE"="PC"."COST_CENTRE"(+))
54 - access("TPC"."LEGAL_ENTITY"="TA"."LEGAL_ENTITY")
58 - storage("EMPLOYEE_BU_CLASS"='non-MSD')
filter("EMPLOYEE_BU_CLASS"='non-MSD')
59 - access("TSR"."OIC_RUN_ID"="TA"."OIC_RUN_ID")
72 - access("TPC_SECONDARY_RUNS"."OIC_RUN_ID"="FV"."OIC_RUN_ID")
filter("TPC_SECONDARY_RUNS"."FROM_DATE">=NVL("START_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")-1) AND
"TPC_SECONDARY_RUNS"."FROM_DATE"<=NVL("END_DATE_ACTIVE",INTERNAL_FUNCTION("TPC_SECONDARY_RUNS"."FROM_DATE")+1))
74 - storage("FV"."LOOKUP_TYPE"='ENDAVA_TPC_1' OR "FV"."LOOKUP_TYPE"='ENDAVA_TPC_27' OR "FV"."LOOKUP_TYPE"='ENDAVA_TPC_4' OR
"FV"."LOOKUP_TYPE"='ENDAVA_TPC_5')
filter("FV"."LOOKUP_TYPE"='ENDAVA_TPC_1' OR "FV"."LOOKUP_TYPE"='ENDAVA_TPC_27' OR "FV"."LOOKUP_TYPE"='ENDAVA_TPC_4' OR
"FV"."LOOKUP_TYPE"='ENDAVA_TPC_5')
76 - access("TA"."LEGAL_ENTITY"(+)="PLA"."LEGAL_ENTITY" AND "TA"."BUSINESS_UNIT"(+)="PLA"."BUSINESS_UNIT" AND
"TA"."COST_CENTRE"(+)="PLA"."COST_CENTRE" AND "TA"."OIC_RUN_ID"(+)="TSR"."OIC_RUN_ID")
79 - filter("OIC_RUN_ID"="PLA"."OIC_RUN_ID")
84 - access("TA"."LEGAL_ENTITY"="PC"."LEGAL_ENTITY"(+) AND "TA"."BUSINESS_UNIT"="PC"."BUSINESS_UNIT"(+) AND
"TA"."COST_CENTRE"="PC"."COST_CENTRE"(+))
87 - access("TPC"."LEGAL_ENTITY"="TA"."LEGAL_ENTITY")
91 - storage("EMPLOYEE_BU_CLASS"='non-MSD')
filter("EMPLOYEE_BU_CLASS"='non-MSD')
92 - access("TSR"."OIC_RUN_ID"="TA"."OIC_RUN_ID")
98 - access("CF"."LEGAL_ENTITY_CF"(+)="PLA"."LEGAL_ENTITY" AND "CF"."OIC_RUN_ID"(+)="PLA"."OIC_RUN_ID")
103 - access("TPC_SECONDARY_RUNS"."OIC_RUN_ID"="PLA"."OIC_RUN_ID")
104 - storage("CF"."OIC_RUN_ID"="TPC_SECONDARY_RUNS"."OIC_RUN_ID")
filter("CF"."OIC_RUN_ID"="TPC_SECONDARY_RUNS"."OIC_RUN_ID" AND "CF"."OIC_RUN_ID"="PLA"."OIC_RUN_ID" AND
"TPC_SECONDARY_RUNS"."FROM_DATE"<=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY') AND
"TPC_SECONDARY_RUNS"."TO_DATE">=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY'))
106 - access("TSR"."OIC_RUN_ID"="PLA"."OIC_RUN_ID")
108 - filter("PLA"."OIC_RUN_ID"="PLA"."OIC_RUN_ID" AND NVL("NATURAL_ACC_TYPE",'XXXX')<>'Income')
109 - access("A"."OIC_RUN_ID"="MAIN"."OIC_RUN_ID")
113 - access("PLA"."OIC_RUN_ID"="CF"."OIC_RUN_ID"(+))
118 - access("TPC_SECONDARY_RUNS"."OIC_RUN_ID"=29601962)
119 - storage("CF"."OIC_RUN_ID"=29601962 AND "TPC_SECONDARY_RUNS"."FROM_DATE"<=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR01
0",'DD-MM-YYYY') AND "TPC_SECONDARY_RUNS"."TO_DATE">=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY'))
filter("CF"."OIC_RUN_ID"=29601962 AND "TPC_SECONDARY_RUNS"."FROM_DATE"<=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR010
",'DD-MM-YYYY') AND "TPC_SECONDARY_RUNS"."TO_DATE">=TO_DATE('01-'||"EXTN_ATTRIBUTE_CHAR009"||'-'||"EXTN_ATTRIBUTE_CHAR010",'DD-MM-YYYY'))
121 - storage("PLA"."OIC_RUN_ID"=29601962)
filter("PLA"."OIC_RUN_ID"=29601962)
125 - filter("OIC_RUN_ID"="MAIN"."OIC_RUN_ID")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
any pointers will be highly appreciated…
version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Thanks,
Aleks