Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Joining to a view kills the performance

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

Comments
Post Details
Added on Feb 8 2024
7 comments
191 views