DBMS_SPM

Mostrando las entradas con la etiqueta DBMS_SPM. Mostrar todas las entradas
Mostrando las entradas con la etiqueta DBMS_SPM. Mostrar todas las entradas

martes, 27 de octubre de 2026

Cómo fijar un SQL Plan Baseline basado en el historial de AWR en Oracle


Cuando una sentencia SQL experimenta degradación de rendimiento en producción, una de las prácticas más efectivas es consultar el histórico del AWR para identificar el plan de ejecución más eficiente y fijarlo mediante SQL Plan Baselines (DBMS_SPM).

A continuación, te comparto el procedimiento paso a paso, desde la detección de las sentencias problemáticas hasta la aplicación, purga selectiva en memoria y validación del plan óptimo.

Antes de auditar un plan específico, necesitamos saber qué sql_id están generando problemas de rendimiento en la base de datos (por ejemplo, acumulando el mayor tiempo de respuesta en los últimos días).

Podemos consultar el AWR histórico con la siguiente sentencia:

SELECT 
  s.sql_id, ROUND(SUM(s.elapsed_time_total)/1000000, 2) AS total_elapsed_sec, 
  SUM(s.executions_total) AS total_execs, 
  ROUND(SUM(s.elapsed_time_total) / DECODE(SUM(s.executions_total), 0, 1, 
  SUM(s.executions_total)) / 1000, 2) AS avg_elapsed_ms, 
  SUM(s.buffer_gets_total) AS total_buffer_gets 
FROM
  dba_hist_sqlstat s, dba_hist_snapshot snap
WHERE s.snap_id = snap.snap_id
  AND snap.begin_interval_time >= SYSDATE - 3 -- Analiza los últimos 3 días
  GROUP BY s.sql_id ORDER BY total_elapsed_sec DESC
  FETCH FIRST 10 ROWS ONLY;
Con este listado de los 10 SQL con mayor tiempo acumulado, seleccionamos aquel sql_id que esté presentando desviaciones o un rendimiento subóptimo (por ejemplo, el fd4b9db6ju2ck).

Con el sql_id obtenemos todos los plan_hash_value cruzando las vistas DBA_HIST_SQLSTAT y DBA_HIST_SNAPSHOT:
SELECT 
    a.plan_hash_value,
    COUNT(DISTINCT a.snap_id) AS total_snapshots_visto,
    SUM(a.executions_total) AS total_ejecuciones,
    ROUND(SUM(a.elapsed_time_total) / DECODE(SUM(a.executions_total), 0, 1, 
    SUM(a.executions_total)) / 1000, 2) AS avg_tiempo_ms,
    MIN(b.begin_interval_time) AS primera_vez_visto,
    MAX(b.begin_interval_time) AS ultima_vez_visto
FROM 
    dba_hist_sqlstat a,
    dba_hist_snapshot b
WHERE 
    a.sql_id = 'fd4b9db6ju2ck'
    AND a.snap_id = b.snap_id
GROUP BY 
    a.plan_hash_value
ORDER BY 
    avg_tiempo_ms ASC;


Una vez obtenido el plan_hash_value y el sql_id, debemos auditar cómo se ha comportado la sentencia a través del tiempo, cruzando las vistas DBA_HIST_SQLSTAT y DBA_HIST_SNAPSHOT para identificar con precisión el rango de snap_id y el PLAN_HASH_VALUE eficiente:

SELECT  
    a.snap_id,
    b.begin_interval_time,
    a.plan_hash_value,
    a.executions_total AS ejecuciones,
    ROUND(a.elapsed_time_total / DECODE(a.executions_total, 0, 1, a.executions_total) / 1000, 2) AS avg_tiempo_ms
FROM  
    dba_hist_sqlstat a,
    dba_hist_snapshot b
WHERE  
    a.sql_id = 'fd4b9db6ju2ck'
    AND a.PLAN_HASH_VALUE=3022309352
    AND a.snap_id = b.snap_id
ORDER BY 
    b.begin_interval_time DESC;


