1

We have two databases with the same data, let's call it Database A which is the original and Database B which is the backup from Database A. A query is performing much better in Database B for example the query takes 42 ms and in Database A takes 10 seconds. I noticed that the execution plans are different. I already asked the DBA of the company but he has no clue what could the problem be. There are 42 entries in the execution plan of Database A and only 33 entries in Database A, maybe that could be the problem but I don't know.

UPDATE: I've been researching and I found out that this has something to do with hash join. Database A is performing hash join on VW_PERSONA while Database B is performing NESTED JOIN. The end result is that HASH JOIN for this case performs bad. What could be the reason that Database A prefers HASH JOIN instead of NESTED LOOPS JOIN? I verified this by forcing a HASH JOIN using a hint in Database B and it performed as bad as Database A.

Version of the Database: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

This is the query:

SELECT t1.ID, t1.CLAVEESTATUS, t1.IDEJECUTIVO, t1.GERENCIA_ID, t1.IDTIPOEJECUTIVO, t1.IDDOMICILIO, t1.IDPERSONA, t0.ID, t0.CLAVEESTATUS, t0.CORREOELECTRONICO, t0.DESCRIPCION, t0.IDGERENCIA, t0.CENTROOPERACION_ID, t0.IDDOMICILIO, t0.IDPERSONARESPONSABLE, t2.BCATPURAASEG, t2.CALLENUMERO, t2.CIUDAD, t2.CLAVECIUDAD, t2.CLAVEESTADO, t2.CLAVEPAIS, t2.CLAVEUSUARIOMODIFICACION, t2.CODIGOPOSTAL, t2.ESTADO, t2.FECHAMODIFICACION, t2.IDCOLONIA, t2.idPersona, t2.COLONIA, t2.TIPODOMICILIO, t2.idDomicilio, t3.IDPERSONA, t3.APELLIDOMATERNO, t3.APELLIDOPATERNO, t3.BCAPTURAASEG, t3.CLAVEESTADOCIVIL, t3.CLAVEESTADONACIMIENTO, t3.CLAVEMUNICIPIONACIMIENTO, t3.CLAVENACIONALIDAD, t3.CLAVERAMAACTIV, t3.CLAVERAMAPERSONAFISICA, t3.CLAVERESIDENCIA, t3.CLAVESEXO, t3.CLAVESITUACION, t3.CLAVESUBRAMAACTIV, t3.CLAVETIPOPERSONA, t3.CLAVETIPOSECTOR, t3.CLAVETIPOSECTORMORAL, t3.CLAVETIPOSITUACION, t3.CLAVEUSUARIOMODIFICACION, t3.CORREOSADICIONALES, t3.CURP, t3.EMAIL, t3.FACEBOOK, t3.FECHAALTA, t3.FECHABAJA, t3.FECHACONSTITUCION, t3.FECHAMODIFICACION, t3.FECHANACIMIENTO, t3.IDREPRESENTANTE, t3.LUGARNACIMIENTO, t3.NOMBRE, t3.NOMBRECOMPLETO, t3.NOMBRECONTACTO, t3.PAGINAWEB, t3.PUESTOCONTACTO, t3.RAZONSOCIAL, t3.CODIGORFC, t3.TELEFONOCASA, t3.TELEFONOCELULAR, t3.TELEFONOFAX, t3.TELEFONOOFICINA, t3.TELEFONOSADICIONALES, t3.TWITTER 
    FROM MIDAS.TOEJECUTIVO t1 
    LEFT OUTER JOIN MIDAS.TOGERENCIA t0 ON (t0.ID = t1.GERENCIA_ID) 
    LEFT OUTER JOIN MIDAS.VW_DOMICILIO t2 ON ((t2.idDomicilio = t1.IDDOMICILIO) AND (t2.idPersona = t1.IDPERSONA)) 
    LEFT OUTER JOIN MIDAS.VW_PERSONA t3 ON (t3.IDPERSONA = t1.IDPERSONA);

The execution plans for that query:

Database A (performs bad, takes 10 seconds):

