Close Menu
Performance

SQL Patch: injetar hints no Oracle sem alterar o código

HenriqueBy 9 Mins Read

This post is also available in: English (Inglês)

Você herdou uma query com PARALLEL(16) cravado no código, ou o contrário: precisa forçar paralelismo numa query que roda serial e não pode tocar na aplicação. Código de fornecedor, deploy que não preserva os comentários do SQL, ticket que leva meses para virar release. O SQL Patch existe exatamente para isso: injetar hints em um SQL específico sem alterar uma única linha do código.

Neste post você vai entender quando o SQL Patch é a ferramenta certa, como ele se compara a Baseline e Profile, e vai ter um lab completo no 19c forçando paralelismo em uma query serial, com a saída real de cada passo.

O que é (e o que não é) um SQL Patch

Um SQL Patch é um conjunto de hints que o otimizador passa a aplicar a um SQL identificado pela assinatura do texto. Ele não congela um plano inteiro: apenas adiciona a restrição do hint e deixa o CBO escolher o melhor plano dentro dela.

Três pontos que importam no dia a dia:

  • Não exige Tuning Pack (diferente do SQL Profile).
  • Sobrevive a deploys da aplicação que não preservam comentários inline.
  • É leve: guarda hints, não o plano completo com outline.

Quando o SQL Patch é a escolha certa

  • O código da aplicação não pode ser alterado.
  • Você já tentou SQL Plan Baseline e ela não reproduz (REPRODUCED = NO).
  • Você precisa de algo simples: forçar um hint específico, sem amarrar o plano inteiro.
  • Você quer flexibilidade: o CBO continua escolhendo o plano, mas dentro da restrição do hint.

SQL Patch vs Baseline vs Profile vs hint inline

AbordagemO que fazCusto / risco
SQL PatchInjeta hints específicosLeve. Sempre aplica o hint. Sem Tuning Pack
SQL Plan BaselineTenta recriar um plano inteiroPesada. Pode falhar em reproduzir e cair fora
SQL ProfileAjusta estimativas (cardinalidade, custo)Precisa de Tuning Pack. Não garante o plano
Hint inlineHint no próprio códigoSome no primeiro deploy que reescreve o SQL

Resumo prático: Baseline é pesada e pode não reproduzir; Profile mexe em estimativas e exige licença; hint inline morre no deploy. O Patch é direto, explícito e persistente.

Lab: forçar PARALLEL sem alterar o código

⚠️ Pré-requisitos: Enterprise Edition (Parallel Query é recurso EE), parallel_max_servers > 0 (o padrão já atende) e privilégio para executar DBMS_SQLDIAG. Cenário: Oracle 19c, Single Instance.

1. Massa de dados

Uma tabela sem grau de paralelismo definido (DEGREE = 1) e sem hint nenhum.

DROP TABLE t_lab_px PURGE;

CREATE TABLE t_lab_px AS
SELECT level AS id,
       MOD(level, 1000) AS grp,
       RPAD('x', 50, 'x') AS pad
FROM   dual
CONNECT BY level <= 3000000;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_LAB_PX');

SELECT degree FROM user_tables WHERE table_name = 'T_LAB_PX';
-- DEGREE = 1

⚠️ Bateu ORA-30009 (Not enough memory for CONNECT BY)? Em sessões com PGA apertada, gerar 3 milhões de níveis de uma vez estoura. Troque o gerador por dois CONNECT BY pequenos em cross join, que não seguram tudo na PGA: “sql CREATE TABLE t_lab_px AS WITH gen AS ( SELECT /+ materialize / level AS n FROM dual CONNECT BY level <= 2000 ) SELECT ROWNUM AS id, MOD(ROWNUM, 1000) AS grp, RPAD('x', 50, 'x') AS pad FROM gen a, gen b WHERE ROWNUM <= 3000000; “

2. Baseline: o plano nasce serial

SELECT /*+ qb_name(main) */ grp, COUNT(*)
FROM   t_lab_px t
GROUP  BY grp;

SELECT sql_id, child_number
FROM   v$sql
WHERE  sql_text LIKE 'SELECT /*+ qb_name(main) */ grp%'
AND    sql_text NOT LIKE '%v$sql%';
SQL_ID        CHILD_NUMBER
------------- ------------
3w40z8nk3h5g5            0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3w40z8nk3h5g5', NULL, 'BASIC +NOTE'));
Plan hash value: 1056803887

---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  HASH GROUP BY     |          |
|   2 |   TABLE ACCESS FULL| T_LAB_PX |
---------------------------------------

Nada de paralelismo. TABLE ACCESS FULL serial e ponto.

💡 O qb_name(main) é proposital: nomear o query block deixa o hint do patch mais legível e estável do que depender do SEL$1 autogerado.

3. Criar o SQL Patch injetando PARALLEL

No 19c, CREATE_SQL_PATCH é function, não procedure: o retorno tem que ir para uma variável.

SET SERVEROUTPUT ON
DECLARE
  v_patch VARCHAR2(128);
