Performance - Oracle: Executive Plan: Is it reliable? - execution, Oracle, performance, plan, reliable

Please see the execution plan below.
It says that Oracle will start from the index range scan…
But it’s impossible because it doesn’t have any values to look up, Because it reads these values from the ENI_TAVOLO_ETL_DETT table.

Is the execution plan telling the truth?

The query is:

SELECT DISTINCT
 DTSC.TSC_ETL_ID "Tavolo ETL",
 DTSC.TIPO_PROCESSO_COD "TIPO_PROCESSO",< br ged .eif_campo24
 FROM etl_elab_interf_flat f1
 WHERE f1.eif_campo198 = DTSC.id_messaggio
 AND f1.idde_identif_dati_ext_id = DTSC.idde_identif_dati_ext_id
 AND ITURA f1. f1.eif_campo29 ='Nuovo')
 "C_ORD_IT",
 (SELECT TO_CHAR (
 TRUNC (DataCompetenzaProcesso (dtsc.id_messaggio,
 dtsc.idde_identif_dati_ext_id,
 dtsc. t ipo_processo_cod,
 c.pia_pod_attesa_id)),
'DD/MM/YYYY')
 FROM eni_flussi_hub c
 WHERE c.flh_id_messaggio = dtsc.id_messaggio
 AND dtsc. idde_identif_dati_ext_id = c.idde_identif_dati_ext_id)
 "DATA COMPETENZA",
 DTSC.id_messaggio "ID_MESSAGGIO",
 - ParseError (dtsc.mon_desc_errore_siu) "Tipo /> REPLACE (REGEX error_REPLACE) (dtsc.mon_desc_errore_siu,
'
- StackTrace
.*',
'',
 1,
 0,
'imn') ,
'
',
 '')
 "Errore",
 dtsc.IDDE_IDENTIF_DATI_EXT_ID
 FROM SIUINTEGRA.ENI_TAVOLO_ETL_DETT DTSC
 WHERE 1 = 1
 AND DTSC.DTSC_TAVOLO_DETT_ID = DTSC.DTSC_STORICO_ID
 AND DTSC.DTSC_DATA_FIN = TO_TIMESTAMP ('31-DIC-9999 00:00:00')
 --
 AND dtsc.tipo_processo_cod IN
 ('CC',
'NUOVA_ATT_ENI',
'VOLTURA_ATT',
'VOLTURA_ENI',
'AT',
 'VT',
'CC_POWER',
'VOLTURA_ATT_PWR')
 AND EXISTS
 (SELECT 1
 FROM NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF p
 WHERE p.id_messaggio = DTSC.id_messaggio);

Plan in text format

