SQL Plan Management (SPM) é uma feature do Oracle para controle dos planos de execução usados pelo otimizador e atua principalmente para evitar problemas com regressão de desempenho de instruções SQL. Se você ainda não conhece, recomendo a leitura do artigo do “Criando SQL Plan baselines utilizando o SQL*Plus” publicado por Alex Zaballa.
Recentemente precisei implementar a solução e uma das necessidades era obter o PLAN_HASH_VALUE para correlacionar os planos de execução existentes no SPM com outras ferramentas utilizadas no ambiente, e a view DBA_SQL_PLAN_BASELINES não ajuda muito com isso (até versão 19c). Também, cada SQL só é catalogado no SPM quando ele é executado após habilitar a feature, mas ainda é possível importar um SQL/Plano manualmente caso o mesmo ainda não tenha sido executado/utilizado desde que o parâmetro “optimizer_capture_sql_plan_baselines” tenha sido alterado para TRUE.
Neste post disponibilizo alguns scripts que precisei para conseguir esses objetivos: Relacionar SPM PLAN_NAME vs PLAN_HASH_VALUE disponível no Cursor Cache e importar um plano manualmente do histórico do AWR.
Obter PLAN_HASH_VALUE para PLAN_NAME
Usamos a function DISPLAY_SQL_PLAN_BASELINE da package DBMS_XPLAN para obter o plano de execução completo para uma determinada combinaçao de SQL_HANDLE + PLAN_NAME da view DBA_SQL_PLAN_BASELINES.
Síntaxe:
SELECT * FROM DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('<SQL HANDLE>','<PLAN_NAME>');
A query abaixo obtém o plano completo com a DBMS_XPLAN e extrai somente o valor do atributo “Plan hash value”.
Listando todos os planos no SPM de um SQL_ID específico, com os respectivos PLAN_HASH_VALUE:
SELECT SIGNATURE,
SQL_HANDLE,
PLAN_NAME,
ENABLED,
ACCEPTED,
ADAPTIVE
,(select replace(plan_table_output,'Plan hash value: ','')
from dbms_xplan.display_sql_plan_baseline('' || SQL_HANDLE || '','' || PLAN_NAME || '')
where plan_table_output like '%Plan hash value%'
) as PLAN_HASH_VALUE
-- comando para obter o plano
,'select * from dbms_xplan.display_sql_plan_baseline(''' || SQL_HANDLE || ''',''' || PLAN_NAME || ''');' as get_full_plan
FROM DBA_SQL_PLAN_BASELINES B
where signature in (select exact_matching_signature from gv$sql where sql_id='&sqlid')
order by signature;
Listando todos os planos de um SQL_ID específico, com os respectivos PLAN_HASH_VALUE e tempo médio de execução (sub query do script coe.sql):
SET VERIFY OFF
SET LINESIZE 400
SET PAGESIZE 500
COL SIGNATURE FORMAT 9999999999999999999999
COL SQL_HANDLE FORMAT A20
COL PLAN_NAME FORMAT A30
COL ORIGIN FORMAT A15 TRUNC
COL CREATED FORMAT A20
COL MODIFIED FORMAT A20
COL ENABLED FORMAT A8
COL ACCEPTED FORMAT A7
COL ADAPTIVE FORMAT A8
COL PLAN_HASH_VALUE FORMAT A15
WITH SPM AS (
SELECT SIGNATURE,
SQL_HANDLE,
PLAN_NAME,
ORIGIN,
TO_CHAR(CREATED,'DD/MM/YYYY HH24:MI:SS') CREATED,
TO_CHAR(LAST_MODIFIED,'DD/MM/YYYY HH24:MI:SS') AS MODIFIED,
ENABLED,
ACCEPTED,
ADAPTIVE
,(select replace(plan_table_output,'Plan hash value: ','')
from dbms_xplan.display_sql_plan_baseline('' || SQL_HANDLE || '','' || PLAN_NAME || '')
where plan_table_output like '%Plan hash value%'
) as PLAN_HASH_VALUE
FROM DBA_SQL_PLAN_BASELINES
WHERE signature in (select exact_matching_signature from gv$sql where sql_id='&&sql_id')
)
SELECT P.* , T.AVG_ET_SECS
FROM SPM P,
( -- sub consulta do coe.sql para obter tempo medio de execucao de cada plano
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value, SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY plan_hash_value ),
a AS (
SELECT plan_hash_value,SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY plan_hash_value )
SELECT p.plan_hash_value, ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 2) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
) T
WHERE P.PLAN_HASH_VALUE = T.PLAN_HASH_VALUE(+);
undefine sql_id;
Como Importar Um Plano do Histórico AWR
Consulte os planos existentes no histórico do AWR para o SQL_ID específico,
A query abaixo lista os PLAN_HASH_VALUE com o tempo médio de execução e o número do maior snapshot do AWR:
select plan_hash_value, max(snap_id)-1 awr_snap, round(sum(elapsed_time_total)/sum(executions_total)/1e6,2) avg_et_secs
from dba_hist_sqlstat
where sql_id='&sql_id'
and executions_total > 0
group by plan_hash_value;
O script a seguir importa o plano para o SPM Baseline:
DECLARE
vSnapMin number := 94263; -- valor da coluna "awr_snap" na query anterior
vSQL_ID varchar2(200) := '57hkw363cmjbs';
vHashPlan number := 483801899; -- hash_plan a ser importado, conforme query anterior
vLoad varchar2(200);
BEGIN
vLoad := DBMS_SPM.LOAD_PLANS_FROM_AWR(
BEGIN_SNAP => vSnapMin,
END_SNAP => vSnapMin+1,
ENABLED => 'NO', --> alterar para YES se quiser habilitar o plano automaticamente
BASIC_FILTER => 'sql_id=''' || vSQL_ID || ''' and plan_hash_value=''' || vHashPlan || ''' '
);
END;
/
Referências:
Pingback: 3 Abordagens Diferentes Para Fixar Planos de Execução de Comandos SQL no Oracle Database – Blog do Dibiei