Introdução

Quando precisamos analisar um plano de execução de uma consulta SQL no Oracle, podemos precisar da versão completa do plano incluindo as estatísticas reais das últimas execuções (ao invés de confiar puramente nas estimativas que aparecem no plano de execução convencional).

Por exemplo, a query com SQL_ID “90wxxwzxgpxvs” demonstrada abaixo apresenta a seguinte estrutura:

Plan hash value: 4040251747                                                                                                                        
                                                                                                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                          | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |  OMem |  1Mem | Used-Mem |    
-----------------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                   |              |        |       | 32617 (100)|          |       |       |       |       |          |    
|   1 |  HASH GROUP BY                     |              |      1 |    56 | 32617   (1)| 00:00:02 |       |       |  1034K|  1034K|  554K (0)|    
|*  2 |   HASH JOIN                        |              |    328 | 18368 | 32616   (1)| 00:00:02 |       |       |  1572K|  1572K|  747K (0)|    
|*  3 |    TABLE ACCESS FULL               | COUNTRIES    |      1 |    18 |     3   (0)| 00:00:01 |       |       |       |       |          |    
|   4 |    NESTED LOOPS                    |              |   7225 |   268K| 32613   (1)| 00:00:02 |       |       |       |       |          |    
|   5 |     NESTED LOOPS                   |              |   7225 |   268K| 32613   (1)| 00:00:02 |       |       |       |       |          |    
|   6 |      VIEW                          | VW_GBC_16    |   7225 |   190K| 25366   (1)| 00:00:01 |       |       |       |       |          |    
|   7 |       HASH GROUP BY                |              |   7225 |   232K| 25366   (1)| 00:00:01 |       |       |  1417K|  1417K| 1866K (0)|    
|*  8 |        HASH JOIN                   |              |   7225 |   232K| 25365   (1)| 00:00:01 |       |       |  1506K|  1506K| 1279K (0)|    
|   9 |         PART JOIN FILTER CREATE    | :BF0000      |      7 |   112 |    27   (0)| 00:00:01 |       |       |       |       |          |    
|* 10 |          TABLE ACCESS FULL         | TIMES        |      7 |   112 |    27   (0)| 00:00:01 |       |       |       |       |          |    
|  11 |         PARTITION RANGE JOIN-FILTER|              |     11M|   180M| 25308   (1)| 00:00:01 |:BF0000|:BF0000|       |       |          |    
|  12 |          TABLE ACCESS FULL         | SALES        |     11M|   180M| 25308   (1)| 00:00:01 |:BF0000|:BF0000|       |       |          |    
|* 13 |      INDEX UNIQUE SCAN             | CUSTOMERS_PK |      1 |       |     1   (0)| 00:00:01 |       |       |       |       |          |    
|  14 |     TABLE ACCESS BY INDEX ROWID    | CUSTOMERS    |      1 |    11 |     2   (0)| 00:00:01 |       |       |       |       |          |    
-----------------------------------------------------------------------------------------------------------------------------------------------    

Neste exemplo temos apenas as colunas com o prefixo “E-” , de “Estimated”. Então você decide visualizar o plano com as métricas reais, que são apresentadas naquelas colunas com o prefixo “A-” de “Actual”, e tenta obter isso incluindo a opção “allstats” na chamada da DBMS_XPLAN.DISPLAY_CURSOR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
 SQL_ID=>'90wxxwzxgpxvs', 
 CURSOR_CHILD_NO=>0, 
 FORMAT=>'ALL allstats last'
));

Mas o plano resultante é exatamente igual ao anterior, com a única diferença que ele inclui essa Nota no rodapé:

Note                                                                                                                                               
-----                                                                                                                                              
   - Warning: basic plan statistics not available. These are only collected when:                                                                  
       * hint 'gather_plan_statistics' is used for the statement or                                                                                
       * parameter 'statistics_level' is set to 'ALL', at session or system level                                                                  
                                                                                              

Aqui você se depara com algumas alternativas como: Alterar o parâmetro statistics_level para ALL a nível de sistema, o que é impraticável se você está fazendo isso em um banco de dados importante (“e se ele não é importante, por que você precisa dele ?” — Ricardo Portilho).

Outra opção mais usual seria alterar esse parâmetro a nível de sessão, sendo possível criar uma Trigger de Logon para executar o “ALTER SESSION” quando chegar uma nova conexão da aplicação ou módulo específico. Mas essa abordagem também pode ser impraticável em ambientes altamente críticos em que a criação de uma Trigger de Logon poderia causar um impacto muito maior na aplicação, além de possivelmente precisar passar por um fluxo de aprovação que só falta pedir a benção do papa.

Agora a gente cai na terceira opção que é adicionar esse HINT “gather_plan_statistics” na Query, algo que você pode simplesmente pegar o texto da consulta e fazer isso executando manualmente no SQL Developer. Mas eventualmente essa abordagem também pode adicionar mais variáveis ao teste, fazendo com que o otimizador gere um plano de execução diferente daquele que está sendo usado pela a aplicação.

A dica

A dica deste post é se você quer ou precisa adicionar esse HINT a query original pra avaliar o comportamento gerado pela própria aplicação, sem alterar a aplicação para adicionar o HINT no texto da query.

Solução: Criar um SQL Patch !

Se você ainda não sabe o que é ou nunca usou esse recurso, de forma bem resumida: Ele permite anexar HINTs em um SQL_ID de forma dinâmica. Se você pesquisar por “Oracle SQL Patch” no Google, irá encontrar vários posts de boas referências explicando sobre como usá-lo. Eu já falei um pouco sobre SQL Patch no post “3 Abordagens Diferentes Para Fixar Planos de Execução de Comandos SQL no Oracle Database“.

