1. Análisis Inicial de Consultas Lentas

El proceso de diagnóstico comienza examinando los registros de ejecución para identificar cuellos de botella. Se agrupan los tiempos de ejecución para detectar rangos problemáticos:

SELECT rango_tiempo_ejecucion, COUNT(*) 
FROM registro_log_sql_2024
GROUP BY rango_tiempo_ejecucion;

SELECT consulta_texto, parametros_texto, COUNT(*) 
FROM (
    SELECT TO_CHAR(consulta_texto) AS consulta_texto, 
           TO_CHAR(parametros_texto) AS parametros_texto, 
           LENGTH(parametros_texto) 
    FROM registro_log_sql_2024 
    WHERE (rango_tiempo_ejecucion IN ('10s_30s', '30s_1min', '1min_5min'))
      AND consulta_texto LIKE '% doc_gestion_principal %'
      AND LENGTH(parametros_texto) != 0
    ORDER BY tiempo_ejecucion DESC
) 
GROUP BY consulta_texto, parametros_texto;

2. Caso de Optimización: Filtrado LIKE e IN

Se identificó una consulta que tardaba aproximadamente 51 segundos. El objetivo era recuperar documentos específicos basados en el asunto y permisos de lectura.

SELECT doc_gestion_principal_.identificador_unico AS id_1_, 
       doc_gestion_principal_.sincronizacion_calendario AS sync_2_, 
       doc_gestion_principal_.ultima_modificacion AS mod_3_, 
       doc_gestion_principal_.titulo_documento AS titulo_4_, 
       doc_gestion_principal_.numero_actual AS num_5_, 
       doc_gestion_principal_.estado_documento AS estado_19_
FROM doc_gestion_principal doc_gestion_principal_ 
WHERE doc_gestion_principal_.identificador_unico IN (
    SELECT doc_gestion_principal_.identificador_unico 
    FROM doc_gestion_principal doc_gestion_principal_ 
    INNER JOIN doc_permisos_lectura permisos_lectura_ 
            ON doc_gestion_principal_.identificador_unico = permisos_lectura_.id_documento_ref
    INNER JOIN org_unidades unidades_org_ 
            ON permisos_lectura_.id_lector_global = unidades_org_.identificador_unico
    WHERE (LOWER(doc_gestion_principal_.titulo_documento) LIKE '%finanzas_reporte_2023%') 
      AND (unidades_org_.identificador_unico IN ('a1b2c3d4e5f6...', 'f6e5d4c3b2a1...', '112233445566...'))
) 
ORDER BY doc_gestion_principal_.timestamp_creacion DESC, 
         doc_gestion_principal_.identificador_unico DESC 
LIMIT 15;

El plan de ejecución original mostraba un costo elevado en la operación de recuperación de bloques (BLKUP), consumiendo cerca del 80% del tiempo total. Las lecturas lógicas superaban los 24 millones.

Para mitigar esto, se aplicaron hints para forzarel uso de índices y cambiar la estrategia de join:

/*+ enable_index_filter(1) use_nl_with_index(permisos_lectura_,IDX_DOC_MAIN_01) */

Tras la ajuste, el tiempo de ejecución descendió a 2.7 segundos. El nuevo plan eliminó la mayoría de las lecturas físicas y redujo las lecturas lógicas a menos de 1 millón.

3. Caso de Optimización: Variación con Diferentes IDs

En un escenario similar, pero con una lista distinta de identificadores de organización, la estrategia de hints se mantuvo efectiva. La consulta original uitlizaba un patrón LIKE genérico.

SELECT /*+ enable_index_filter(1) use_nl_with_index(permisos_lectura_,IDX_DOC_MAIN_01) */ 
       doc_gestion_principal_.identificador_unico AS id_1_
FROM doc_gestion_principal doc_gestion_principal_ 
WHERE doc_gestion_principal_.identificador_unico IN (
    SELECT doc_gestion_principal_.identificador_unico 
    FROM doc_gestion_principal doc_gestion_principal_ 
    INNER JOIN doc_permisos_lectura permisos_lectura_ 
            ON doc_gestion_principal_.identificador_unico = permisos_lectura_.id_documento_ref
    INNER JOIN org_unidades unidades_org_ 
            ON permisos_lectura_.id_lector_global = unidades_org_.identificador_unico
    WHERE (LOWER(doc_gestion_principal_.titulo_documento) LIKE '%contrato_marco%') 
      AND (unidades_org_.identificador_unico IN ('x9y8z7w6v5u4...', 't3s2r1q0p9o8...'))
);