Anota el rango exacto de snapshots (snap_id inicial y final) donde la sentencia ejecutó con el menor tiempo de respuesta y el plan_hash_value asociado. Esto asegura que cargues el plan óptimo correspondiente a esa ventana específica y evites falsos positivos.

Una vez localizados los límites en el repositorio histórico, cargamos el plan directamente a la caché de SQL como un Baseline delimitándolo con begin_snap y end_snap:

DECLARE
  v_plans_loaded PLS_INTEGER;
BEGIN
  v_plans_loaded := DBMS_SPM.load_plans_from_awr(
    begin_snap   => 82233,
    end_snap     => 82235,
    basic_filter => 'sql_id = ''fd4b9db6ju2ck'' AND plan_hash_value = 3022309352'
  );
  DBMS_OUTPUT.PUT_LINE('Planes cargados exitosamente: ' || v_plans_loaded);
END;
/





Para confirmar que el plan quedó registrado y habilitado correctamente sin tropezar con errores de vistas inexistentes, validamos mediante la firma numérica (signature):

SELECT 
    b.sql_handle, 
    b.plan_name, 
    b.enabled, 
    b.accepted, 
    b.fixed, 
    b.origin,
    b.last_executed
FROM 
    dba_sql_plan_baselines b, 
    v$sql s 
WHERE 
    s.exact_matching_signature = b.signature
    AND s.sql_id = 'fd4b9db6ju2ck';



Asegúrate de que las columnas ENABLED y ACCEPTED estén en YES. 

Si deseas fijar estrictamente el plan para que el CBO no fluctúe ante cambios futuros de estadísticas, ejecuta:

DECLARE
  v_fixed PLS_INTEGER;
BEGIN
  v_fixed := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_9447000dc483f1a8',
    plan_name       => 'SQL_PLAN_98js01r287wd82fd5eb16',
    attribute_name  => 'fixed',
    attribute_value => 'YES'
  );
END;
/


Para forzar a la instancia a reconocer de inmediato el nuevo baseline sin esperar a que la sentencia sea liberada por el mecanismo LRU de la memoria, purgamos el cursor actual:

SELECT address, hash_value, plan_hash_value 
FROM v$sql 
WHERE sql_id = 'fd4b9db6ju2ck';


Luego ejecutamos la purga:
EXEC DBMS_SHARED_POOL.purge(('00000000C753E9C8,3441232274'), 'C');

Monitorea las siguientes ejecuciones mediante V$SQL para comprobar la mejora en el rendimiento:

SELECT 
    sql_id, 
    plan_hash_value, 
    executions, 
    elapsed_time/1000 AS elapsed_ms, 
    buffer_gets 
FROM 
    v$sql 
WHERE 
    sql_id = 'fd4b9db6ju2ck';

NOTA: Si por cambios en la volumetría de datos el plan forzado no se comporta como esperabas, realiza un rollback inmediato:

DESACTIVAR:
DECLARE
  v_unfixed PLS_INTEGER;
BEGIN
  v_unfixed := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_9447000dc483f1a8',
    plan_name       => 'SQL_PLAN_98js01r287wd82fd5eb16',
    attribute_name  => 'enabled',
    attribute_value => 'NO'
  );
END;
/
ELIMINAR:
DECLARE
  v_plans_dropped PLS_INTEGER;
BEGIN
  v_plans_dropped := DBMS_SPM.drop_sql_plan_baseline(
    sql_handle => 'SQL_9447000dc483f1a8',
    plan_name  => 'SQL_PLAN_98js01r287wd82fd5eb16'
  );
  
  DBMS_OUTPUT.PUT_LINE('Planes eliminados (Rollback ejecutado): ' || v_plans_dropped);
END;
/

En resumen: usar el historial de la base de datos para obligarla a trabajar siempre con el mejor plan de ejecución es la manera más inteligente de solucionar consultas lentas. Te permite evitar sorpresas en producción y mantener todo funcionando rápido, sin necesidad de cambiar ni una sola línea de código.

Saludos.