These are two explain plans of two different queries that return the same results but at a drastically different speed (280 second vs 1-7 seconds).
I want some help knowing what the fast query has so that I can replicate it in the future.
EDIT:
Here is the explain plan for the fast query:
Plan hash value: 3147806849
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 528 | 46 (9)| 00:00:01 | | |
| 1 | NESTED LOOPS SEMI | | 1 | 528 | 46 (9)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 526 | 43 (10)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 338 | 26 (4)| 00:00:01 | | |
|* 4 | HASH JOIN | | 1 | 333 | 25 (4)| 00:00:01 | | |
|* 5 | HASH JOIN | | 1 | 324 | 23 (5)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 264 | 19 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 264 | 19 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 229 | 18 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 215 | 17 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | 1 | 192 | 16 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 153 | 15 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 142 | 15 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS OUTER | | 1 | 71 | 13 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS OUTER | | 1 | 62 | 12 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS OUTER | | 1 | 54 | 11 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 42 | 10 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 6 | 126 | 4 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | CAT_ESTATUS_ACCION | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | PK_EST_ACC_ESTATUS_ACCION_ID | 1 | | 0 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | PRAO_BLOQUES_PROGRAMADOS | 6 | 48 | 3 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS BY GLOBAL INDEX ROWID| PRAO_PROGRAMACIONES_UNIVERSOS | 1 | 21 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 22 | INDEX RANGE SCAN | IXN_PRAOPRUN_IDBLPR | 81 | | 1 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | PRAO_BRIGADAS | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | PK_PRAOBRIG_IDBRIG | 1 | | 0 (0)| 00:00:01 | | |
| 25 | TABLE ACCESS BY INDEX ROWID | PRAO_BRIGADAS_CUPOS | 1 | 8 | 1 (0)| 00:00:01 | | |
|* 26 | INDEX UNIQUE SCAN | PK_PRAOBRCU_IDBRCU | 1 | | 0 (0)| 00:00:01 | | |
| 27 | TABLE ACCESS BY INDEX ROWID | AMPO_PERSONAS | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 28 | INDEX UNIQUE SCAN | PK_AMPOPERS_IDPERS | 1 | | 0 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS BY GLOBAL INDEX ROWID | ADIO_UNIVERSOS | 1 | 71 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 30 | INDEX UNIQUE SCAN | PK_ADIOUNIV_IDUNIV | 1 | | 1 (0)| 00:00:01 | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | ADIO_ARCHIVOS_INSUMOS | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 32 | INDEX UNIQUE SCAN | PK_ADIOARIN_IDARIN | 1 | | 0 (0)| 00:00:01 | | |
| 33 | TABLE ACCESS BY INDEX ROWID | CAT_LOCALIDADES | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | PK_LOCALIDADES_LOCALIDAD_ID | 1 | | 0 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS BY INDEX ROWID | CAT_MUNICIPIOS | 1 | 23 | 1 (0)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | PK_MUNICIPIOS_MUNICIPIO_ID | 1 | | 0 (0)| 00:00:01 | | |
| 37 | TABLE ACCESS BY INDEX ROWID | CAT_ESTADOS | 1 | 14 | 1 (0)| 00:00:01 | | |
|* 38 | INDEX UNIQUE SCAN | PK_ESTADOS_ESTADO_ID | 1 | | 0 (0)| 00:00:01 | | |
|* 39 | INDEX UNIQUE SCAN | PK_PROCESOS_PROCESO_ID | 1 | | 0 (0)| 00:00:01 | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | CAT_PROCESOS | 1 | 35 | 1 (0)| 00:00:01 | | |
| 41 | VIEW | | 6 | 360 | 4 (25)| 00:00:01 | | |
| 42 | HASH GROUP BY | | 6 | 126 | 4 (25)| 00:00:01 | | |
| 43 | TABLE ACCESS FULL | PRAO_PERIODOS_PROGRAMADOS | 6 | 126 | 3 (0)| 00:00:01 | | |
| 44 | VIEW | index$_join$_004 | 14 | 126 | 2 (0)| 00:00:01 | | |
|* 45 | HASH JOIN | | | | | | | |
| 46 | INDEX FAST FULL SCAN | IXN_DIMEDEES_IDESCE | 14 | 126 | 1 (0)| 00:00:01 | | |
| 47 | INDEX FAST FULL SCAN | PK_DIMEDEES_IDDEES | 14 | 126 | 1 (0)| 00:00:01 | | |
|* 48 | TABLE ACCESS BY INDEX ROWID | DIME_ESCENARIOS | 1 | 5 | 1 (0)| 00:00:01 | | |
|* 49 | INDEX UNIQUE SCAN | PK_DIMEESCE_IDESCE | 1 | | 0 (0)| 00:00:01 | | |
| 50 | VIEW PUSHED PREDICATE | | 1 | 188 | 17 (18)| 00:00:01 | | |
| 51 | NESTED LOOPS OUTER | | 1 | 75 | 17 (18)| 00:00:01 | | |
| 52 | NESTED LOOPS | | 1 | 61 | 5 (0)| 00:00:01 | | |
|* 53 | INDEX UNIQUE SCAN | XPK_FAMILIA | 1 | 7 | 2 (0)| 00:00:01 | | |
| 54 | MAT_VIEW ACCESS BY INDEX ROWID | INTEGRANTE | 1 | 54 | 3 (0)| 00:00:01 | | |
|* 55 | INDEX RANGE SCAN | IXN_INTEGRANTE_FAM_ES_TIT | 1 | | 2 (0)| 00:00:01 | | |
| 56 | VIEW PUSHED PREDICATE | | 1 | 14 | 12 (25)| 00:00:01 | | |
| 57 | SORT UNIQUE | | 1 | 42 | 12 (25)| 00:00:01 | | |
| 58 | NESTED LOOPS OUTER | | 1 | 42 | 11 (19)| 00:00:01 | | |
| 59 | MAT_VIEW ACCESS BY INDEX ROWID | DOMICILIO_FAMILIA | 1 | 12 | 3 (0)| 00:00:01 | | |
|* 60 | INDEX UNIQUE SCAN | XPK_DOMFAM_FAMILIAID | 1 | | 2 (0)| 00:00:01 | | |
| 61 | MAT_VIEW ACCESS BY INDEX ROWID | DOMICILIO_FAMILIA_ANT | 1 | 20 | 3 (0)| 00:00:01 | | |
|* 62 | INDEX UNIQUE SCAN | XPKDOMFAM_FAM_ID | 1 | | 2 (0)| 00:00:01 | | |
| 63 | VIEW | VM_ESTE_FAMILIAS | 1 | 20 | 9 (23)| 00:00:01 | | |
| 64 | NESTED LOOPS | | 1 | 14 | 9 (23)| 00:00:01 | | |
|* 65 | INDEX UNIQUE SCAN | XPK_FAMILIA | 1 | | 2 (0)| 00:00:01 | | |
| 66 | VIEW | | 1 | 2 | 6 (34)| 00:00:01 | | |
| 67 | SORT UNIQUE | | 2 | 28 | 6 (34)| 00:00:01 | | |
| 68 | UNION ALL PUSHED PREDICATE | | | | | | | |
|* 69 | INDEX UNIQUE SCAN | XPK_DOMFAM_FAMILIAID | 1 | 7 | 2 (0)| 00:00:01 | | |
|* 70 | INDEX UNIQUE SCAN | XPKDOMFAM_FAM_ID | 1 | 7 | 2 (0)| 00:00:01 | | |
| 71 | TABLE ACCESS BY INDEX ROWID | CAT_MANZANAS | 1 | 22 | 2 (0)| 00:00:01 | | |
|* 72 | INDEX UNIQUE SCAN | PK_MANZANAS_MANZANA_ID | 1 | | 1 (0)| 00:00:01 | | |
| 73 | VIEW PUSHED PREDICATE | | 318 | 636 | 3 (0)| 00:00:01 | | |
|* 74 | INDEX RANGE SCAN | IXN_DIME_IDDETALLEESCENARIO | 557 | 1671 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("AAI"."ID_ARCHIVO_INSUMO"="ID_ARCHIVO_INSUMO")
5 - access("PBP"."ID_BLOQUE_PROGRAMADO"="PP"."ID_BLOQUE_PROGRAMADO")
19 - access("CEA"."ESTATUS_ACCION_ID"=1)
21 - filter("PPU"."ID_ESTATUS_ACCION"=1)
22 - access("PPU"."ID_BLOQUE_PROGRAMADO"="PBP"."ID_BLOQUE_PROGRAMADO")
24 - access("PBP"."ID_BRIGADA"="PB"."ID_BRIGADA"(+))
26 - access("PBP"."ID_BRIGADA_CUPO"="PBC"."ID_BRIGADA_CUPO"(+))
28 - access("PBC"."ID_PERSONA"="AP"."ID_PERSONA"(+))
29 - filter("AU"."CARGA_VALIDA"=1)
30 - access("AU"."ID_UNIVERSO"="PPU"."ID_UNIVERSO")
31 - filter("AAI"."ID_ESTATUS_ARCHIVO_UNIVERSO"=2 OR "AAI"."ID_ESTATUS_ARCHIVO_UNIVERSO"=4)
32 - access("AAI"."ID_ARCHIVO_INSUMO"="AU"."ID_ARCHIVO_INSUMO")
34 - access("AU"."ID_LOCALIDAD"="ECL"."LOCALIDAD_ID")
36 - access("ECL"."MUNICIPIO_ID"="ECM"."MUNICIPIO_ID")
38 - access("ECM"."ESTADO_ID"="ECE"."ESTADO_ID")
39 - access("AAI"."ID_PROCESO"="CP"."PROCESO_ID")
40 - filter("CP"."TIPO_PROCESO_ID"=2)
45 - access(ROWID=ROWID)
48 - filter("DE"."ESTATUS_ESCENARIO"=2)
49 - access("DDE"."ID_ESCENARIO"="DE"."ID_ESCENARIO")
53 - access("F"."FAMILIA_ID"="AU"."ID_FAMILIA_DGPL")
55 - access("I"."ES_TITULAR"=1 AND "I"."FAMILIA_ID"="AU"."ID_FAMILIA_DGPL")
60 - access("D"."FAMILIA_ID"=:B1)
62 - access("FAMILIA_ID"=:B1)
65 - access("F"."FAMILIA_ID"="F"."FAMILIA_ID")
69 - access("DF"."FAMILIA_ID"="F"."FAMILIA_ID")
70 - access("DFA"."FAMILIA_ID"="F"."FAMILIA_ID")
72 - access("VMEF"."ID_MANZANA"="VMM"."MANZANA_ID"(+))
74 - access("ID_DETALLE_ESCENARIO"="DDE"."ID_DETALLE_ESCENARIO")
And this is the slow query explain plan:
Plan hash value: 566099118
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 345 | 270 (2)| 00:00:04 | | |
| 1 | MAT_VIEW ACCESS BY INDEX ROWID | INTEGRANTE | 1 | 37 | 4 (0)| 00:00:01 | | |
|* 2 | INDEX RANGE SCAN | IXN_INTEGRANTE_FAM_ES_TIT | 1 | | 3 (0)| 00:00:01 | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | INTEGRANTE | 1 | 27 | 4 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IXN_INTEGRANTE_FAM_ES_TIT | 1 | | 3 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 2 | 84 | 12 (17)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 2 | 84 | 12 (17)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 2 | 40 | 8 (25)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | XPK_FAMILIA | 1 | 7 | 2 (0)| 00:00:01 | | |
| 9 | VIEW | | 2 | 26 | 6 (34)| 00:00:01 | | |
| 10 | SORT UNIQUE | | 2 | 28 | 6 (34)| 00:00:01 | | |
| 11 | UNION-ALL | | | | | | | |
|* 12 | INDEX UNIQUE SCAN | XPK_DOMFAM_FAMILIAID | 1 | 7 | 2 (0)| 00:00:01 | | |
|* 13 | INDEX UNIQUE SCAN | XPKDOMFAM_FAM_ID | 1 | 7 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | PK_MANZANAS_MANZANA_ID | 1 | | 1 (0)| 00:00:01 | | |
| 15 | MAT_VIEW ACCESS BY INDEX ROWID | DOMICILIO_FAMILIA | 1 | 12 | 3 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | XPK_DOMFAM_FAMILIAID | 1 | | 2 (0)| 00:00:01 | | |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | DOMICILIO_FAMILIA_ANT | 1 | 20 | 3 (0)| 00:00:01 | | |
|* 18 | INDEX UNIQUE SCAN | XPKDOMFAM_FAM_ID | 1 | | 2 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | CAT_MANZANAS | 1 | 22 | 2 (0)| 00:00:01 | | |
| 20 | SORT AGGREGATE | | 1 | 3 | | | | |
|* 21 | TABLE ACCESS FULL | PRAO_PERIODOS_PROGRAMADOS | 1 | 3 | 3 (0)| 00:00:01 | | |
| 22 | HASH UNIQUE | | 1 | 345 | 270 (2)| 00:00:04 | | |
|* 23 | HASH JOIN OUTER | | 1 | 345 | 269 (1)| 00:00:04 | | |
| 24 | NESTED LOOPS | | 1 | 298 | 264 (0)| 00:00:04 | | |
| 25 | NESTED LOOPS | | 1 | 298 | 264 (0)| 00:00:04 | | |
| 26 | NESTED LOOPS OUTER | | 1 | 284 | 263 (0)| 00:00:04 | | |
| 27 | NESTED LOOPS OUTER | | 1 | 275 | 262 (0)| 00:00:04 | | |
| 28 | NESTED LOOPS OUTER | | 1 | 267 | 261 (0)| 00:00:04 | | |
| 29 | NESTED LOOPS OUTER | | 1 | 255 | 260 (0)| 00:00:04 | | |
|* 30 | FILTER | | | | | | | |
| 31 | NESTED LOOPS OUTER | | 1 | 247 | 260 (0)| 00:00:04 | | |
| 32 | NESTED LOOPS | | 1 | 226 | 258 (0)| 00:00:04 | | |
| 33 | NESTED LOOPS | | 1 | 191 | 257 (0)| 00:00:04 | | |
| 34 | NESTED LOOPS | | 1 | 171 | 256 (0)| 00:00:04 | | |
| 35 | NESTED LOOPS | | 1 | 166 | 248 (0)| 00:00:03 | | |
| 36 | NESTED LOOPS | | 1 | 132 | 247 (0)| 00:00:03 | | |
|* 37 | HASH JOIN | | 7 | 350 | 9 (0)| 00:00:01 | | |
|* 38 | HASH JOIN | | 7 | 189 | 6 (0)| 00:00:01 | | |
| 39 | NESTED LOOPS | | 7 | 126 | 4 (0)| 00:00:01 | | |
| 40 | TABLE ACCESS BY INDEX ROWID | CAT_ESTATUS_ACCION | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | PK_EST_ACC_ESTATUS_ACCION_ID | 1 | | 0 (0)| 00:00:01 | | |
|* 42 | TABLE ACCESS FULL | DIME_ESCENARIOS | 7 | 35 | 3 (0)| 00:00:01 | | |
| 43 | VIEW | index$_join$_025 | 14 | 126 | 2 (0)| 00:00:01 | | |
|* 44 | HASH JOIN | | | | | | | |
| 45 | INDEX FAST FULL SCAN | IXN_DIMEDEES_IDESCE | 14 | 126 | 1 (0)| 00:00:01 | | |
| 46 | INDEX FAST FULL SCAN | PK_DIMEDEES_IDDEES | 14 | 126 | 1 (0)| 00:00:01 | | |
|* 47 | TABLE ACCESS FULL | ADIO_ARCHIVOS_INSUMOS | 16 | 368 | 3 (0)| 00:00:01 | | |
| 48 | PARTITION RANGE ALL | | 1 | 82 | 34 (0)| 00:00:01 | 1 | 17 |
|* 49 | TABLE ACCESS BY LOCAL INDEX ROWID| ADIO_UNIVERSOS | 1 | 82 | 34 (0)| 00:00:01 | 1 | 17 |
|* 50 | INDEX RANGE SCAN | IXN_ADIOUNIVERSOS_ARINOPERLOC | 2039K| | 34 (0)| 00:00:01 | 1 | 17 |
| 51 | TABLE ACCESS BY INDEX ROWID | CAT_LOCALIDADES | 1 | 34 | 1 (0)| 00:00:01 | | |
|* 52 | INDEX UNIQUE SCAN | PK_LOCALIDADES_LOCALIDAD_ID | 1 | | 0 (0)| 00:00:01 | | |
|* 53 | TABLE ACCESS BY INDEX ROWID | DIME_DISTRIBUCIONES_METAS | 10 | 50 | 8 (0)| 00:00:01 | | |
|* 54 | INDEX RANGE SCAN | IXN_DIMEDIME_IDESIDLOIDMU | 144 | | 1 (0)| 00:00:01 | | |
| 55 | TABLE ACCESS BY INDEX ROWID | CAT_MUNICIPIOS | 1 | 20 | 1 (0)| 00:00:01 | | |
|* 56 | INDEX UNIQUE SCAN | PK_MUNICIPIOS_MUNICIPIO_ID | 1 | | 0 (0)| 00:00:01 | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | CAT_PROCESOS | 1 | 35 | 1 (0)| 00:00:01 | | |
|* 58 | INDEX UNIQUE SCAN | PK_PROCESOS_PROCESO_ID | 1 | | 0 (0)| 00:00:01 | | |
| 59 | TABLE ACCESS BY GLOBAL INDEX ROWID | PRAO_PROGRAMACIONES_UNIVERSOS | 1 | 21 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 60 | INDEX RANGE SCAN | IXN_PRAOPRUN_IDUNIV | 1 | | 1 (0)| 00:00:01 | | |
| 61 | TABLE ACCESS BY INDEX ROWID | PRAO_BLOQUES_PROGRAMADOS | 1 | 8 | 0 (0)| 00:00:01 | | |
|* 62 | INDEX UNIQUE SCAN | PK_PRAOBLPR_IDBLPR | 1 | | 0 (0)| 00:00:01 | | |
| 63 | TABLE ACCESS BY INDEX ROWID | PRAO_BRIGADAS | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 64 | INDEX UNIQUE SCAN | PK_PRAOBRIG_IDBRIG | 1 | | 0 (0)| 00:00:01 | | |
| 65 | TABLE ACCESS BY INDEX ROWID | PRAO_BRIGADAS_CUPOS | 1 | 8 | 1 (0)| 00:00:01 | | |
|* 66 | INDEX UNIQUE SCAN | PK_PRAOBRCU_IDBRCU | 1 | | 0 (0)| 00:00:01 | | |
| 67 | TABLE ACCESS BY INDEX ROWID | AMPO_PERSONAS | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 68 | INDEX UNIQUE SCAN | PK_AMPOPERS_IDPERS | 1 | | 0 (0)| 00:00:01 | | |
|* 69 | INDEX UNIQUE SCAN | PK_ESTADOS_ESTADO_ID | 1 | | 0 (0)| 00:00:01 | | |
| 70 | TABLE ACCESS BY INDEX ROWID | CAT_ESTADOS | 1 | 14 | 1 (0)| 00:00:01 | | |
| 71 | VIEW | | 6 | 282 | 4 (25)| 00:00:01 | | |
| 72 | HASH GROUP BY | | 6 | 126 | 4 (25)| 00:00:01 | | |
| 73 | TABLE ACCESS FULL | PRAO_PERIODOS_PROGRAMADOS | 6 | 126 | 3 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."ES_TITULAR"=1 AND "I"."FAMILIA_ID"=:B1)
4 - access("I"."ES_TITULAR"=1 AND "I"."FAMILIA_ID"=:B1)
8 - access("F"."FAMILIA_ID"=:B1)
12 - access("DF"."FAMILIA_ID"=:B1)
13 - access("DFA"."FAMILIA_ID"=:B1)
14 - access("VMM"."MANZANA_ID"=NVL( (SELECT "MANZANA_ID" FROM "VM_SIIOP"."DOMICILIO_FAMILIA" "D" WHERE "D"."FAMILIA_ID"=:B1), (SELECT
"MANZANA_ID" FROM "VM_SIIOP"."DOMICILIO_FAMILIA_ANT" "DOMICILIO_FAMILIA_ANT" WHERE "FAMILIA_ID"=:B2)))
16 - access("D"."FAMILIA_ID"=:B1)
18 - access("FAMILIA_ID"=:B1)
21 - filter("PRAOPERIOD1_"."ID_BLOQUE_PROGRAMADO"=:B1)
23 - access("PP"."ID_BLOQUE_PROGRAMADO"(+)="PBP"."ID_BLOQUE_PROGRAMADO")
30 - filter(CASE WHEN "PPU"."ID_BLOQUE_PROGRAMADO" IS NOT NULL THEN "PPU"."ID_ESTATUS_ACCION" ELSE 0 END <>7 AND CASE WHEN
"PPU"."ID_BLOQUE_PROGRAMADO" IS NOT NULL THEN "PPU"."ID_ESTATUS_ACCION" ELSE 0 END =1)
37 - access("AAI"."ID_ARCHIVO_INSUMO"="DDE"."ID_ARCHIVO_INSUMO")
38 - access("DE"."ID_ESCENARIO"="DDE"."ID_ESCENARIO")
41 - access("ACEA"."ESTATUS_ACCION_ID"=1)
42 - filter("DE"."ESTATUS_ESCENARIO"=2)
44 - access(ROWID=ROWID)
47 - filter("AAI"."ID_ESTATUS_ARCHIVO_UNIVERSO"=2 OR "AAI"."ID_ESTATUS_ARCHIVO_UNIVERSO"=4)
49 - filter(("AU"."PROGRAMA_ID"=1 OR "AU"."PROGRAMA_ID"=2) AND "AU"."CARGA_VALIDA"=1)
50 - access("AAI"."ID_ARCHIVO_INSUMO"="AU"."ID_ARCHIVO_INSUMO")
52 - access("AU"."ID_LOCALIDAD"="ECL"."LOCALIDAD_ID")
53 - filter("DDE"."ID_DETALLE_ESCENARIO"="DDM"."ID_DETALLE_ESCENARIO")
54 - access("DDM"."ID_ESTADO"="AU"."ID_ESTADO")
56 - access("AU"."ID_MUNICIPIO"="ECM"."MUNICIPIO_ID")
57 - filter("ACP"."TIPO_PROCESO_ID"=2)
58 - access("ACP"."PROCESO_ID"="AAI"."ID_PROCESO")
60 - access("PPU"."ID_UNIVERSO"(+)="AU"."ID_UNIVERSO")
62 - access("PPU"."ID_BLOQUE_PROGRAMADO"="PBP"."ID_BLOQUE_PROGRAMADO"(+))
64 - access("PB"."ID_BRIGADA"(+)="PBP"."ID_BRIGADA")
66 - access("PBC"."ID_BRIGADA_CUPO"(+)="PBP"."ID_BRIGADA_CUPO")
68 - access("AP"."ID_PERSONA"(+)="PBC"."ID_PERSONA")
69 - access("AU"."ID_ESTADO"="ECE"."ESTADO_ID")
Best Answer
My findings:
Slow running query:
--------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------------------------------- | 48 | PARTITION RANGE ALL | | 1 | 82 | 34 (0)| 00:00:01 | 1 | 17 | |* 49 | TABLE ACCESS BY LOCAL INDEX ROWID| ADIO_UNIVERSOS | 1 | 82 | 34 (0)| 00:00:01 | 1 | 17 | |* 50 | INDEX RANGE SCAN | IXN_ADIOUNIVERSOS_ARINOPERLOC | 2039K| | 34 (0)| 00:00:01 | 1 | 17 | 49 - filter(("AU"."PROGRAMA_ID"=1 OR "AU"."PROGRAMA_ID"=2) AND "AU"."CARGA_VALIDA"=1) 50 - access("AAI"."ID_ARCHIVO_INSUMO"="AU"."ID_ARCHIVO_INSUMO"Fast running query:
--------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------------------------------- |* 29 | TABLE ACCESS BY GLOBAL INDEX ROWID | ADIO_UNIVERSOS | 1 | 71 | 2 (0)| 00:00:01 | ROWID | ROWID | |* 30 | INDEX UNIQUE SCAN | PK_ADIOUNIV_IDUNIV | 1 | | 1 (0)| 00:00:01 | | | 29 - filter("AU"."CARGA_VALIDA"=1) 30 - access("AU"."ID_UNIVERSO"="PPU"."ID_UNIVERSO")I found that the table
ADIO_UNIVERSOSis accessed by a Local IndexIXN_ADIOUNIVERSOS_ARINOPERLOC(INDEX RANGE SCAN) which has returned 2039K rows, scans several partitions(1-17). In the fast running query the same table is accessed by Global IndexPK_ADIOUNIV_IDUNIV(INDEX UNIQUE SCAN) has returned 1 row, which didn't run through all the partitions.Index Range Scan obviously returns more rows than Index Unique Scan. Here, in the slow running query execution plan, Index Range Scan is influencing the Nested Loop(Cost 269).