Atuando em projetos de migração de banco de dados Oracle, depois de vencer todos os desafios que determinam como executar a migração em si, o grande “chefão” final deste jogo acaba sendo as mudanças de comportamento nos planos de execução de alguns comandos SQL. Dependendo do cenário, é normal e até esperado ter algumas mudanças, que podem ser para melhor ou para pior, mas as mudanças para pior são as que chamam mais atenção.
Considerando um cenário em que você já analisou a situação e idenficou uma regressão de performance devido ao otimizador ter escolhido (ou gerado) um plano com tempo de execução maior que o original, este post apresenta 3 formas diferentes de induzir o banco de dados a utilizar um plano de execução específico.
Resumo das Abordagens
Os recursos apresentados não limitam-se a fixar plano de execução, no entanto, este post visa apresentar como usar cada um deles para este fim. As capacidades de cada recurso vão muito além do que serão apresentadas aqui e cada uma deles requer um estudo indvidual mais detalhado para compreender todo o seu potencial.
SQL Profile (Com o script COE)
Conceitualmente, a função de um SQL Profile não é fixar plano de execução, mas sim fornecer informações complementares para ajudar o otimizador a escolher o plano mais otimizado. No entanto, o script COE cria um Profile usando todos os Outlines associados com um Plan Hash Value específico, o que na prática acaba levando o otimizador a usar o plano em questão.
Até a versão 12cR1, é a solução mais rápida e fácil de usar em momentos de emergência quando se precisa pegar um plano do AWR (se já tiver o script a disposição). A partir do 12cR2, o SPM tem uma opção mais simples.
Requer a Option Oracle Tuning Pack (o que também requer Oracle Diagnostics Pack).
SQL Plan Management (SPM)
Conceitualmemte a função do SPM é funcionar como um controle de planos de execução aceitáveis. O comando SQL pode ter múltiplos planos, mas o otimizador só utiliza aqueles que são permitidos pelo SPM. Na prática, ter somente um Baseline marcado como aceito e ativado no SPM para o SQL, forçará o otimizador a usar sempre o mesmo plano.
É uma solução mais rica e elegante do que SQL Profile para controlar planos de execução, já que tem esse objetivo em sua definição.
Essa feature faz parte do Enterprise Edition, não tendo necessidade de nenhum custo extra com options. A partir da versão 18c, está disponível para Standard Edition com a limitação de 1 Baseline por SQL (o que já é suficiente para o objetivo de fixar um plano específico).
SQL Patch
Conceitualmente a função do SQL Patch é anexar HINTs para um comando SQL sem alterar o seu código, isso o torna a solução com maior flexibilidade para induzir o otimizador a fazer algo específico com o comando SQL (ou tentar). Na prática, vamos usá-lo para anexar todos os Outlines de um Plan Hash Value específico, o que fará com que o otimizador use sempre o mesmo plano de execução.
Do ponto de vista de quem quer apenas fixar alguns planos, é uma boa alternativa quando não se pode usar SQL Profile devido as questões de licenciamento, ou quando o ambiente tem algum BUG relacionado ao SPM. Do ponto de vista de quem quer tentar qualquer coisa diferente no SQL, essa feature proporciona as melhores aventuras.
Também faz parte do Enterprise Edition e não requer licenciamento específico.
1) SQL Profile Criado Manualmente Com Script COE
Esta opção é a mais simples, direta e efetiva em situações de emergência.
1) Faça download do SQLT no My Oracle Support:
All About the SQLT Diagnostic Tool (Doc ID 215187.1)
2) Extraia o conteúdo do arquivo .zip, acesse o diretório “utl” e encontre o script coe_xfr_sql_profile.sql:
3) Copie o script para o servidor de banco de banco de dados ou para o local de onde você costuma se conectar ao BD via SQLPLUS.
4) Execute o script no SQLPLUS passando o SQL_ID e Plan Hash Value desejado (se já tiver ele anotado):
SQL> @coe fddt8cykhj6zn 2612496285
DICA: Normalmente renomeio o arquivo para “coe.sql” para simplificar a chamada no SQLPLUS.
Se ainda não tiver analisado qual o melhor Plan Hash Value e quiser escolher um pelo menor tempo apresentado no histórico (em memória + AWR), pode executar o script informando somente o SQL_ID:
SQL> @coe 29a937h82ptgb
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
__________________ ______________
1323445484 0.077
251589227 0.24
3030005328 0.323
Parameter 2:
PLAN_HASH_VALUE (required)
O script lista todos os Plan Hash Value disponíveis ordenados pelo menor tempo médio de execução.
Digite (copie e cole) o número do Plan Hash Value como o segundo argumento para o script continuar a execução:
Enter value for 2: 1323445484 Values passed: ~~~~~~~~~~~~~ SQL_ID : "29a937h82ptgb" PLAN_HASH_VALUE: "1323445484" Execute coe_xfr_sql_profile_29a937h82ptgb_1323445484.sql on TARGET system in order to create a custom SQL Profile with plan 1323445484 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed.
5) Até este ponto o script gerou os comandos necessários para criar um novo SQL Profile no banco de dados. Para efetivamente criar o profile no banco, execute o script recém gerado como coe_xfr_sql_profile_<SQL ID>_<PLAN HASH VALUE>.sql.
SQL> @coe_xfr_sql_profile_29a937h82ptgb_1323445484.sql
O script deve fazer jus ao nome de Carlos Sierra, gerar um output consideravelmene grande e se tudo ocorrer bem, o resultado deve ser algo similar a este:
PL/SQL procedure successfully completed. SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE ---------- 12486396382186351909 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_29a937h82ptgb_1323445484 completed
Você pode consultar o SQL Profile na view DBA_SQL_PROFILES:
SET lin 1000
COL NAME FORMAT A40
COL CREATED FORMAT a20
COL STATUS FORMAT A10
COL TYPE FORMAT A10
COL SIGNATURE FORMAT 9999999999999999999999999
SELECT NAME,
to_char(CREATED,'dd/mm/yyyy hh24:mi:ss') as CREATED,
STATUS,
TYPE,
SIGNATURE
FROM DBA_SQL_PROFILES B
WHERE SIGNATURE = 12486396382186351909
ORDER BY CREATED;
NAME CREATED STATUS TYPE SIGNATURE
---------------------------------------- -------------------- ---------- ---------- --------------------------
coe_29a937h82ptgb_1323445484 17/09/2022 18:34:51 ENABLED MANUAL 12486396382186351909
Se precisar dropar ou desabilitar o SQL Profile, pode usar um dos respectivos comandos:
-- dropando profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_29a937h82ptgb_1323445484');
-- desabilitando profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('coe_29a937h82ptgb_1323445484','STATUS','DISABLED');
-- habilitando profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('coe_29a937h82ptgb_1323445484','STATUS','ENABLED');
2) Criando Baseline no SQL Plan Management (SPM)
Considerações
Essa é uma feature que funciona muito bem quando não apresenta BUG. Antes de usá-la para algum SQL específico, recomendo avaliar se já ela já está sendo utilzada no banco em questão.
Para verificar a existência de SPM Baselines:
-- conta baselines existentes
SELECT COUNT(*) FROM DBA_SQL_PLAN_BASELINES;
-- conta baselines que realmente influenciam o otimizador
SELECT COUNT(*) FROM DBA_SQL_PLAN_BASELINES WHERE ENABLED = 'YES' AND ACCEPTED = 'YES';
Para listar comandos SQL no Cursor Cache que utilizam SPM Baseline:
SELECT SQL_ID, SQL_PLAN_BASELINE
FROM V$SQL
WHERE SQL_PLAN_BASELINE IS NOT NULL;
Se for usar em larga escala, recomendo avaliar uma das seguintes notas do MOS (de acordo a versão do banco):
Things to Consider to Avoid SQL Plan Management (SPM) Related Problems on 19c (Doc ID 2774029.1)
Things to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management (SPM) (Doc ID 2035898.1)
Things to Consider for 11.2.0.4 to Avoid Problems with SQL Plan Management (SPM) (Doc ID 2034706.1)
Muito dos possíveis problemas descritos nas notas acima podem ser evitados tendo o último RU / PSU aplicado no ambiente.
Confirme que o parâmetro optimizer_use_sql_plan_baselines está com valor TRUE (esse é o valor default):
SQL> show parameter optimizer_use_sql_plan_baselines NAME TYPE VALUE -------------------------------- ------- ----- optimizer_use_sql_plan_baselines boolean TRUE
OBS: Este parâmetro determina se o otimizador irá considerar o SPM Baseline ao determinar o plano de execução do SQL. Por outro lado, o parâmetro optimizer_capture_sql_plan_baselines determina se o banco vai criar Baselines automaticamente para cada novo SQL conforme são executados (este é FALSE por padrão).
Mantendo a configuração padrão tem o resultado menos intrusivo, onde somente as queries com Baselines criados manualmente serão afetadas.
Temos 3 opções para criar um Baseline manualmente no SPM:
- A) Importando do Cursor Cache (LOAD_PLANS_FROM_CURSOR_CACHE)
- B) Importando de um SQL Tuning Set (LOAD_PLANS_FROM_SQLSET)
- C) Importando do AWR (LOAD_PLANS_FROM_AWR) –> new feature do 12cR12
Opção A) Baseline Importando Plano do Cursor Cache
Neste exemplo estamos criando um Baseline para o SQL_ID bd5c8fvmh78c6 usando o Plan Hash Value 3735151949. As informações do plano são importadas diretamente do Cursor Cache:
SET SERVEROUTPUT ON
DECLARE
vLoad varchar2(200);
BEGIN
vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'bd5c8fvmh78c6', PLAN_HASH_VALUE => 3735151949);
DBMS_OUTPUT.put_line('Number of plans loaded: ' || vLoad);
END;
/
Opção B) Baseline Importando Plano de um SQL Tuning Set (STS)
SQL Tuning Set (STS) é um objeto de banco de dados que armazena uma lista de comando SQL junto com seus planos de execução. Um STS pode ser usado como fonte para outras features do Oracle Database, assim como usaremos com o SPM.
Podemos carregar um STS de diversas formas e com uma grande variedade de filtros. Neste post veremos dois exemplos simples, um pegando os comandos SQL do Cursor Cache e o outro pegando do AWR. Também temos uma boa flexibilidade de filtro sobre quais comandos serão importados, podendo ser um conjunto limitado ou até mesmo todos os comandos disponíveis.
1) Crie o SQL Tuning Set com um nome e descrição amigável:
BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'STS_PRE_MIGRACAO',
description => 'Plano dos SQL antes da Migracao'
);
END;
/
2) Importe os comandos SQL para o SQL Tuning Set
a) Exemplo pegando do AWR
-- identifica o intervalo de snapshots pelo período
select min(snap_id), max(snap_id)
from dba_hist_snapshot
where begin_interval_time
between to_date('01/09/2022 00:00:00','dd/mm/yyyy hh24:mi:ss')
and to_date('16/09/2022 23:59:59','dd/mm/yyyy hh24:mi:ss');
MIN(SNAP_ID) MAX(SNAP_ID)
_______________ _______________
194838 197141
-- importa os SQL do AWR passando o intervalo de snapshots
DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p)
from table(dbms_sqltune.select_workload_repository
(
begin_snap => 194838,
end_snap => 197141,
basic_filter => 'parsing_schema_name <> ''SYS'' '
)) p;
dbms_sqltune.load_sqlset('STS_PRE_MIGRACAO', cur);
close cur;
END;
/
b) Exemplo com Cursor Cache
-- pegando todos os comandos disponíveis no Cursor Cache que não foram executados pelo SYS
declare
sts_ref_cur sys_refcursor;
begin
open sts_ref_cur for SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P;
DBMS_SQLTUNE.LOAD_SQLSET('STS_PRE_MIGRACAO', sts_ref_cur);
end;
/
DICA: Você pode criar um SQL Tuning Set com todos os planos existentes no Cursor Cache antes de uma migração, depois usá-lo como uma fonte histórica para criar Baselines após o GOLIVE (se não puder usar AWR para este fim).
4) Crie o Baseline para um SQL_ID e Plan Hash Value específico, importando do STS:
set serveroutput on
declare
l_num_plans PLS_INTEGER;
begin
l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=> 'STS_PRE_MIGRACAO',sqlset_owner=>'SYS' ,basic_filter=>'sql_id=''fb3fw1hahd4qh'' and plan_hash_value=598595646');
DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/
Opção C) Baseline Importando Plano Direto do AWR (12cR2 +)
Cenário 1: Criar baseline para um SQL e plano específico.
1) Identifique os planos disponíveis no AWR, assim como o intervalo de Snapshots mais recente para cada um:
col plan_hash_value format 9999999999999999999999999
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='4acnfvacp187y'
and executions_total > 0
group by plan_hash_value;
PLAN_HASH_VALUE AWR_SNAP AVG_ET_SECS
-------------------------- ---------- -----------
980905172 187353 1199.96
201510187 197316 7530.96
60851287 194649 9460.51
2126558315 195150 67074.78
2) Importe o plano específico informando o intervalo de snapshots AWR:
DECLARE
vSnapMin number := 187353; -- valor da coluna "awr_snap" na query anterior
vSQL_ID varchar2(200) := '4acnfvacp187y';
vHashPlan number := 980905172; -- 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,
BASIC_FILTER => 'sql_id=''' || vSQL_ID || ''' and plan_hash_value=''' || vHashPlan || ''' '
);
DBMS_OUTPUT.put_line('Number of plans loaded: ' || vLoad);
END;
/
Cenário 2: Criar baseline para todos os comandos SQL e planos dentro de um intervalo de snaphosts AWR.
1) Identifique o intervalo de snaphots desejado pelo período:
select min(snap_id), max(snap_id)
from dba_hist_snapshot
where begin_interval_time
between to_date('01/09/2022 00:00:00','dd/mm/yyyy hh24:mi:ss')
and to_date('16/09/2022 23:59:59','dd/mm/yyyy hh24:mi:ss');
MIN(SNAP_ID) MAX(SNAP_ID)
_______________ _______________
194838 197141
2) Crie baseline para todos os planos existentes nesse intervalo de snapshots:
DECLARE
vLoad varchar2(200);
BEGIN
vLoad := DBMS_SPM.LOAD_PLANS_FROM_AWR(
BEGIN_SNAP => 194838,
END_SNAP => 197141
);
DBMS_OUTPUT.put_line('Number of plans loaded: ' || vLoad);
END;
/
No exemplo acima, todos os baselines são criados com a opção ENABLED=YES. Opcionalmente, você poderia importá-los como ENABLED=NO e depois marcá-los como ENABLED=YES individualmente, sob demanda.
Consultando Baselines criados (filtrando somente os que foram importados manualmente):
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 A18 TRUNC
COL ENABLED FORMAT A8
COL ACCEPTED FORMAT A8
COL FIXED FORMAT A7
SELECT SIGNATURE,
SQL_HANDLE,
PLAN_NAME,
ORIGIN,
CREATED,
ENABLED,
ACCEPTED,
FIXED
FROM DBA_SQL_PLAN_BASELINES B
where 1=1
and origin = 'MANUAL-LOAD'
order by created desc;
O post “Oracle SQL Plan Management (SPM): Query Para Relacionar PLAN_NAME com PLAN_HASH_VALUE” apresenta uma query para consultar os detalhes dos Baselines.
Desabilitando um Baseline (evita que o otimizador utilize esse Baseline).
Para desabilitar um baseline, alteramos o atributo “ENABLED” de “YES” para “NO”:
DECLARE
vChange VARCHAR2(200);
BEGIN
vChange:= DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle => 'SQL_c54a779b10b38510',
plan_name => 'SQL_PLAN_cakmrmc8b718h67ebd226',
attribute_name => 'ENABLED',
attribute_value => 'NO');
END;
/
Marcando um Baseline como FIXED.
Quando temos múltiplos Baselines marcados como ENABLED=YES para o mesmo SQL, o otimizador pode escolher qualquer um deles. Quando existe um Baseline marcado como FIXED, o otimizador irá considerar somente esses Baselines marcados como FIXED, mesmo que existam outros marcados como ENABLED.
Exsite uma exceção para essa regra se o plano do Baseline marcado como FIXED não puder ser reproduzido.
Um exemplo seria quando um índice referenciado no plano já foi dropado da tabela.
SET SERVEROUTPUT ON
DECLARE
vChange VARCHAR2(200);
BEGIN
vChange:= DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle => 'SQL_c54a779b10b38510',
plan_name => 'SQL_PLAN_cakmrmc8b718h54263bf4',
attribute_name => 'FIXED',
attribute_value => 'YES');
END;
/
DICA: Use essa opção se quiser “forçar” o otimizador a usar um Baseline específico sem dropar ou desabilitar os demais Baselines.
Deletando um Baseline
Se você criou diversos Baselines para o mesmo comando SQL e quer manter somente um ou alguns que realmente apresentaram o melhor resultado:
SET SERVEROUTPUT ON
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_c54a779b10b38510',
plan_name => 'SQL_PLAN_cakmrmc8b718h67ebd226'
);
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/
3) Criando SQL Patch
A função do SQL Patch é adicionar HINTS para um comando SQL, o que é muito útil quando testamos uma forma de execução mais otimizada para um comando SQL usando algum Hint (ex: Parallel, Index, etc), mas não é possível alterar o código dentro da aplicação.
Neste exemplo vou demosntrar como “forçar” um Plan Hash Value específico via SQL Patch. A estratégia consiste em obter o plano que queremos usar, extrair todos os Outlines e criar um SQL Patch incluindo todos os Outlines do plano que escolhemos. Observe que isso não é um requisito, é apenas uma forma de tentar garantir que o SQL Patch vai direcionar o otimizador para Plan Hash Value que estamos escolhendo.
1) Obtendo o plano de execução da query com todos os Outlines.
Opção 1) Pegando do Cursor Cache:
-- identifica os childs disponíveis em memória
set linesize 300
col sql_id format a20
col child_number format 999
select inst_id, sql_id, child_number, plan_hash_value
from gv$sql
where sql_id='bm2gp7sdc3pzp';
-- pegando o plano do child 0
SET LIN 1000
SET PAGESIZE 10000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'bm2gp7sdc3pzp', CURSOR_CHILD_NO=>0, FORMAT=>'ALL +OUTLINE'));
Opção 2) Pegando do AWR:
set lines 1000
set pages 4000
select * from table(dbms_xplan.display_awr(sql_id=>'bm2gp7sdc3pzp',format=>'ALLSTATS LAST +OUTLINE +NOTE +PEEKED_BINDS +PROJECTION +ALIAS +COST +BYTES +PARALLEL +PARTITION +REMOTE'));
2) Copie o conteúdo do Outline Data. Deve ser algo similar ao seguinte:
Plan hash value: 397881999
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| | | | |
| 1 | SORT ORDER BY | | 1 | 255 | 1 (100)| 00:00:01 | | | |
|* 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 90 | 0 (0)| | Q1,00 | P->S | QC (RAND) |
|* 4 | VIEW | GV$SESSION | | | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS | | 1 | 90 | 0 (0)| | Q1,00 | PCWP | |
| 6 | NESTED LOOPS | | 1 | 60 | 0 (0)| | Q1,00 | PCWP | |
|* 7 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0)| | Q1,00 | PCWP | |
|* 8 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 8 | 0 (0)| | Q1,00 | PCWP | |
|* 9 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 30 | 0 (0)| | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$2
7 - SEL$2 / S@SEL$2
8 - SEL$2 / W@SEL$2
9 - SEL$2 / E@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "S"@"SEL$1")
FULL(@"SEL$2" "S"@"SEL$2")
FULL(@"SEL$2" "W"@"SEL$2")
FULL(@"SEL$2" "E"@"SEL$2")
LEADING(@"SEL$2" "S"@"SEL$2" "W"@"SEL$2" "E"@"SEL$2")
USE_NL(@"SEL$2" "W"@"SEL$2")
USE_NL(@"SEL$2" "E"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("STATUS"='ACTIVE' AND "TYPE"='USER'))
4 - filter(("STATUS"='ACTIVE' AND "TYPE"='USER'))
7 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
DECODE(BITAND("S"."KSUSEFLG",19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?')='USER' AND
DECODE(BITAND("S"."KSUSEIDL",9),1,'ACTIVE',0,DECODE(BITAND("S"."KSUSEFLG",4096),0,'INACTIVE','CACHED'),'KILLED')='ACTI
VE'))
8 - filter("S"."INDX"="W"."KSLWTSID")
9 - filter("W"."KSLWTEVT"="E"."INDX")
3) Crie o SQL Patch passando todo o Outline Data copiado na etapa anterior.
Opção 1) Disponível a partir do 12cR2:
SET SERVEROUTPUT ON;
DECLARE
l_patch_name VARCHAR2(2000);
l_SQLID VARCHAR2(100) := 'bm2gp7sdc3pzp';
BEGIN
l_patch_name := dbms_sqldiag.create_sql_patch(
sql_id=> l_SQLID
,hint_text=> q'[
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "S"@"SEL$1")
FULL(@"SEL$2" "S"@"SEL$2")
FULL(@"SEL$2" "W"@"SEL$2")
FULL(@"SEL$2" "E"@"SEL$2")
LEADING(@"SEL$2" "S"@"SEL$2" "W"@"SEL$2" "E"@"SEL$2")
USE_NL(@"SEL$2" "W"@"SEL$2")
USE_NL(@"SEL$2" "E"@"SEL$2")
END_OUTLINE_DATA
]'
,name=>'sql_patch_'|| l_SQLID);
dbms_output.put_line('SQL Patch: ' || l_patch_name);
END;
/
Opção 2) Quando for 12cR1 ou inferior:
SET SERVEROUTPUT ON;
DECLARE
l_sql_text CLOB;
vSQL_ID varchar2(100) := 'bm2gp7sdc3pzp';
BEGIN
SELECT sql_fulltext
INTO l_sql_text
FROM v$sqlarea
WHERE sql_id = vSQL_ID;
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
name => 'SQL_Patch_' || vSQL_ID,
sql_text => l_sql_text,
hint_text => q'[
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
RBO_OUTLINE
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$2" "W"@"SEL$2")
FULL(@"SEL$2" "E"@"SEL$2")
FULL(@"SEL$2" "S"@"SEL$2")
LEADING(@"SEL$2" "W"@"SEL$2" "E"@"SEL$2" "S"@"SEL$2")
USE_MERGE(@"SEL$2" "E"@"SEL$2")
USE_MERGE(@"SEL$2" "S"@"SEL$2")
END_OUTLINE_DATA
]'
);
END;
/
Podemos consultar os SQL Patches na view DBA_SQL_PATCHES:
SET LINESIZE 300
COL NAME FORMAT A30
COL SIGNATURE FORMAT 999999999999999999999
COL CREATED FORMAT A20
COL FORCE FORMAT A10
SELECT NAME, SIGNATURE, TO_CHAR(CREATED,'DD/MM/YYYY HH24:MI:SS') AS DTCREATED, STATUS, FORCE_MATCHING AS FORCE
,(select sql_id from gv$sql where exact_matching_signature = b.signature and sql_text not like '%explain%' and rownum = 1) as sql_id
FROM DBA_SQL_PATCHES B
ORDER BY CREATED DESC;
Para dropar um SQL Patch:
EXEC SYS.DBMS_SQLDIAG.drop_sql_patch(name => 'sql_patch_bm2gp7sdc3pzp');
Desabilitando e habilitando um SQL Patch:
-- desabilitando
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH (name => 'sql_patch_bm2gp7sdc3pzp', attribute_name => 'STATUS', attribute_value => 'DISABLED');
-- habilitando
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH (name => 'sql_patch_bm2gp7sdc3pzp', attribute_name => 'STATUS', attribute_value => 'ENABLED');
Limpando Cursor da Memória
Em alguns casos pode ser que mesmo aplicando uma solução para fixar um plano, as sessões conectadas no BD continuem usando o plano de execução antigo nas novas execuções. Uma forma de tentar forçar o otimizador a pegar o novo plano é revmovendo o seu cursor da memória.
1) Gere os comandos que que devem ser executados em cada instância (em caso de RAC):
set lines 100
select inst_id, 'exec DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''',''C'');' limpar_memoria from GV$SQLAREA where SQL_ID='bm2gp7sdc3pzp';
INST_ID LIMPAR_MEMORIA
---------- ----------------------------------------------------------------------------------------------
2 exec DBMS_SHARED_POOL.PURGE ('000000045C5519D0,448911349','C');
2) Execute o comando DBMS_SHARED_POOL.PURGE conectado na respectiva instância:
SQL Instance 2> exec DBMS_SHARED_POOL.PURGE ('000000045C5519D0,448911349','C');
PL/SQL procedure successfully completed.
3) Verifique o Plan Hash Value sendo usado pelo comando SQL e valide se ele está sendo influenciado por algum SQL Profile, SPM Baseline ou SQL Patch:
set linesize 300
col sql_id format a20
col child_number format 999999
col sql_profile format a30
col sql_plan_baseline format a30
col sql_patch format a30
select inst_id, sql_id, child_number, sql_profile, sql_plan_baseline, sql_patch, plan_hash_value
from gv$sql
where sql_id='bm2gp7sdc3pzp';
INST_ID SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PLAN_BASELINE SQL_PATCH PLAN_HASH_VALUE
---------- -------------------- ------------ ------------------------------ ------------------------------ ------------------------------ ---------------
2 bm2gp7sdc3pzp 0 397881999
2 bm2gp7sdc3pzp 1 sql_patch_bm2gp7sdc3pzp 397881999
Conclusão
Este post apresentou as principais alternativas para tentar direcionar o otimizador a utilizar um plano de execução específico. Como citado nas respectivas seções, cada solução tem uma característica funcional e de licenciamento diferente. Fique atento a alguns detalhes de licenciamento, como por exemplo ao usar SPM: A feature em si faz parte do Enterprise Edition, mas criar Baseline importando do AWR já requer licença de Oracle Diagnostics Pack.
Também há um detalhe importante a se considerar sobre todas as 3 abordagens: Se não for possível reproduzir o plano devido alguma mudança no ambiente, como a ausência de um índice referenciado no plano, o otimizador não conseguirá usá-lo de forma alguma.
Olá, boa tarde. Excelente artigo, porém, tenho uma dúvida. Já li em outros tutoriais no site da Oracle e até algumas referencias ao Nigel Bayliss, que o SQL PATCH, pode ser utilizado na versão standard, porém, você informa que está faz parte do Enterprise. Minha dúvida é: Pode ser usado no Standard ou só no Enterprise?
Segue um exemplo (informação no final):
https://www.oracle.com/br/technical-resources/articles/sql/oracle-sql-patch.html
Olá Ricardo. Obrigado pelo feedback !
Revendo esse detalhe do licenciamento é meio obscurso mesmo. SQL Profile e SPM tem informações explícitas na documentação, mas realmente não há nada muito claro sobre SQL Repair Advisor ou SQL Patch especificamente.
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/
Em uma das referências no próprio artigo do Rogerio Eguchi, Maria Colgan diz:
“No additional licenses are needed to use SQL Repair Advisor or SQL patches. SQL Repair Advisor is available as part of Oracle Database Enterprise Edition 11g.”
https://blogs.oracle.com/optimizer/post/additional-information-on-sql-patches
Mas refletindo melhor essa colocação, ela pode estar querendo dizer que já está incluso no Enterprise sem necessidade de Option, mas não necessariamente que é restrito a Enterprise. Também há algumas variáveis como data da publicação, e a versão usada como base, visto que não é incomum a Oracle mudar a regra de licenciamento de um feature ao lançar novas versões.
Estou me convencendo de que podemos usar no Standard mesmo.