El plan optimizado mostró una reducción significativa en los pasos de filtrado (SLCT2) y escaneo de índices (SSEK2), validando la eficacia del hint use_nl_with_index cuando la selectividad del LIKE es alta.

4. Caso de Optimización: Ranngo de Fechas y Texto

Al introducir condiciones de rango de fechas junto con el filtrado de texto, el optimizador tendía a elegir Hash Join, lo cual no era óptimo para este volumen de datos.

SELECT /*+ enable_index_filter(1) enable_hash_join(0) */ 
       doc_gestion_principal_.identificador_unico AS id_1_
FROM doc_gestion_principal doc_gestion_principal_ 
WHERE doc_gestion_principal_.identificador_unico IN (
    SELECT doc_gestion_principal_.identificador_unico 
    FROM doc_gestion_principal doc_gestion_principal_ 
    INNER JOIN doc_permisos_lectura permisos_lectura_ 
            ON doc_gestion_principal_.identificador_unico = permisos_lectura_.id_documento_ref
    INNER JOIN org_unidades unidades_org_ 
            ON permisos_lectura_.id_lector_global = unidades_org_.identificador_unico
    WHERE (doc_gestion_principal_.timestamp_creacion BETWEEN '2023-01-01 00:00:00' AND '2024-12-31 23:59:59') 
      AND (LOWER(doc_gestion_principal_.titulo_documento) LIKE '%auditoria_interna%') 
      AND (unidades_org_.identificador_unico IN ('id_org_01', 'id_org_02', 'id_org_03'))
);

Desactivar el Hash Join (enable_hash_join(0)) forzó al motor a utilizar Nested Loop, aprovechando mejor los índices de fecha y texto. El tiempo de ejecución se estabilizó alrededor de 2 segundos.

5. Caso de Optimización: Solo Fechas e IDs

Cuando la condición LIKE se elimina y solo permanecen los rangos de fecha y la lista de IDs, la estrategia de semi-join del optimizador requería ajuste.

SELECT /*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) */ 
       doc_gestion_principal_.identificador_unico AS id_1_
FROM doc_gestion_principal doc_gestion_principal_ 
WHERE doc_gestion_principal_.identificador_unico IN (
    SELECT doc_gestion_principal_.identificador_unico 
    FROM doc_gestion_principal doc_gestion_principal_ 
    INNER JOIN doc_permisos_lectura permisos_lectura_ 
            ON doc_gestion_principal_.identificador_unico = permisos_lectura_.id_documento_ref
    INNER JOIN org_unidades unidades_org_ 
            ON permisos_lectura_.id_lector_global = unidades_org_.identificador_unico
    WHERE (doc_gestion_principal_.timestamp_creacion BETWEEN '2023-01-01 00:00:00' AND '2024-12-31 23:59:59') 
      AND (unidades_org_.identificador_unico IN ('id_org_a', 'id_org_b', 'id_org_c'))
);

Los hints no_semi_gen_cross y ADAPTIVE_NPLN_FLAG permitieron controlar la generación del plan semi-join y el ordan de las tabas a la derecha, reduciendo el tiempo a aproximadamente 3.3 segundos.

6. Implementación de Hints Persistentes

Para aplicar estas optimizaciones sin modificar el código de la aplicación, se pueden inyectar hints directamente en el diccionario de la base de datos.

-- Inyección de hint para caso mixto
CALL sf_inject_hint('', 'no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3)', 'HINT_CONFIG_01', NULL, TRUE, TRUE);

-- Verificación de estadísticas de tablas involucradas
SELECT s.last_gathered, o.owner, o.object_name, b.column_name, s.n_distinct, s.n_null
FROM sysstats s 
INNER JOIN dba_objects o ON s.id = o.object_id
INNER JOIN dba_tab_columns b ON o.owner = b.owner AND o.object_name = b.table_name AND s.colid + 1 = b.column_id 
WHERE o.owner = 'ESQUEMA_PROD' 
AND o.object_name IN ('DOC_GESTION_PRINCIPAL', 'DOC_PERMISOS_LECTURA', 'ORG_UNIDADES')
AND o.object_type = 'TABLE'
ORDER BY b.column_id;

-- Actualización de configuración de hints
CALL sf_inject_hint('FROM doc_gestion_principal ... WHERE ... titulo_documento LIKE ?', 'enable_index_filter(1) use_nl_with_index(permisos_lectura_,IDX_DOC_MAIN_01)', 'HINT_CONFIG_02', NULL, TRUE, TRUE);

-- Desactivación de hints si es necesario
CALL SF_ALTER_HINT('HINT_CONFIG_02', 'STATUS', 'DISABLED');

-- Eliminación permanente
CALL SF_DEINJECT_HINT('HINT_CONFIG_02');