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);
Best Answer
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.