Plan
SELECT STATEMENT ALL_ROWSCost: 205 Bytes: 758 Cardinality: 1 CPU Cost: 82.337.155 IO Cost: 199 Time: 3 
 2 TABLE ACCESS BY INDEX ROWID TABLE SIUETL.ETL_ELAB_INTERF_FLAT Object Instance: 1 Filter Predicates: "F1"."EIF_C AMPO198"=:B1 AND "F1"."EIF_CAMPO200"='FORNITURA_ENI' AND "F1"."EIF_CAMPO29"='Nuovo' Cost: 6 Bytes: 27 Cardinality: 1 CPU Cost: 103.802 IO Cost: 6 Time: 1 < br /> 1 INDEX RANGE SCAN INDEX SIUETL.ETL_EIF_FK_IDX4 Search Columns: 1 Access Predicates: "F1"."IDDE_IDENTIF_DATI_EXT_ID"=:B1 Cost: 4 Cardinality: 9 CPU Cost: 40.772 IO Cost: 4 Time: 1 
 4 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.ENI_FLUSSI_HUB Object Instance: 2 Filter Predicates: "C"."IDDE_IDENTIF_DATI_EXT_ID"=:B1 Cost: 4 Bytes: 21 Cardinality: 1 CPU Cost: 40.070 IO Cost: 4 Time: 1 
 3 INDEX RANGE SCAN INDEX SIUINTEGRA.ENI_FLH_IDX6 Search Columns: 1 Access Predicates: "C"."FLH_ID_MESSAGGIO"=:B1 Cost: 3 Cardinality: 1 CPU Cost: 29.429 IO Cost: 3 Time: 1 
 12 HASH UNIQUE Cost : 205 Bytes: 758 Cardinality: 1 CPU Cost: 82.337.155 IO Cost: 199 Time: 3 
 11 NESTED LOOPS 
 9 NESTED LOOPS Cost: 204 Bytes: 758 Cardinality: 1 CPU Cost: 69.141.535 IO Cost: 199 Time: 3 
 6 SORT UNIQUE Cost: 35 Bytes: 611.020 Cardinality: 61.102 CPU Cost: 8.113.248 IO Cost: 34 Time: 1 
 5 INDEX FAST FULL SCAN INDEX NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF2 Cost: 35 Bytes: 611.020 Cardinality: 61.102 CPU Cost: 8.113.248 IO Cost: 34 Time: 1 
 8 INLIST ITERATOR 
 7 INDEX RANGE SCAN INDEX SIUINTEGRA.ENI_DTSC_UK4_IDX Search Columns: 2 Access Predicates: "P"."ID_MESSAGGIO"="DTSC"."ID_MESSAGGIO" AND ("DTSC"."TIPO_PROCESSO_COD"='AT' ORCO "DTSC".SO_TIPODSO "='CC' OR "DTSC"."TIPO_PROCESSO_COD"='CC_POWER' OR "DTSC"."TIPO_PROCESSO_COD"='NUOVA_ATT_ENI' OR "DTSC"."TIPO_PROCESSO_COD"='VOLTURA_ATT' OR "DTSC"."TIPO"=PROCESSO_ 'VOLTURA_ATT_PWR' OR "DTSC"."TIPO_PROCESSO_COD"='VOLTURA_ENI' OR "DTSC"."TIPO_PROCESSO_COD"='VT') Cost: 9 Cardinality: 1 CPU Cost: 162.886 IO Cost: 9 Time: 1 
 10 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.ENI_TAVOLO_ETL_DETT Object Instance: 3 Filter Predicates: "DTSC"."DTSC_TAVOLO_DETT_ID"="DTSC"."DTSC_STORICO_ID" AND "DTSC"." DTSC_DATA_FIN"=TO_TIMESTAMP('31-DIC-9999 00:00:00') Cost: 10 Bytes: 748 Cardinality: 1 CPU Cost: 173.206 IO Cost: 10 Time: 1

The current comments are correct.

The query plan first shows scalar subqueries, but they are querying from the driver Executed after extracting records in the .Today I learned that the cost of these subqueries is not even considered in the total cost, because Oracle does not know how many times they are executed, see Scalar Subselect Costing

Please check the execution plan below.
It says that Oracle will start with an index range scan…
But this is impossible because it doesn’t have any values to look up because it reads these from the ENI_TAVOLO_ETL_DETT table Value.

Is the execution plan telling the truth?

The query is:

SELECT DISTINCT
 DTSC.TSC_ETL_ID "Tavolo ETL",
 DTSC.TIPO_PROCESSO_COD "TIPO_PROCESSO",< br ged .eif_campo24
 FROM etl_elab_interf_flat f1
 WHERE f1.eif_campo198 = DTSC.id_messaggio
 AND f1.idde_identif_dati_ext_id = DTSC.idde_identif_dati_ext_id
 AND ITURA f1. f1.eif_campo29 ='Nuovo')
 "C_ORD_IT",
 (SELECT TO_CHAR (
 TRUNC (DataCompetenzaProcesso (dtsc.id_messaggio,
 dtsc.idde_identif_dati_ext_id,
 dtsc. tipo _processo_cod,
 c.pia_pod_attesa_id)),
'DD/MM/YYYY')
 FROM eni_flussi_hub c
 WHERE c.flh_id_messaggio = dtsc.id_messaggio
 AND dtsc. idde_identif_dati_ext_id = c.idde_identif_dati_ext_id)
 "DATA COMPETENZA",
 DTSC.id_messaggio "ID_MESSAGGIO",
 - ParseError (dtsc.mon_desc_errore_siu) "Tipo /> REPLACE (REGEX error_REPLACE) (dtsc.mon_desc_errore_siu,
'
- StackTrace
.*',
'',
 1,
 0,
'imn') ,
'
',
 '')
 "Errore",
 dtsc.IDDE_IDENTIF_DATI_EXT_ID
 FROM SIUINTEGRA.ENI_TAVOLO_ETL_DETT DTSC
 WH ERE 1 = 1
 AND DTSC.DTSC_TAVOLO_DETT_ID = DTSC.DTSC_STORICO_ID
 AND DTSC.DTSC_DATA_FIN = TO_TIMESTAMP ('31-DIC-9999 00:00:00')
 --
 AND dtsc.tipo_processo_cod IN
 ('CC',
'NUOVA_ATT_ENI',
'VOLTURA_ATT',
'VOLTURA_ENI',
'AT',
 'VT',
'CC_POWER',
'VOLTURA_ATT_PWR')
 AND EXISTS
 (SELECT 1
 FROM NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF p
 WHERE p.id_messaggio = DTSC.id_messaggio);

