Optimización SQL. Ejemplo 1. Producto cartesiano.

Para Nacho, por haber descubierto la pieza que faltaba. :-P


La siguiente consulta se ha cancelado tras 15 horas y 37 minutos de ejecución.

SQL> select count(*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE,
  2    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID,
  3    HSCONTRATOS_ESTADOS.ESTADO_DE,
  4    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT,
  5    BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID
  6  FROM
  7    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
  8    BITOWN03.LK_C_ESTADOS_BIT_03  HSCONTRATOS_ESTADOS,
  9    BITOWN03.BS_C_CLIENTES_BIT_03,
10    BITOWN03.BS_C_CONTRATOS_BIT_03  CONTRATOS
11  WHERE  BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID          = CONTRATOS.ABONADO_ID
12    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = CONTRATOS.CONTRATO_ID
13    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID   = HSCONTRATOS_ESTADOS.ESTADO_ID
14    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
15                   BETWEEN  to_date('01-06-2005 00:00:00','DD-MM-YYYY HH24:MI:SS')
16                       AND  to_date('30-06-2005 23:59:59','DD-MM-YYYY HH24:MI:SS')
17   AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID  =  'BA');
  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
           *
ERROR at line 7:
ORA-00028: your session has been killed


Elapsed: 15:37:01.66

con el siguiente plan de ejecución:
SQL> @C:\ORACLE\ORA92\RDBMS\ADMIN\UTLXPLS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name                         | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                               |   461 | 24433 |    16 |       |       |
|   1 |  NESTED LOOPS                       |                               |   461 | 24433 |    16 |       |       |
|   2 |   MERGE JOIN CARTESIAN              |                               |  1448M|    58G|    16 |       |       |
|   3 |    NESTED LOOPS                     |                               |   971 | 35927 |    16 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID     | LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |       |       |
|*  5 |      INDEX UNIQUE SCAN              | PK_C_ESTADOS_BIT_03           |     1 |       |       |       |       |
|*  6 |     INDEX RANGE SCAN                | IDX_FECHA_ESTADO_CONT_JAVIER  |   971 | 16507 |    15 |       |       |
|   7 |    BUFFER SORT                      |                               |  1491K|  8739K|     1 |       |       |
|   8 |     INDEX FULL SCAN                 | PK_C_CLIENTES_BIT_03          |  1491K|  8739K|       |       |       |
|*  9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BS_C_CONTRATOS_BIT_03         |     1 |    10 |       | ROWID | ROW L |
|* 10 |    INDEX UNIQUE SCAN                | PK_C_CONTRATOS_BIT_03         |     1 |       |       |       |       |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
   6 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
       filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID" AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
   9 - filter("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")
  10 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID")

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Las tablas contienen estadísticas fieles. El producto cartesiano tampoco engaña: cruzará 58 Gb. para atender nuestra petición. De todas maneras, algo falla. Ese plan no es óptimo.

Las estadísticas se generan mediante la siguiente llamada a DBMS_STATS.

SQL> begin
  2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>usuario,
  3         TABNAME=>tabla,
  4         ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
  5         METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
  6         DEGREE=>4);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Es preciso no únicamente analizar las tablas, sino también los índices. Algunos de ellos están analizados con el procedimiento DBMS_STATS.GATHER_INDEX_STATS, pero una de las tablas está particionada. Lo mejor y más simple es incluir el parámetro CASCADE=>TRUE en la llamada a DBMS_STATS.

Es cierto que el coste de análisis de estas tablas incrementará. Además, el parámetro CASCADE afecta, no sólo a índices, sino también a vistas materializadas, particiones, etc. (es decir, a todas sus dependencias).

Con las tablas analizadas de este modo, el nuevo plan de ejecución es éste:

SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                         | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |   471 | 30615 |   405 |
|*  1 |  HASH JOIN                     |                               |   471 | 30615 |   405 |
|*  2 |   INDEX RANGE SCAN             | IDX_FECHA_ESTADO_CONT_JAVIER  |  8740 |   145K|    75 |
|   3 |   NESTED LOOPS                 |                               |   771K|    35M|   257 |
|   4 |    NESTED LOOPS                |                               |  1626K|    46M|   257 |
|   5 |     TABLE ACCESS BY INDEX ROWID| LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |
|*  6 |      INDEX UNIQUE SCAN         | PK_C_ESTADOS_BIT_03           |     1 |       |       |
|   7 |     INDEX FAST FULL SCAN       | IDX_ABONADO_CONTRATO          |  1626K|    15M|   256 |
|   8 |    TABLE ACCESS BY INDEX ROWID | BS_C_CLIENTES_BIT_03          |     1 |    18 |       |
|*  9 |     INDEX UNIQUE SCAN          | PK_C_CLIENTES_BIT_03          |     1 |       |       |
------------------------------------------------------------------------------------------------

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

   1 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID" AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID")
   2 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd
              hh24:mi:ss'))
       filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
   6 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
   9 - access("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")

Note: cpu costing is off

30 rows selected.

La ejecución ahora sólo tarda: 3 segundos.
El coste de análisis aumenta de 26 minutos a 32 minutos. La ganancia justifica el coste.

Muy bien. :-)