BEGIN
  v_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(
               sql_id      => '3w40z8nk3h5g5',
               hint_text   => 'PARALLEL(4)',
               name        => 'PX_LAB_PATCH',
               description => 'Injeta PARALLEL(4) sem alterar o codigo',
               category    => 'DEFAULT',
               validate    => TRUE);
  DBMS_OUTPUT.PUT_LINE('Patch criado: ' || v_patch);
END;
/
Patch criado: PX_LAB_PATCH
PL/SQL procedure successfully completed.
SELECT name, status, force_matching, sql_text
FROM   dba_sql_patches
WHERE  name = 'PX_LAB_PATCH';
NAME           STATUS   FOR SQL_TEXT
-------------- -------- --- -------------------------------------------
PX_LAB_PATCH   ENABLED  NO  SELECT /*+ qb_name(main) */ grp, COUNT(*)...

💡 Para mirar uma tabela específica em vez do statement inteiro, use a forma objeto do hint: PARALLEL(t@main 4), no formato PARALLEL(alias@query_block grau).

4. Rodar de novo e confirmar o plano paralelo

O sql_id não muda: o texto é idêntico, então só nasce um child cursor novo.

SELECT /*+ qb_name(main) */ grp, COUNT(*)
FROM   t_lab_px t
GROUP  BY grp;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3w40z8nk3h5g5', NULL, 'BASIC +NOTE'));
Plan hash value: 142943352

---------------------------------------------
| Id  | Operation                | Name     |
---------------------------------------------
|   0 | SELECT STATEMENT         |          |
|   1 |  PX COORDINATOR          |          |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |
|   3 |    HASH GROUP BY         |          |
|   4 |     PX RECEIVE           |          |
|   5 |      PX SEND HASH        | :TQ10000 |
|   6 |       HASH GROUP BY      |          |
|   7 |        PX BLOCK ITERATOR |          |
|   8 |         TABLE ACCESS FULL| T_LAB_PX |
---------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint
   - SQL patch "PX_LAB_PATCH" used for this statement

Duas linhas no Note fecham o caso: o grau de paralelismo virou 4 por causa do hint, e o patch foi de fato aplicado.

5. Confirmar o uso em runtime

SELECT sql_id, child_number, sql_patch
FROM   v$sql
WHERE  sql_id = '3w40z8nk3h5g5'
AND    sql_patch IS NOT NULL;
SQL_ID        CHILD_NUMBER SQL_PATCH
------------- ------------ ------------
3w40z8nk3h5g5            0 PX_LAB_PATCH

Comandos de administração do dia a dia

-- Listar
SELECT name, status, force_matching FROM dba_sql_patches;

-- Desabilitar sem apagar
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('PX_LAB_PATCH', 'STATUS', 'DISABLED');

-- Reabilitar
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('PX_LAB_PATCH', 'STATUS', 'ENABLED');

-- Remover
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH('PX_LAB_PATCH');

-- Confirmar uso em runtime
SELECT sql_id, sql_patch FROM v$sql WHERE sql_patch IS NOT NULL;

Pegadinhas que valem ouro

O patch é amarrado à assinatura do texto. Se a aplicação manda literais que mudam (um sql_id diferente a cada execução), um patch por sql_id casa só com aquela variante. Para os demais, ele simplesmente não aplica.

Force matching não sai pela CREATE_SQL_PATCH. A função pública DBMS_SQLDIAG.CREATE_SQL_PATCH aceita só sql_id (ou sql_text), hint_text, name, description, category e validate. Não existe parâmetro force_match aqui. O force_match que existe pertence a outra função, a DBMS_SQLDIAG.ACCEPT_SQL_PATCH, usada no fluxo do SQL Repair Advisor. Para forçar matching em um patch criado por CREATE_SQL_PATCH, o caminho é o workaround de staging table (criar a staging table com CREATE_STGTAB_SQLPATCH, empacotar com PACK_STGTAB_SQLPATCH, ajustar sqlflags e a assinatura na tabela e reimportar). É técnica avançada, não um simples parâmetro.

Hint de PARALLEL tem comportamento próprio. IGNORE_OPTIM_EMBEDDED_HINTS e OPTIMIZER_IGNORE_HINTS não derrubam um PARALLEL embutido. Para o caso inverso, remover paralelismo cravado no código, o caminho que funciona é injetar opt_param('optimizer_ignore_parallel_hints' 'true') via patch (disponível a partir do 18c).

CREATE_SQL_PATCH mudou de natureza no 12.2. Quem vem da época do DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH precisa lembrar: no 19c é function com retorno, então capture o valor em variável.

Conclusão

Quando você não pode tocar no código mas precisa controlar o plano, o roteiro é:

  1. Identifique o sql_id da query problemática.
  2. Capture o plano atual como baseline (DBMS_XPLAN.DISPLAY_CURSOR).
  3. Crie o patch com o hint desejado via DBMS_SQLDIAG.CREATE_SQL_PATCH (lembre: é function no 19c).
  4. Rode de novo e valide pela seção Note do plano e por v$sql.sql_patch.
  5. Se literais variam, avalie force matching pela staging table, não por parâmetro.
  6. Documente o patch e tenha ALTER e DROP à mão para reverter.

Referências

Share.

Related Posts

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted