Regarding difference in execution time of queries

Question

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")

Post EXPLAIN PLAN output for both queries, or we can't help you — Philᵀᴹ, Commented Feb 22, 2016 at 17:49
And I can't post the second one cuz I'm out of body space... — Erick Travis, Commented Feb 22, 2016 at 18:09
We do need both explain plans otherwise we can't compare. We would also need to see the predicates It's usually at the bottom after the execution plan. Every rows with a * has a predicate. Even better if you can do this for both: alter session set statistics_level='ALL'; <execute query without explain plan> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST'); — Nicolas de Fontenay, Commented Feb 22, 2016 at 18:14
There's a big difference between being the "same query" and returning the "same result". Query optimisation is frequently about obtaining the same result with different queries but improving speed. — Vérace, Commented Feb 22, 2016 at 22:22

atokpas · Accepted Answer · 2016-11-07 10:08:04Z

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_UNIVERSOS is accessed by a Local Index IXN_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 Index PK_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).

I agree, scanning through the plan, that was the first thing I noticed as well, tune the access on that table, it should help. — Ditto, Commented Feb 22, 2016 at 20:40
What do you mean with tune the access? I'm still new to this so I'm sorry for my lack of experience... — Erick Travis, Commented Feb 23, 2016 at 15:15
Analizing the database I have noticed that 'IXN_ADIOUNIVERSOS_ARINOPERLOC' is an index with 3 columns, is that affecting the performance? And why does the fast query access the primary key instead? — Erick Travis, Commented Feb 23, 2016 at 17:06
@Erick Travis . Your fast running query uses PK_ADIOUNIV_IDUNIV index to access ADIO_UNIVERSOS table. In the slow running query the plan uses IXN_ADIOUNIVERSOS_ARINOPERLOC index to access the same table, the predicate specified has caused to scan the parition(1-17). So yeah problem may exists around that index. Tune the access mean analyze the access methods(index range scan, unique scan, full scan etc). You can do that by changing the predicate used. — atokpas, Commented Feb 24, 2016 at 6:18
What if I can't modify or add any indexes? Is there some thing I can do in that case? — Erick Travis, Commented Feb 25, 2016 at 17:03

Nicolas de Fontenay · Accepted Answer · 2016-02-22 19:12:05Z

Looking at the bad query only: The following section is where your performance issues start:

|  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 |

The cost of that nested loop is almost all the cost of your query. Looking into that, Row 50 is accessing an index IXN_ADIOUNIVERSOS_ARINOPERLOC and planning to pull 2 million rows using the following predicate:

50 - access("AAI"."ID_ARCHIVO_INSUMO"="AU"."ID_ARCHIVO_INSUMO")

However, still based on estimates, it will filter all the rows out and keep only 1 (row 49)

49 - filter(("AU"."PROGRAMA_ID"=1 OR "AU"."PROGRAMA_ID"=2) AND "AU"."CARGA_VALIDA"=1)

it's very inefficient to grab so much rows just to throw out all of them minus 1. If you didn't find a way to rewrite that query yet, I would have told you to add the columns from predicate 49 in an index with column from predicate 50.

Also running the query and gather runtime statistics would tell us if the estimates match the actual run (I gave you instructions in comments).

Yeah, I didn't understand how to do what you said, tried to run it in sqldeveloper replacing the sentence between brackets with my queries but it returned an error. I probably got something wrong, sorry. — Erick Travis, Commented Feb 23, 2016 at 15:16

Stack Exchange Network

Regarding difference in execution time of queries

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
oracle
performance
or ask your own question.

Hot Network Questions

Regarding difference in execution time of queries

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracleperformance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
oracle
performance
or ask your own question.