PLAN_TABLE_OUTPUT
Plan hash value: 1113260721

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |    79 |   108K|       |   996K  (1)| 03:19:14 |
|*  1 |  HASH JOIN OUTER                      |                     |    79 |   108K|       |   996K  (1)| 03:19:14 |
|   2 |   VIEW                                |                     |    79 | 40290 |       |   659  (13)| 00:00:08 |
|   3 |    NESTED LOOPS OUTER                 |                     |    79 | 43687 |       |   659  (13)| 00:00:08 |
|   4 |     VIEW                              |                     |    79 | 22199 |       |     7  (15)| 00:00:01 |
|*  5 |      HASH JOIN OUTER                  |                     |    79 |  5925 |       |     7  (15)| 00:00:01 |
|   6 |       TABLE ACCESS FULL               | TOEJECUTIVO         |    79 |  1343 |       |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL               | TOGERENCIA          |    31 |  1798 |       |     3   (0)| 00:00:01 |
|*  8 |     VIEW PUSHED PREDICATE             |                     |     1 |   272 |       |     8  (13)| 00:00:01 |
|   9 |      WINDOW SORT                      |                     |     1 |   195 |       |     8  (13)| 00:00:01 |
|  10 |       NESTED LOOPS OUTER              |                     |     1 |   195 |       |     7   (0)| 00:00:01 |
|  11 |        NESTED LOOPS                   |                     |     1 |   156 |       |     5   (0)| 00:00:01 |
|  12 |         NESTED LOOPS                  |                     |     1 |   126 |       |     4   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID  | DOMICILIO           |     1 |    96 |       |     3   (0)| 00:00:01 |
|* 14 |           INDEX RANGE SCAN            | IDX01_DOMICILIO_PER |     1 |       |       |     2   (0)| 00:00:01 |
|* 15 |          TABLE ACCESS BY INDEX ROWID  | POBLACION           |     1 |    30 |       |     1   (0)| 00:00:01 |
|* 16 |           INDEX UNIQUE SCAN           | PK_POBLACION        |     1 |       |       |     1   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS BY INDEX ROWID   | POBLACION           |     1 |    30 |       |     1   (0)| 00:00:01 |
|* 18 |          INDEX UNIQUE SCAN            | PK_POBLACION        |     1 |       |       |     1   (0)| 00:00:01 |
|* 19 |        TABLE ACCESS BY INDEX ROWID    | CODIGO_POSTAL       |     1 |    39 |       |     2   (0)| 00:00:01 |
|* 20 |         INDEX RANGE SCAN              | PK_CODIGO_POSTAL    |     5 |       |       |     1   (0)| 00:00:01 |
|  21 |   VIEW                                | VW_PERSONA          |   917K|   782M|       |   995K  (1)| 03:19:06 |
|  22 |    MERGE JOIN OUTER                   |                     |   917K|   803M|       |   995K  (1)| 03:19:06 |
|  23 |     VIEW                              |                     |   917K|   787M|       |   760K  (1)| 02:32:05 |
|  24 |      MERGE JOIN OUTER                 |                     |   917K|   734M|       |   760K  (1)| 02:32:05 |
|  25 |       VIEW                            |                     |   917K|   691M|       |   531K  (1)| 01:46:13 |
|  26 |        MERGE JOIN OUTER               |                     |   917K|   631M|       |   531K  (1)| 01:46:13 |
|  27 |         VIEW                          |                     |   917K|   584M|       |   508K  (1)| 01:41:47 |
|  28 |          MERGE JOIN OUTER             |                     |   917K|    91M|       |   508K  (1)| 01:41:47 |
|  29 |           TABLE ACCESS BY INDEX ROWID | PERSONA             |   917K|    75M|       |   258K  (1)| 00:51:41 |
|  30 |            INDEX FULL SCAN            | PK_PERSONA          |   917K|       |       |  1546   (1)| 00:00:19 |
|* 31 |           SORT JOIN                   |                     |   916K|    16M|    84M|   250K  (1)| 00:50:06 |
|  32 |            TABLE ACCESS BY INDEX ROWID| PERSONA_EXT         |   916K|    16M|       |   245K  (1)| 00:49:01 |
|  33 |             INDEX FULL SCAN           | PK_PERSONA_EXT      |   916K|       |       |  1959   (1)| 00:00:24 |
|* 34 |         SORT JOIN                     |                     | 73382 |  3869K|    10M| 22109   (1)| 00:04:26 |
|  35 |          TABLE ACCESS BY INDEX ROWID  | PERSONA_MORAL       | 73382 |  3869K|       | 21130   (1)| 00:04:14 |
|  36 |           INDEX FULL SCAN             | PK_PERSONA_MORAL    | 73382 |       |       |   129   (1)| 00:00:02 |
|* 37 |       SORT JOIN                       |                     |   843K|    39M|   129M|   229K  (1)| 00:45:53 |
|  38 |        TABLE ACCESS BY INDEX ROWID    | PERSONA_FISICA      |   843K|    39M|       |   219K  (1)| 00:43:49 |
|  39 |         INDEX FULL SCAN               | PK_PERSONA_FISICA   |   843K|       |       |  1633   (1)| 00:00:20 |
|* 40 |     SORT JOIN                         |                     |   760K|    13M|    46M|   235K  (1)| 00:47:02 |
|  41 |      TABLE ACCESS BY INDEX ROWID      | RFC                 |   760K|    13M|       |   230K  (1)| 00:46:10 |
|  42 |       INDEX FULL SCAN                 | PK_RFC              |   760K|       |       |  1558   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."IDPERSONA"(+)="T1"."IDPERSONA")
   5 - access("T0"."ID"(+)="T1"."GERENCIA_ID")
   8 - filter("ID_DOMICILIO"(+)="T1"."IDDOMICILIO" AND "CUENTA"(+)="NUMERO"(+))
  14 - access("DOM"."ID_PERSONA"="T1"."IDPERSONA")
       filter("ID_PERSONA" IS NOT NULL)
  15 - filter("POBLACION"."CVE_TIPO_POBLAC"='ES' AND "POBLACION"."CVE_POBLAC_UBIC" IS NOT NULL AND 
              "POBLACION"."CVE_POBLAC_UBIC"="DOM"."CVE_PAIS")
  16 - access("POBLACION"."CVE_POBLACION"="DOM"."CVE_ESTADO")
  17 - filter("CIUDAD"."CVE_POBLAC_UBIC" IS NOT NULL AND "CIUDAD"."CVE_TIPO_POBLAC"='CD' AND 
              "CIUDAD"."CVE_POBLAC_UBIC"="DOM"."CVE_ESTADO")
  18 - access("CIUDAD"."CVE_POBLACION"="DOM"."CVE_CIUDAD")
  19 - filter("DOM"."COLONIA"="CP"."DESC_USR_CODPOST"(+))
  20 - access("DOM"."CODIGO_POSTAL"="CP"."CODIGO_POSTAL"(+))
  31 - access("PEXT"."ID_PERSONA"(+)="P"."ID_PERSONA")
       filter("PEXT"."ID_PERSONA"(+)="P"."ID_PERSONA")
  34 - access("MORAL"."ID_PERSONA"(+)="P"."ID_PERSONA")
       filter("MORAL"."ID_PERSONA"(+)="P"."ID_PERSONA")
  37 - access("FISICA"."ID_PERSONA"(+)="P"."ID_PERSONA")
       filter("FISICA"."ID_PERSONA"(+)="P"."ID_PERSONA")
  40 - access("RFC"."ID_PERSONA"(+)="P"."ID_PERSONA")
       filter("RFC"."ID_PERSONA"(+)="P"."ID_PERSONA")

