Resolvendo problemas de lentidão com o SQL Plan Management

Olá pessoal,

No artigo de hoje vou mostrar como resolver problemas de performance em SQLs que usam variáveis bind, geram diversos cursores filhos e possuem mais de 1 plano de execução (PE), onde por exemplo, o desempenho de 1 desses PEs é bom, e o dos demais não. Isso ocorre com uma certa frequência nos Bancos de Dados de produção que administro, e para resolver o problema, costumo usar o SQL Plan Management.

Segundo a Oracle, o Otimizador é normalmente capaz de avaliar e gerar o melhor plano de execução sem qualquer intervenção do usuário, porém, muitas vezes, o PE pode mudar inesperadamente, e ocasionalmente para pior, por diversos motivos, tais como: coleta de novas estatísticas de objetos, alterações em parâmetros do otimizador e alterações na definição (DDL) dos objetos (tabelas ou índices). Para evitar a regressão de performance dos SQLs, nesses casos, a Oracle criou no 11G, o SQL Plan Management (SPM), que é um recurso que habilita o otimizador a executar somente PEs conhecidos e verificados. Ele fornece um repositório para gerenciar os PEs, permitindo que possamos, por exemplo, manter habilitado somente o PE (entre vários de um SQL) que apresenta o melhor desempenho, realizando essa tarefa de forma automática ou manual. Não entrarei em mais detalhes do SPM, explicarei apenas como usá-lo no modo manual, de forma rápida e simples. Se quiser aprender mais sobre ele sugiro estudar minuciosamente o conteúdo dos links que estou compartilhando nas referências deste artigo.

Não consegui (ainda) reproduzir em laboratório um cenário para gerarmos e testarmos o uso do SPM, portanto, iremos usar como exemplo um caso meu que ocorreu em um BD de Produção semana passada. Estou incluindo neste artigo os scripts que executei, porém fiz diversas alterações em nomes de objetos, por exemplo, para não expor dados confidenciais da empresa em que trabalho. Abstraia os exemplos partindo do princípio que você já identificou um SQL que usa variáveis bind, que possui vários cursores filhos, e que está apresentando problemas de desempenho. Para começar iremos executar a consulta abaixo para pegar algumas informações do SQL e seus PEs:

select sql_id, child_number, plan_hash_value,

OPTIMIZER_COST, SQl_PLAN_BASELINE

from v$sql where sql_id = '123hy8fmz7aun';

Veja no retorno abaixo que temos 2 cursores (child_number) com PEs diferentes (plan_has_value), e cada um com um custo (optimizer_cost) também diferente, apesar da diferença ser pouca:

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE OPTIMIZER_COST SQL_PLAN_BASELINE

------------- ------------ --------------- -------------- -------------------------------

123hy8fmz7aun 0 5554955165 1894

123hy8fmz7aun 3 6663155055 1877

Agora precisamos entender qual deles é o que está apresentando lentidão. Veremos primeiro o PE do cursor com child number igual a zero:

select * from table(dbms_xplan.display_cursor('123hy8fmz7aun',0));

Veja no resultado abaixo (parcial, pois extrai as informações irrelevantes para esta análise) que o Otimizador executa um FTS (Full Table Scan) em todas as tabelas acessadas:

Plan hash value: 5554955165
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1894 (100) | |
| 1 | SORT UNIQUE | | 157 | 7065 | 1893 (1) | 00:00:01 |
|* 2 | HASH JOIN RIGHT SEMI| | 157 | 7065 | 1892 (1) | 00:00:01 |
|* 3 | TABLE ACCESS FULL | TB_ABA | 679 | 10185 | 88 (4) | 00:00:01 |
|* 4 | HASH JOIN | | 15691| 459K | 1804 (1) | 00:00:01 |
|* 5 | TABLE ACCESS FULL | TB_ABG | 18 | 162 | 7 (0) | 00:00:01 |
|* 6 | TABLE ACCESS FULL | TB_ABA | 887 | 18627 | 353 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------

Agora veremos o PE do cursor com child number igual a três:

select * from table(dbms_xplan.display_cursor('123hy8fmz7aun',3));

Veja no resultado abaixo (parcial, pois extrai as informações irrelevantes para esta análise) que o Otimizador executou alguns passos diferentes neste segundo PE. Para realizar um join entre as tabelas TB_ABG e TB_ABA, ao invés de fazer um FTS nelas, ele fez um FTS apenas na tabela TB_ABG e fez um INDEX RANGE SCAN no índice IX_ANG para encontrar as linhas correspondentes na tabela TB_ABA.

