Comparar planes de ejecución en el AWR
y además colorear consultas y seguirlas en el histórico del repositorio
A partir de oracle 19c, se ha introcido una nueva funcionalidad en el paquete DBMS_XPLAN, es la posibilidad de comparar planes de ejecución desde distintas fuentes.
En mi caso, el cliente tiene licencia de tuning & diagnostic pack, por lo que suelo usar frecuentemente las vistas del awr para ver la evolución de los planes de ejecución o directamente localizar los planes que cambien
Así teniendo el sql_id y el plan_hash_value de cada plan, el procedimiento DBMS_XPLAN.COMPARE_PLANS nos los compara y nos saca el informe.
https://github.com/sysassysdba/database-scripts/blob/main/sql_compare_plans_awr.sql
--sql_compare_plans_awr.sql
--sysassysdba@gmail.com
set echo off
set verify off
set feedback off
set linesize 300
SET PAGESIZE 500
UNDEFINE PLHASHCOMP
UNDEFINE PLHASHREF
UNDEFINE SQL_ID
ACCEPT SQLID PROMPT 'SQLID TO compare --> '
ACCEPT PLHASHREF PROMPT 'PLAN_HASH_VALUE OF REFERENCCE PLAN --> '
ACCEPT PLHASHCOMP PROMPT 'PLAN_HASH_VALUE OF COMPARE PLAN --> '
spool LOGS/compare_sql_'&sqlid'hashs'&PLHASHREF'vs'&PLHASHCOMP'.txt
VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => awr_object('&sqlid', null, null, &PLHASHREF),
compare_plan_list => plan_object_list(awr_object('&sqlid', null, null, &PLHASHCOMP)),
type => 'TEXT',
level => 'ALL',
section => 'ALL');
END;
/
set long 100000
COLUMN report FORMAT a250
SELECT :v_rep REPORT FROM DUAL;
spool off
UNDEFINE PLHASHCOMP
UNDEFINE PLHASHREF
UNDEFINE SQL_ID
set feedback on
set verify on
set echo on
Podemos ver la evolución de una query tirando de los snap del awr y además ver sus planes de ejecución.
Vamos a partir de un sqlid conocido, al que le he aplicado un sql tuning y como recomendaciones, se nos sugirió la creación de unos índices y en una segunda tuning task nos sugirió aplicar un profile.
Consultando la DBA_HIST_ACTIVE_SESS_HISTORY y la DBA_HIST_SQLSTAT vemos los distintos planes que ha tenido para un día esa sqlid, así como si usa un profile o no:
(https://github.com/sysassysdba/database-scripts/blob/main/chk_sql_history_daily.sql)
Con este script suelo sacar las ejecuciones por snap para un día determinado de un sqlid
Como hemos dicho, vamos a comparar los planes de ejecución que tenemos para ese sqlid:
Nos salen en el mismo informe los dos planes y sus diferencias.
Vamos a comprobar tambien el plan original, con el que usa el sql profile.
En este caso la mejora fué considerable.
Bonus Track Las queries que se registran en el awr siempre son limitadas, por lo que corremos el riesgo de que nuestra consulta no quede reflejada en los snaps de awr si deja de estar entre las que mas recursos consumen, eso siempre es bueno para performance general, pero a nosotros si nos interesa seguirla por unos días , para ello tenemos la opción de “colorear” el sqlid:
execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>'0n9y53v8vgyvj');
Eso nos asegura que podremos seguir nuestra consulta aún cuando no se ejecute lo suficiente como para quedar registrada en los snap del awr…veamos como despues de colorearla, la hemos podido seguir aun cuando su consumo de recursos por snap era muy bajo:
Conclusión
Suelo usar estos tres scripts en mi trabajo diario (en bbdd con licencia tuning & diagnostic pack) para perseguir queries costosas, tanto para ver como se comportan en un mismo día, como para ver como han sido sus planes de ejecución.
Los scripts usados son:
Para ver cuantas sql tenemos coloreadas:
https://github.com/sysassysdba/database-scripts/blob/main/chk_colored_sql.sql
Para ver el histórico de un sqlid en todo el intervalo del awr:
https://github.com/sysassysdba/database-scripts/blob/main/chk_sql_history_all.sql
Para ver un sqlid solo para un día:
https://github.com/sysassysdba/database-scripts/blob/main/chk_sql_history_daily.sql
Para comparar dos planes de un mismo sql_id en el awr:
https://github.com/sysassysdba/database-scripts/blob/main/sql_compare_plans_awr.sql
