BD2 · S10 · Optimización del rendimiento¶
SGE-JD · JD Refrigeración S.A.C. · Equipo CherryTree · Yoel Wagner Chambi Sirena · 10/11/2026
Trabajo autónomo de la sesión 10. Se verifica con evidencia, sin suponer nada:
- si las estadísticas están vigentes;
- si los índices de S5 se usan de verdad;
- si AWR está disponible en esta instancia;
- cómo se obtiene el mismo diagnóstico sin AWR;
- si un índice compuesto se justifica.
Se hizo en la base de práctica, con la seguridad de S8 y el almacenamiento de S9.
| Orden | Script | Se ejecuta como |
|---|---|---|
| 0 | S10_00_pausar_auditoria.sql / S10_00_reanudar_auditoria.sql | SYSTEM: la política de S9 no debe registrar la carga de prueba |
| 1 | S10_01_volumen_ordenado.sql | JDREFRIGERACION: unas 27 000 ventas en orden de fecha y los índices de S5 |
| 2 | S10_02_estadisticas_y_planes.sql | JDREFRIGERACION |
| 3 | S10_03b_ejecutar_consulta.sql y después S10_03_awr_y_vsql.sql | JDREFRIGERACION / SYSDBA |
| 4 | S10_04_indice_compuesto.sql | JDREFRIGERACION |
1. Punto de partida: una carga que se parece a JD¶
En S5 la carga de S4 tenía las fechas al azar sobre IDs consecutivos. El índice de fecha quedó con un
CLUSTERING_FACTOR de 28 974, casi una fila por bloque, y el optimizador no lo usaba para un mes. En JD las ventas
entran en el orden en que ocurren. Por eso esta vez se cargó día por día, del 01/01/2024 a hoy, con 20 a 40
ventas por día laborable:
Ventas cargadas: 26942 (total en la tabla: 27 200 · líneas: 81 531)
Index created. IX_VENTAS_FECHA_VENTA (en TS_SGEJD_VENTAS)
Index created. IX_DETALLE_VENTA_VENTA (en TS_SGEJD_VENTAS)
2. ¿Estadísticas vigentes?¶
TABLE_NAME FILAS_SEGUN_ESTADISTICAS FILAS_REALES ANALIZADA STALE_S
DETALLE_VENTA 733 81531 10/11 20:43 YES
VENTAS 258 27200 10/11 20:43 YES
Las estadísticas creían que había 258 ventas, cuando había 27 200: 105 veces menos. Es el mismo error de S4.
Se corrigieron con DBMS_STATS.GATHER_TABLE_STATS(USER, …, cascade => TRUE). Con cascade, también se recalculan
las estadísticas de los índices de la tabla:
DETALLE_VENTA 81531 10/11 20:44 NO
VENTAS 27200 10/11 20:44 NO
3. ¿Se usan los índices de S5? Ahora sí¶
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR BLOQUES_TABLA FILAS_TABLA
IX_DETALLE_VENTA_VENTA 178 27312 596 499 81531
IX_VENTAS_FECHA_VENTA 73 897 730 373 27200 ← en S5: 28 974
Con las ventas en orden de fecha, el factor de agrupamiento bajó de 28 974 a 730, cerca de los 373 bloques de la tabla. Las ventas de un mismo mes están en bloques vecinos. La consulta de Rentabilidad de S4 (versión reescrita, octubre de 2026), que en S5 ignoraba el índice, ahora lo usa:
| Id | Operation | Name | Rows | Cost |
| 0 | SELECT STATEMENT | | 1606 | 175 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED | VENTAS | 817 | 27 |
|* 10 | INDEX RANGE SCAN | IX_VENTAS_FECHA_VENTA | 842 | 4 |
| 11 | TABLE ACCESS FULL | DETALLE_VENTA | 81531 | 138 |
STATEMENT_ID COST (forzando FULL sobre VENTAS y DETALLE_VENTA con un hint)
MES 175
FULL 251
El optimizador eligió el índice porque cuesta 175 frente a 251, y no porque exista. DETALLE_VENTA se sigue
leyendo completa: para unirla con 817 ventas, un HASH JOIN sobre la tabla entera le resulta más barato que 817
búsquedas por IX_DETALLE_VENTA_VENTA.
4. AWR: intento real¶
NAME VALUE
statistics_level TYPICAL
control_management_pack_access DIAGNOSTIC+TUNING
SNAPSHOTS_PREVIOS (en FREEPDB1) 0
BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; → PL/SQL procedure successfully completed.
SNAP_ID INICIO FIN
2 10/11 20:44:42 10/11 20:45:20 (segunda corrida)
1 10/11 20:43:08 10/11 20:44:42
El comando funcionó, y la instancia declara los paquetes de Diagnóstico y Tuning como habilitados. Que funcione no significa que esté permitido. AWR pertenece al Diagnostics Pack, que se licencia aparte, y Oracle no lo bloquea técnicamente. Usarlo en un servidor real de JD sin esa licencia sería uso no autorizado, aunque nunca dé un error. Por eso el diagnóstico de la sección 5 se hace sin AWR.
5. El mismo diagnóstico con vistas V$ (sin licencia)¶
Las consultas más caras en memoria ahora:
SQL_ID EJEC MS_TOTAL MS_POR_EJEC LECTURAS_POR_EJEC TEXTO
3qkhfbf2kyvhk 4 126 31.5 401 SELECT POS+1 POS, VAL, NONNULLS, NDV, … (interna de DBMS_STATS)
1t3xkwr9xva5j 5 42 8.35 527 SELECT /*+ GATHER_PLAN_STATISTICS S10_RENTABILIDAD */ D.ID FROM DETALL…
2f002z1xcdb8a 54 16 .3 3 SELECT VALUE FROM SYS.NLS_SESSION_PARAMETERS …
Plan real de la consulta de Rentabilidad (DBMS_XPLAN.DISPLAY_CURSOR, ALLSTATS LAST):
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 2251 | 485 |
|* 2 | HASH JOIN | | 1 | 2438 | 2251 | 485 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS | 1 | 817 | 765 | 43 |
|* 4 | INDEX RANGE SCAN | IX_VENTAS_FECHA_VENTA | 1 | 842 | 793 | 4 |
| 5 | TABLE ACCESS FULL | DETALLE_VENTA | 1 | 81531 | 81531 | 442 |
Responde las mismas preguntas que un reporte AWR, pero solo para lo que está en memoria ahora:
- qué tan bien estimó: 817 filas estimadas y 765 reales;
- dónde está el costo:
DETALLE_VENTAse lleva 442 de los 485 bloques; - si el índice ayudó:
VENTAScostó 43 bloques, frente a los 373 de leer la tabla completa.
6. ¿Índice compuesto? Medido antes de decidir¶
Consulta real: el historial de un cliente en un periodo (VentaRepository.buscar(estado, desde, hasta,
clienteId), filtro de cliente en la pantalla de Ventas).
COLUMNAS DISTINTOS FILAS SELECTIVIDAD
cliente_id 37 27200 .00136
fecha_venta 897 27200 .03298
estado 2 27200 .00007
(cliente_id, fecha_venta) 18577 27200 .68298 ← casi única
(estado, fecha_venta) 1437 27200 .05283 ← apenas mejor que la fecha sola
INDICES_SOBRE_CLIENTE_ID: 0 ← la FK VENTAS.CLIENTE_ID no tiene ningún índice
solo cliente 12 714 filas
solo octubre 793 filas
cliente 12 y octubre 27 filas
| Plan | Costo | |
|---|---|---|
| Antes | INDEX RANGE SCAN sobre IX_VENTAS_FECHA_VENTA (842 entradas) y filtro CLIENTE_ID = 12 sobre cada fila |
28 |
Después de (cliente_id, fecha_venta) |
INDEX RANGE SCAN sobre IX_VENTAS_CLIENTE_FECHA con las dos condiciones como acceso (22 entradas) |
12 |
| Solo el periodo, sin cliente | Sigue usando IX_VENTAS_FECHA_VENTA (el compuesto empieza por cliente y no sirve) |
27 |
Decisión: se crea IX_VENTAS_CLIENTE_FECHA (cliente_id, fecha_venta). Se justifica por tres razones medidas:
- Reduce el costo de 28 a 12 en una consulta real de la pantalla de Ventas.
- La combinación es casi única (0,68).
- Cubre la clave foránea
VENTAS.CLIENTE_ID, que no tenía índice. Ese es el mismo riesgo de bloqueo que S5 encontró enDETALLE_VENTA.VENTA_ID: sin índice en la FK, borrar o cambiar un cliente bloqueaVENTAS.
El orden de las columnas importa: va primero la de igualdad (cliente) y después la de rango (fecha). Al revés, el índice serviría para el periodo pero no podría usar el cliente como acceso.
Descartado: (estado, fecha_venta). Su selectividad (0,053) apenas mejora la de la fecha sola (0,033), porque
el 97 % de las ventas está REGISTRADA (S5). Sería un segundo índice para casi la misma información, y habría que
mantenerlo en cada venta y en cada anulación.
7. Relación con ADS y LP2¶
| BD2 | ADS | LP2 |
|---|---|---|
Estadísticas al día con cascade |
Operación (stakeholder DBA, S1) | En producción lo hace el job automático; esta copia lo tenía atrasado |
IX_VENTAS_FECHA_VENTA usado de verdad |
Rendimiento (RNF-02) | lineasVendidas, registradasEntre |
IX_VENTAS_CLIENTE_FECHA |
Rendimiento | VentaRepository.buscar con clienteId; historial del cliente |
AWR sin licencia, diagnóstico con V$ |
Restricción de costo para JD (ADR-S4-01: una PC, sin licencias extra) | — |
8. Error o hallazgo¶
Hallazgo 1: la lección de S5 se cumplió. En S5 el índice de fecha no se usaba para un mes, y la causa
diagnosticada fue el orden de la carga, no el índice. Con los datos en orden de fecha, el CLUSTERING_FACTOR pasó de
28 974 a 730, y el mismo índice, para la misma consulta, ahora se usa: costo 175 frente a 251. Si me hubiera
quedado con la conclusión de S5 ("el índice no sirve para un mes"), habría descartado un índice que en la
realidad de JD sí ayuda.
Hallazgo 2: AWR funcionó, y eso no lo hace válido. Se esperaba un posible bloqueo por licencia. No lo hubo:
CREATE_SNAPSHOT corrió sin error y control_management_pack_access dice DIAGNOSTIC+TUNING. Tampoco había
snapshots automáticos previos en FREEPDB1 (0), a diferencia de lo que anticipaba la guía. El riesgo está
justamente ahí: nada técnico le avisa a quien lo usa que está consumiendo una opción de pago.
Hallazgo 3: V$SQL es memoria, no historia. La primera vez que se buscó el cursor de la consulta de
Rentabilidad en V$SQL, ya no estaba: no rows selected y cannot fetch plan, aunque se había ejecutado un
minuto antes. Se volvió a ejecutar y se buscó de inmediato, y entonces apareció (1t3xkwr9xva5j, 5 ejecuciones).
El diagnóstico con V$ solo sirve para lo que ocurre ahora. Para comparar con ayer hace falta guardar el
resultado, que es exactamente lo que hace AWR y lo que se paga.
Error propio, por tercera vez: COUNT(*) junto a una subconsulta escalar sin agrupar da ORA-00937. Me pasó
en S5, en S6 y aquí. La forma que funciona es calcular cada conteo en su propia tabla derivada (WITH … d1, d2, d3)
y unirlas.
9. Reflexión¶
¿Por qué un índice que "suena razonable" puede terminar sin usarse, y qué cuesta mantenerlo de todas formas?
El optimizador no usa un índice porque exista: lo usa si, con las estadísticas reales, le sale más barato. En S5,
el índice de fecha "sonaba razonable" para un mes y no se usaba, porque los datos estaban desordenados. Hoy, con
los datos como en la realidad, sí se usa. El de (estado, fecha_venta) también sonaba razonable, y la medición
mostró que casi no agrega nada sobre el de fecha. Si se hubiera creado igual, Oracle lo actualizaría en cada venta
registrada y en cada anulación, para siempre, sin que ninguna consulta lo aprovechara. Ese costo nadie lo ve en un
plan, porque se paga en cada escritura. El compuesto (cliente_id, fecha_venta) se creó porque los números lo
justificaron: costo de 28 a 12, una combinación casi única y una FK que no tenía índice.