Optimización SQL. Ejemplo 1. Producto cartesiano.


Para Nacho, por haber descubierto la pieza que faltaba. 😛

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:ORACLEORA92RDBMSADMINUTLXPLS

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:oracleora92rdbmsadminutlxpls

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. 🙂

Deja una respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.