Database B execution plan (performs better, takes 42 ms):

PLAN_TABLE_OUTPUT
Plan hash value: 2434982697

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |  1522 |  1863K|  1271   (7)| 00:00:16 |
|   1 |  NESTED LOOPS OUTER                 |                     |  1522 |  1863K|  1271   (7)| 00:00:16 |
|*  2 |   HASH JOIN RIGHT OUTER             |                     |    79 | 77578 |   619   (1)| 00:00:08 |
|   3 |    TABLE ACCESS FULL                | TOGERENCIA          |    31 |  1953 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS OUTER               |                     |    79 | 72601 |   616   (1)| 00:00:08 |
|   5 |     TABLE ACCESS FULL               | TOEJECUTIVO         |    79 |  1738 |     3   (0)| 00:00:01 |
|   6 |     VIEW PUSHED PREDICATE           | VW_PERSONA          |     1 |   897 |     8   (0)| 00:00:01 |
|   7 |      NESTED LOOPS OUTER             |                     |     1 |   260 |     8   (0)| 00:00:01 |
|   8 |       NESTED LOOPS OUTER            |                     |     1 |   232 |     6   (0)| 00:00:01 |
|   9 |        NESTED LOOPS OUTER           |                     |     1 |   175 |     5   (0)| 00:00:01 |
|  10 |         NESTED LOOPS OUTER          |                     |     1 |   118 |     4   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| PERSONA             |     1 |    98 |     2   (0)| 00:00:01 |
|* 12 |           INDEX UNIQUE SCAN         | PK_PERSONA          |     1 |       |     2   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID| RFC                 |     1 |    20 |     2   (0)| 00:00:01 |
|* 14 |           INDEX UNIQUE SCAN         | PK_RFC              |     1 |       |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID | PERSONA_FISICA      |     1 |    57 |     2   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN          | PK_PERSONA_FISICA   |     1 |       |     1   (0)| 00:00:01 |
|  17 |        TABLE ACCESS BY INDEX ROWID  | PERSONA_MORAL       |     1 |    57 |     1   (0)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN           | PK_PERSONA_MORAL    |     1 |       |     1   (0)| 00:00:01 |
|  19 |       TABLE ACCESS BY INDEX ROWID   | PERSONA_EXT         |     1 |    28 |     2   (0)| 00:00:01 |
|* 20 |        INDEX UNIQUE SCAN            | PK_PERSONA_EXT      |     1 |       |     1   (0)| 00:00:01 |
|* 21 |   VIEW PUSHED PREDICATE             |                     |     1 |   272 |     8  (13)| 00:00:01 |
|  22 |    WINDOW SORT                      |                     |     1 |   195 |     8  (13)| 00:00:01 |
|  23 |     NESTED LOOPS OUTER              |                     |     1 |   195 |     7   (0)| 00:00:01 |
|  24 |      NESTED LOOPS                   |                     |     1 |   156 |     5   (0)| 00:00:01 |
|  25 |       NESTED LOOPS                  |                     |     1 |   126 |     4   (0)| 00:00:01 |
|  26 |        TABLE ACCESS BY INDEX ROWID  | DOMICILIO           |     1 |    96 |     3   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN            | IDX01_DOMICILIO_PER |     1 |       |     2   (0)| 00:00:01 |
|* 28 |        TABLE ACCESS BY INDEX ROWID  | POBLACION           |     1 |    30 |     1   (0)| 00:00:01 |
|* 29 |         INDEX UNIQUE SCAN           | PK_POBLACION        |     1 |       |     1   (0)| 00:00:01 |
|* 30 |       TABLE ACCESS BY INDEX ROWID   | POBLACION           |     1 |    30 |     1   (0)| 00:00:01 |
|* 31 |        INDEX UNIQUE SCAN            | PK_POBLACION        |     1 |       |     1   (0)| 00:00:01 |
|* 32 |      TABLE ACCESS BY INDEX ROWID    | CODIGO_POSTAL       |     1 |    39 |     2   (0)| 00:00:01 |
|* 33 |       INDEX RANGE SCAN              | PK_CODIGO_POSTAL    |     5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T0"."ID"(+)="T1"."GERENCIA_ID")
  12 - access("P"."ID_PERSONA"="T1"."IDPERSONA")
  14 - access("RFC"."ID_PERSONA"(+)="T1"."IDPERSONA")
       filter("RFC"."ID_PERSONA"(+)="P"."ID_PERSONA")
  16 - access("FISICA"."ID_PERSONA"(+)="T1"."IDPERSONA")
       filter("FISICA"."ID_PERSONA"(+)="P"."ID_PERSONA")
  18 - access("MORAL"."ID_PERSONA"(+)="T1"."IDPERSONA")
       filter("MORAL"."ID_PERSONA"(+)="P"."ID_PERSONA")
  20 - access("PEXT"."ID_PERSONA"(+)="T1"."IDPERSONA")
       filter("PEXT"."ID_PERSONA"(+)="P"."ID_PERSONA")
  21 - filter("ID_DOMICILIO"(+)="T1"."IDDOMICILIO" AND "CUENTA"(+)="NUMERO"(+))
  27 - access("DOM"."ID_PERSONA"="T1"."IDPERSONA")
       filter("ID_PERSONA" IS NOT NULL)
  28 - filter("POBLACION"."CVE_TIPO_POBLAC"='ES' AND "POBLACION"."CVE_POBLAC_UBIC" IS NOT NULL AND 
              "POBLACION"."CVE_POBLAC_UBIC"="DOM"."CVE_PAIS")
  29 - access("POBLACION"."CVE_POBLACION"="DOM"."CVE_ESTADO")
  30 - filter("CIUDAD"."CVE_POBLAC_UBIC" IS NOT NULL AND "CIUDAD"."CVE_TIPO_POBLAC"='CD' AND 
              "CIUDAD"."CVE_POBLAC_UBIC"="DOM"."CVE_ESTADO")
  31 - access("CIUDAD"."CVE_POBLACION"="DOM"."CVE_CIUDAD")
  32 - filter("DOM"."COLONIA"="CP"."DESC_USR_CODPOST"(+))
  33 - access("DOM"."CODIGO_POSTAL"="CP"."CODIGO_POSTAL"(+))

