Para Cristina Álvarez, quien confió en mi desde el primer momento y lo disimuló con toda su alma. 🙂
La siguiente consulta ha sido cancelada tras una hora y veinte minutos de ejecución.
SELECT N2.ENTIDAD_ID,
CLI.rowid row_id, cli.*,
MAX(REL.ABONADO_PADRE_ID) OVER (PARTITION BY ABONADO_HIJO_ID) REL_ABONADO_PADRE_ID
FROM BITOWN03.BS_V_MGEC_NODO_BIT_03 N1,
BITOWN03.BS_V_MGEC_NODO_BIT_03 N2,
BITOWN03.BS_V_MGEC_REL_NODOS_BIT_03 RN,
BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
BITOWN03.RE_C_RELACIONES_ABONADO_BIT_03 REL
WHERE N1.ENTIDAD_ID= CLI.COD_DNICIF
AND N1.NODO_ID=RN.NODO_ID
AND RN.TIPO_RELACION_ID=2 AND RN.FECHA_FIN_DT IS NULL
AND RN.NODO_PADRE_ID=N2.NODO_ID
AND CLI.COD_ABONADO = REL.ABONADO_HIJO_ID (+);
con el siguiente plan de ejecución:
PLAN_TABLE_OUTPUT
—————————————————————————————————
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 278 | 2496 |
| 1 | SORT UNIQUE | | 1 | 278 | 2496 |
| 2 | WINDOW SORT | | 1 | 278 | 2496 |
| 3 | NESTED LOOPS | | 1 | 278 | 2479 |
| 4 | NESTED LOOPS | | 1 | 261 | 2478 |
| 5 | NESTED LOOPS OUTER | | 1 | 244 | 2477 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 232 | 2477 |
|* 7 | TABLE ACCESS FULL | BS_V_MGEC_REL_NODOS_BIT_03 | 1 | 22 | 70 |
| 8 | BUFFER SORT | | 1486K| 297M| 2407 |
| 9 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 1486K| 297M| 2407 |
|* 10 | INDEX FULL SCAN | PK_C_RELACIONES_ABONADO_BIT_03 | 1 | 12 | |
|* 11 | TABLE ACCESS BY INDEX ROWID| BS_V_MGEC_NODO_BIT_03 | 1 | 17 | 1 |
|* 12 | INDEX UNIQUE SCAN | PK_V_MGEC_NODO_BIT_03 | 1 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | BS_V_MGEC_NODO_BIT_03 | 1 | 17 | 1 |
|* 14 | INDEX UNIQUE SCAN | PK_V_MGEC_NODO_BIT_03 | 1 | | |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
7 – filter(“RN”.”TIPO_RELACION_ID”=2 AND “RN”.”FECHA_FIN_DT” IS NULL)
10 – access(“CLI”.”COD_ABONADO”=”REL”.”ABONADO_HIJO_ID”(+))
filter(“CLI”.”COD_ABONADO”=”REL”.”ABONADO_HIJO_ID”(+))
11 – filter(“N1″.”ENTIDAD_ID”=”CLI”.”COD_DNICIF”)
12 – access(“N1″.”NODO_ID”=”RN”.”NODO_ID”)
14 – access(“RN”.”NODO_PADRE_ID”=”N2″.”NODO_ID”)
********************************************************************************
SOLUCIÓN al caso.
********************************************************************************
Omitiendo el detalle que la cláusula distinct sobra. La mantenemos para que los planes resulten de ejecuciones similares.
SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03;
COUNT(*)
———-
118907
SQL> select table_name, num_rows from dba_tables where
2 table_name=’BS_V_MGEC_REL_NODOS_BIT_03′;
TABLE_NAME NUM_ROWS
—————————— ———-
BS_V_MGEC_REL_NODOS_BIT_03 118907
SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03
2 where TIPO_RELACION_ID=2 AND FECHA_FIN_DT IS NULL; –> FILTRO OPERACION 7
COUNT(*)
———-
12844
Pues parece que no… Se está produciendo un producto cartesiano de 1,5 millones de filas (operación 9 TABLE FULL SCAN) sobre 12844 elementos. Oracle está estimando mal la cardinalidad de las filas. Cree que con el filtro 7 únicamente obtendrá una fila y por eso el producto cartesiano no dispara el coste.
Graso error.
Es preciso analizar CORRECTAMENTE las tablas implicadas, indicando que se analicen también los valores de las columnas implicadas. Los comandos para realizar ese análisis son:
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>’usuario’,TABNAME=>’BS_V_MGEC_REL_NODOS_BIT_03′,METHOD_OPT=>’for all columns’);
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.71
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>’usuario’,TABNAME=>’BS_V_MGEC_NODO_BIT_03′,METHOD_OPT=>’for all columns’);
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.57
De esta forma, las estadísticas tienen también información sobre la cardinalidad de las columnas y el plan de ejecución cambia sustancialmente:
SQL> @c:oracleora92rdbmsadminutlxpls
PLAN_TABLE_OUTPUT
—————————————————————————————————
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 209K| 53M| | 12462 |
| 1 | SORT UNIQUE | | 209K| 53M| 112M| 12462 |
| 2 | WINDOW SORT | | 209K| 53M| 112M| 12462 |
| 3 | NESTED LOOPS OUTER | | 209K| 53M| | 4217 |
|* 4 | HASH JOIN | | 209K| 51M| | 4217 |
|* 5 | HASH JOIN | | 12841 | 589K| | 320 |
|* 6 | HASH JOIN | | 12841 | 388K| | 192 |
|* 7 | TABLE ACCESS FULL| BS_V_MGEC_REL_NODOS_BIT_03 | 12841 | 188K| | 70 |
| 8 | TABLE ACCESS FULL| BS_V_MGEC_NODO_BIT_03 | 128K| 2012K| | 112 |
| 9 | TABLE ACCESS FULL | BS_V_MGEC_NODO_BIT_03 | 128K| 2012K| | 112 |
| 10 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 1486K| 297M| | 2407 |
|* 11 | INDEX FULL SCAN | PK_C_RELACIONES_ABONADO_BIT_03 | 1 | 12 | | |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
4 – access(“N1″.”ENTIDAD_ID”=”CLI”.”COD_DNICIF”)
5 – access(“RN”.”NODO_PADRE_ID”=”N2″.”NODO_ID”)
6 – access(“N1″.”NODO_ID”=”RN”.”NODO_ID”)
7 – filter(“RN”.”TIPO_RELACION_ID”=2 AND “RN”.”FECHA_FIN_DT” IS NULL)
11 – access(“CLI”.”COD_ABONADO”=”REL”.”ABONADO_HIJO_ID”(+))
filter(“CLI”.”COD_ABONADO”=”REL”.”ABONADO_HIJO_ID”(+))
Note: cpu costing is off
Efectivamente, el coste del plan es mayor, pero REAL.
Sólo ha tardado 23 segundos!!