Plan in text format

Plan
SELECT STATEMENT ALL_ROWSCost: 205 Bytes: 758 Cardinality: 1 CPU Cost: 82.337.155 IO Cost: 199 Time: 3 
 2 TABLE ACCESS BY INDEX ROWID TABLE SIUETL.ETL_ELAB_INTERF_FLAT Object Instance: 1 Filter Predicates: "F1"."EIF_CAMPO19 8"=:B1 AND "F1"."EIF_CAMPO200"='FORNITURA_ENI' AND "F1"."EIF_CAMPO29"='Nuovo' Cost: 6 Bytes: 27 Cardinality: 1 CPU Cost: 103.802 IO Cost: 6 Time: 1 < br /> 1 INDEX RANGE SCAN INDEX SIUETL.ETL_EIF_FK_IDX4 Search Columns: 1 Access Predicates: "F1"."IDDE_IDENTIF_DATI_EXT_ID"=:B1 Cost: 4 Cardinality: 9 CPU Cost: 40.772 IO Cost: 4 Time: 1 
 4 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.ENI_FLUSSI_HUB Object Instance: 2 Filter Predicates: "C"."IDDE_IDENTIF_DATI_EXT_ID"=:B1 Cost: 4 Bytes: 21 Cardinality: 1 CPU Cost: 40.070 IO Cost: 4 Time: 1 
 3 INDEX RANGE SCAN INDEX SIUINTEGRA.ENI_FLH_IDX6 Search Columns: 1 Access Predicates: "C"."FLH_ID_MESSAGGIO"=:B1 Cost: 3 Cardinality: 1 CPU Cost: 29.429 IO Cost: 3 Time: 1 
 12 HASH UNIQUE Cost : 205 Bytes: 758 Cardinality: 1 CPU Cost: 82.337.155 IO Cost: 199 Time: 3 
 11 NESTED LOOPS 
 9 NESTED LOOPS Cost: 204 Bytes: 758 Cardinality: 1 CPU Cost: 69.141.535 IO Cost: 199 Time: 3 
 6 SORT UNIQUE Cost: 35 Bytes: 611.020 Cardinality: 61.102 CPU Cost: 8.113.248 IO Cost: 34 Time: 1 
 5 INDEX FAST FULL SCAN INDEX NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF2 Cost: 35 Bytes: 611.020 Cardinality: 61.102 CPU Cost: 8.113.248 IO Cost: 34 Time: 1 
 8 INLIST ITERATOR 
 7 INDEX RANGE SCAN INDEX SIUINTEGRA.ENI_DTSC_UK4_IDX Search Columns: 2 Access Predicates: "P"."ID_MESSAGGIO"="DTSC"."ID_MESSAGGIO" AND ("DTSC"."TIPO_PROCESSO_COD"='AT' ORCO "DTSC".SO_TIPODSO "='CC' OR "DTSC"."TIPO_PROCESSO_COD"='CC_POWER' OR "DTSC"."TIPO_PROCESSO_COD"='NUOVA_ATT_ENI' OR "DTSC"."TIPO_PROCESSO_COD"='VOLTURA_ATT' OR "DTSC"."TIPO"=PROCESSO_ 'VOLTURA_ATT_PWR' OR "DTSC"."TIPO_PROCESSO_COD"='VOLTURA_ENI' OR "DTSC"."TIPO_PROCESSO_COD"='VT') Cost: 9 Cardinality: 1 CPU Cost: 162.88 6 IO Cost: 9 Time: 1 
 10 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.ENI_TAVOLO_ETL_DETT Object Instance: 3 Filter Predicates: "DTSC"."DTSC_TAVOLO_DETT_ID"="DTSC"."DTSC_STORICO_ID" AND "DTSC"." DTSC_DATA_FIN"=TO_TIMESTAMP('31-DIC-9999 00:00:00') Cost: 10 Bytes: 748 Cardinality: 1 CPU Cost: 173.206 IO Cost: 10 Time: 1

< /p>

The comments are correct now.

The query plan first shows scalar subqueries, but they are executed after extracting records from the driving query. Today I learned about these The cost of subqueries is not even considered in the total cost, because Oracle does not know how many times they are executed, see Scalar Subselect Costing

Performance – Oracle: Executive Plan: Is it reliable?

Leave a Comment Cancel reply