Voltando ao foco deste post, o objetivo aqui é apenas incluir o HINT “gather_plan_statistics” para o SQL_ID que queremos analisar. Neste caso, não iremos usar o SQL Patch para influenciar as decisões do otimizador, apenas para forçar a coleta das estatísticas de execução da query.

Exemplo para versão 12cR2 ou superior:

SET SERVEROUTPUT ON;
DECLARE
l_patch_name VARCHAR2(100);
l_SQLID  VARCHAR2(32) := '90wxxwzxgpxvs';
vName VARCHAR2(32) := 'patch_'|| l_SQLID || '_plan_stats';
BEGIN

l_patch_name := sys.dbms_sqldiag.create_sql_patch(
 sql_id=> l_SQLID
,hint_text => 'gather_plan_statistics' 
,name=> vName
,category=>'DEFAULT'
);

dbms_output.put_line('SQL Patch: ' || l_patch_name);
END;
/

Para as versões 11g e 12cR1:

SET SERVEROUTPUT ON;
DECLARE
 l_sql_text CLOB;
 vSQL_ID varchar2(32) := '90wxxwzxgpxvs';
 vCount number;
 vName VARCHAR2(32) := 'patch_'|| l_SQLID || '_plan_stats';
BEGIN

 SELECT sql_fulltext INTO l_sql_text FROM gv$sqlarea WHERE sql_id = vSQL_ID AND rownum=1;
 
 SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
   name => vName,
   sql_text => l_sql_text,
   hint_text => 'gather_plan_statistics' 
 );

 DBMS_OUTPUT.PUT_LINE('Patch criado: ' || vName);
END;
/

Tecnicamente existe a possibilidade de o código acima falhar se o SQL_ID não estiver mais disponível na memória, mas considerando que o contexto aqui possivelmente envolve a análise de algo que está executando ou executou recentemente, esse script deve funcionar na maioria dos casos.

No entanto, se você eventualmente precisar criar um SQL_PATCH para uma query que não está disponível na memória no momento, e assumindo que pode usar o AWR, basta alterar a linha 9, trocando o nome da view GV$SQLAREA para DBA_HIST_SQLTEXT que funciona da mesma forma.

Demo

SYSTEM@DBSOL1; SET SERVEROUTPUT ON;
SYSTEM@DBSOL1; DECLARE
  2  l_patch_name VARCHAR2(100);
  3  l_SQLID  VARCHAR2(32) := '90wxxwzxgpxvs';
  4  vName VARCHAR2(32) := 'patch_'|| l_SQLID || '_plan_stats';
  5  BEGIN
  6  l_patch_name := sys.dbms_sqldiag.create_sql_patch(
  7   sql_id=>; l_SQLID
  8  ,hint_text =>; 'gather_plan_statistics' 
  9  ,name=>; vName
 10  ,category=>;'DEFAULT'
 11  );
 12  dbms_output.put_line('SQL Patch: ' || l_patch_name);
 13  END;
 14* /
SQL Patch: patch_90wxxwzxgpxvs_plan_stats


PL/SQL procedure successfully completed.

Ao consultar a GV$SQL, podemos confirmar que o cursor foi gerando usando o SQL Patch que criamos:

SYSTEM@DBSOL1; select inst_id, sql_id, plan_hash_value, child_number, last_load_time, sql_patch from gv$sql where sql_id='90wxxwzxgpxvs';

   INST_ID SQL_ID              PLAN_HASH_VALUE    CHILD_NUMBER LAST_LOAD_TIME         SQL_PATCH                         
__________ ________________ __________________ _______________ ______________________ _________________________________ 
         1 90wxxwzxgpxvs            4040251747               1 2024-11-11/13:18:46    patch_90wxxwzxgpxvs_plan_stats    

DICA: A criação de um SQL Patch causa um “invalidate” do cursor atual e o Oracle gera um novo cursor usando o SQL Patch na próxima vez que a query for executada, esse comportamento pode ser visto normalmente nas queries que executam em poucos segundos. Se o problema for com uma query de longa duração, pode ser mais produtivo solicitar o cancelamento da execução atual, forçar o purge do SQL_ID e iniciar uma nova execução.

Agora ao consultar o plano com o novo CHILD_NUMBER=1 que foi gerado, temos as novas colunas geradas graças ao hint “gather_plan_statistics”:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
 SQL_ID=>'90wxxwzxgpxvs', 
 CURSOR_CHILD_NO=>1, 
 FORMAT=>'ALL allstats last'
));

No rodapé do plano você deve ver algo como isso, indicando o uso do SQL Patch, ao invés da antiga mensagem:

Note                                                                                                                                                                                                 
-----                                                                                                                                                                                                
   - SQL patch "patch_90wxxwzxgpxvs_plan_stats" used for this statement                                                                                                                              
                                                                                         

Após concluir o throubleshoting e não precisar mais do HINT, remova o SQL Patch com o comando abaixo:

EXEC SYS.DBMS_SQLDIAG.drop_sql_patch(name => 'patch_90wxxwzxgpxvs_plan_stats');

IMPORTANTE: O desempenho da consulta é afetado quando executada com a coleta de todas as estatísticas, então sempre remova o HINT/SQL_PATCH após não precisar mais dele para fins de Throubleshooting.

Referências:

Using SQL Patch to add hints to a packaged application

Video: Quickly fix bad statements with SQL Patch

How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1)