Plan hash value: 6663155055
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1875 (100)| |
| 1 | SORT UNIQUE | | 157 | 6123 | 1874 (1)| 00:00:01 |
|* 2 | HASH JOIN RIGHT SEMI | | 157 | 6123 | 1873 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TB_ABA | 679 | 10185 | 88 (4)| 00:00:01 |
| 4 | NESTED LOOPS | | 15694 | 367K| 1785 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 35910 | 367K| 1785 (1)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TB_ABG | 18 | 162 | 7 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IX_ANG | 1995 | | 7 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| TB_ABA | 887 | 13305 | 349 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

O segundo PE está com custo um pouco menor (caiu de 1894 para 1875) e faz um FTS a menos, portanto, por dedução (e experiências anteriores), conclui que esse era o PE bom, que não apresenta problemas de performance. O que preciso então fazer agora para que ele seja o único a ser executado? Preciso usar o SPM para carregar os 2 planos em seu repositório, e desabilitar o PE do cursor com child number igual a zero, seguindo o roteiro abaixo:

1- Carregando os PEs no SPM recuperando-os da Shared Pool, a partir do sql_id da instrução SQL:

declare

v_ret number;

begin

v_ret := dbms_spm.load_plans_from_cursor_cache(sql_id=> '123hy8fmz7aun');

end;

2- Agora vamos verificar se os PEs foram corretamente carregados no repositório do SPM, informando parte do texto da instrução SQL (seria mais fácil se fosse possível passar o sql_id, mas infelizmente não é possível):

select sql_text, sql_handle, plan_name, optimizer_cost as cost, enabled

from dba_sql_plan_baselines b

where dbms_lob.substr(sql_text,4000,1) like 'select xxxxx from yyyyyyyyyy%'

3- Sim, eles foram carregados e ambos estão habilitados (ver resultado abaixo). Dentro do repositório do SPM esses PEs são chamados de SQL Plan Baselines. Para desabilitar o PE que parece estar ruim, eu peguei os valores das colunas sql_handle e plan_name da linha que tem o valor de custo (optimizer_cost as cost) igual ao do PE que eu havia identificado anteriormente como sendo o mais lento, neste caso, o valor de 1894, e passei os valores como argumentos para o próximo passo que vou executar:

SQL_TEXT SQL_HANDLE PLAN_NAME COST ENABLED

---------------------------- -------------------- ----------------------- ---- ---------
select xxxxx from yyyyyyyyyy SQL_de8888a07b77033a SQL_PLAN_dwwnrn2a9j46d1 1894 YES

select xxxxx from yyyyyyyyyy SQL_de8888a07b77033a SQL_PLAN_dwwnrn2b7e33e3 1875 YES

4- Desativando o PE com custo igual a 1894:

declare

v_ret number;

begin

v_ret := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_de8888a07b77033a',
plan_name => 'SQL_PLAN_dwwnrn2a9j46d1',

attribute_name => 'ENABLED',

attribute_value => 'NO');

end;

5- Execute novamente a consulta do passo 2 e veja que agora está habilitado somente o PE com nome (plan_name) igual a "SQL_PLAN_dwwnrn2b7e33e3":

SQL_TEXT SQL_HANDLE PLAN_NAME COST ENABLED

---------------------------- -------------------- ----------------------- ---- ---------

select xxxxx from yyyyyyyyyy SQL_de8888a07b77033a SQL_PLAN_dwwnrn2a9j46d1 1894 NO

select xxxxx from yyyyyyyyyy SQL_de8888a07b77033a SQL_PLAN_dwwnrn2b7e33e3 1875 YES

6- Por fim, só para ter certeza de que você deixou habilitado o PE correto, execute o SQL abaixo e compare o PE que será exibido com o PE que você escolheu anteriormente (aquele com child number igual a três):

select * from TABLE

(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_de8888a07b77033a','SQL_PLAN_dwwnrn2b7e33e3'));

Pronto, agora é só monitorar o BD e ver se o PE habilitado é realmente aquele que apresenta melhor performance. Neste meu caso, eu acertei na primeira! Logo após desabilitar o PE que eu deduzia ser o ruim, vi pelo oratop que a carga da máquina e quantidade de usuários ativos caiu imediatamente.

Obs.: "O SQL Plan Management" é free, portanto não precisa de licenciamento extra. A Oracle atualmente recomenda, também, a sua utilização, após um upgrade de versão, visando evitar regressão de performance de SQL, ao invés de configurar o parâmetro OPTIMIZER_FEATURES_ENABLE (ver imagem abaixo):