VW_PERSONA (VIEW):

CREATE OR REPLACE FORCE VIEW MIDAS.VW_PERSONA
(
   IDPERSONA,
   IDDOMICILIO,
   CLAVERESIDENCIA,
   CLAVENACIONALIDAD,
   CLAVETIPOPERSONA,
   FECHAALTA,
   FECHABAJA,
   CLAVETIPOSITUACION,
   TELEFONOCASA,
   TELEFONOFAX,
   TELEFONOOFICINA,
   CLAVETIPOSECTOR,
   NOMBRECOMPLETO,
   FECHAMODIFICACION,
   CLAVEUSUARIOMODIFICACION,
   BCAPTURAASEG,
   EMAIL,
   CURP,
   TELEFONOCELULAR,
   NOMBRECONTACTO,
   PUESTOCONTACTO,
   FACEBOOK,
   TWITTER,
   PAGINAWEB,
   TELEFONOSADICIONALES,
   CORREOSADICIONALES,
   IDREPRESENTANTE,
   RAZONSOCIAL,
   FECHACONSTITUCION,
   CLAVERAMAACTIV,
   CLAVESUBRAMAACTIV,
   CLAVETIPOSECTORMORAL,
   NOMBRE,
   APELLIDOPATERNO,
   APELLIDOMATERNO,
   CLAVESITUACION,
   CLAVEESTADOCIVIL,
   CLAVERAMAPERSONAFISICA,
   CLAVESEXO,
   FECHANACIMIENTO,
   LUGARNACIMIENTO,
   CLAVEESTADONACIMIENTO,
   CLAVEMUNICIPIONACIMIENTO,
   CODIGORFC,
   SIGLASRFC,
   FECHARFC,
   HOMOCLAVERFC
)
AS
   SELECT /*+ INDEX(pext PK_PERSONA_EXT) INDEX(p PK_PERSONA) INDEX(fisica PK_PERSONA_FISICA) INDEX(moral PK_PERSONA_MORAL) INDEX(rfc PK_RFC)*/
         p  .id_persona AS idPersona,
            p.id_domicilio AS idDomicilio,
            p.cve_residencia AS claveResidencia,
            p.cve_nacionalidad AS claveNacionalidad,
            CASE WHEN p.cve_per_juridica = 'PF' THEN 1 ELSE 2 END
               AS claveTipoPersona,
            p.f_alta AS fechaAlta,
            p.f_baja AS fechaBaja,
            p.sit_persona AS claveTipoSituacion,
            p.telef_casa AS telefonoCasa,
            p.telef_fax AS telefonoFax,
            p.telef_oficina AS telefonoOficina,
            p.cve_tipo_sector AS claveTipoSector,
            p.nombre AS nombreCompleto,
            p.fh_modificacion AS fechaModificacion,
            p.cve_usuar_modif AS claveUsuarioModificacion,
            p.b_captura_aseg AS bCapturaAseg,
            --          pext.e_mail AS email,
            --          trim(TRANSLATE(pext.e_mail,'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ','aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC'))  email,
            TRIM (pext.e_mail) email,
            pext.curp AS curp,
            pext.telef_celular AS telefonoCelular,
            pext.nombre_contacto AS nombreContacto,
            pext.puesto_contacto AS puestoContacto,
            pext.facebook,
            pext.twitter,
            pext.pagina_web AS paginaWeb,
            pext.tels_adicionales AS telefonosAdicionales,
            pext.correos_adicionales AS correosAdicionales,
            pext.id_representante AS idRepresentante,
            moral.nom_razon_social AS razonSocial,
            moral.f_constitucion AS fechaConstitucion,
            moral.cve_rama_activ AS claveRamaActiv,
            moral.cve_sub_rama_ac AS claveSubramaActiv,
            moral.cve_tipo_sector AS claveTipoSectorMoral,
            fisica.nombre_persona AS nombre,
            fisica.apellido_paterno AS apellidoPaterno,
            fisica.apellido_materno AS apellidoMaterno,
            fisica.cve_actividad_pf AS claveSituacion,
            fisica.cve_estado_civil AS claveEstadoCivil,
            fisica.cve_rama_pf AS claveRamaPersonaFisica,
            fisica.cve_sexo AS claveSexo,
            fisica.f_nacimiento AS fechaNacimiento,
            fisica.lugar_nacim AS lugarNacimiento,
            fisica.cve_estado_nacim AS claveEstadoNacimiento,
            fisica.cve_munic_nacim AS claveMunicipioNacimiento,
            rfc.siglas_rfc || rfc.f_rfc || rfc.homoclave_rfc AS codigoRfc,
            rfc.siglas_rfc AS siglasRfc,
            rfc.f_rfc AS fechaRfc,
            rfc.homoclave_rfc AS homoclaveRfc
     FROM               SEYCOS.persona p
                     LEFT JOIN
                        SEYCOS.persona_ext pext
                     ON (pext.id_persona = p.id_persona)
                  LEFT JOIN
                     SEYCOS.persona_moral moral
                  ON (moral.id_persona = p.id_persona)
               LEFT JOIN
                  SEYCOS.persona_fisica fisica
               ON (fisica.id_persona = p.id_persona)
            LEFT JOIN
               SEYCOS.RFC rfc
            ON (rfc.id_persona = p.id_persona);
Improve this question
4
  • Are statistics up to date on A? Are there any differences in settings (including system statistics) and hardware between the two instances?
    – Colin 't Hart
    Sep 11, 2013 at 15:33
  • @Colin'tHart According to the DBA statistics are up to date. There are no differences in the configuration between the two databases.
    – Alfredo Osorio
    Sep 11, 2013 at 15:45
  • The explains plan are really different. there are based on the statistics. You may compare it using user_tables, user_tab_columns, user_indexes.
    – Nicolas Durand
    Sep 11, 2013 at 16:02
  • What kind of backup? Made by rman/datafile copies/snapshots or made by exports/imports
    – miracle173
    Sep 13, 2013 at 5:35

1 Answer 1

Reset to default
1

After researching for a while, the problem was that a parameter was different between the two databases. Database A had _complex_view_merging set to FALSE and Database B didn't have it. I just changed _complex_view_merging to TRUE in Database A and the query executed as quickly as Database B.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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