martes, octubre 11, 2005

Optimización SQL. Ejemplo 4. Mala cardinalidad.

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.

Parece un plan bastante aceptable. No obstante, un producto cartesiano llama la atención... ¿únicamente se obtiene una fila? en ese caso, el cartesiano no hace daño. Vamos a consultar el total de filas de la tabla, si la tabla tiene generadas estadísticas (la columna num_rows de user_tables puede darnos una pista) y si el filtro de la operación 7 realmente resuelve una única fila.

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:\oracle\ora92\rdbms\admin\utlxpls

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

No hay comentarios:

Amazon