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;
sql_id que esté presentando desviaciones o un rendimiento subóptimo (por ejemplo, el fd4b9db6ju2ck).DBA_HIST_SQLSTAT y DBA_HIST_SNAPSHOT:SELECTa.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_vistoFROMdba_hist_sqlstat a,dba_hist_snapshot bWHEREa.sql_id = 'fd4b9db6ju2ck'AND a.snap_id = b.snap_idGROUP BYa.plan_hash_valueORDER BYavg_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:
Para confirmar que el plan quedó registrado y habilitado correctamente sin tropezar con errores de vistas inexistentes, validamos mediante la firma numérica (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;
/
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';
ENABLED y ACCEPTED estén en YES. DECLAREv_fixed PLS_INTEGER;BEGINv_fixed := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_9447000dc483f1a8',plan_name => 'SQL_PLAN_98js01r287wd82fd5eb16',attribute_name => 'fixed',attribute_value => 'YES');END;/
SELECT address, hash_value, plan_hash_valueFROM v$sqlWHERE sql_id = 'fd4b9db6ju2ck';
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';
DECLAREv_unfixed PLS_INTEGER;BEGINv_unfixed := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_9447000dc483f1a8',plan_name => 'SQL_PLAN_98js01r287wd82fd5eb16',attribute_name => 'enabled',attribute_value => 'NO');END;/
DECLAREv_plans_dropped PLS_INTEGER;BEGINv_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;/





