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