Evite surpresas desagradáveis no desempenho das consultas

Você já ouviu alguém dizer: Minha aplicação ficou lenta e nada foi alterado. Este problema relativamente comum pode ser evitado se você implementar no banco Oracle o recurso SQL PLAN MANAGEMENT (SPM), este recurso esta disponível a partir versão 11G e não requer a licença das Options Diagnostic & Tuning Pack. Basicamente o SPM é um mecanismo preventivo que permite ao Otimizador gerenciar automaticamente os planos de execução, garantindo que o banco de dados use apenas planos que tenham desempenho igual ou melhor aos planos de execução utilizados anteriormente, este mecanismo impede que uma consulta sofra uma regressão no seu desempenho se não houver alterações nas estruturas dos objetos e parâmetros do banco de dados.

A seguir apresentamos uma pequena introdução de como funciona a SQL PLAN BASELINE que é o principal mecanismo do SPM e na sequência vamos fazer uma simulação prática de como funciona o SPM.

Quando uma instrução SQL é submetida ao processo de HARD PARSE, o Otimizador produz vários planos de execução e seleciona aquele com o menor custo. Se esta instrução SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE for igual a um dos planos existente na BASELINE, o Otimizador vai verificar se este plano foi aceito, se SIM o plano será utilizado na execução, se no entanto o plano não foi aceito, o Otimizador vai verificar a existência de outro plano aceito com custo menor e utilizar este plano da BASELINE na execução da instrução SQL, se não existir outro plano aceito na BASELINE o plano gerado originalmente no HARD PARSE será utilizado.

O fluxograma abaixo representa a explicação acima

Outro fluxo alternativo ao apresentado anteriormente seria após o HARD PARSE, se esta instrução SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE NÃO for igual a um dos planos existente na BASELINE, neste caso o Otimizador vai comparar o custo desse novo plano com o custo dos planos aceitos, se o custo do novo plano for menor serão geradas BASELINE HINTS para ele, se no entanto estas BASELINES HINTS não conseguirem reproduzir o plano gerado no HARD PARSE, o Otimizador vai verificar a existência de outro plano aceito com o menor custo e utilizar este plano da BASELINE na execução da instrução SQL, se as BASELINES HINTS conseguirem reproduzir o plano gerado no HARD PARSE, o novo plano será adicionado a BASELINE como não aceito e um plano da BASELINE com menor custo será utilizado na execução da instrução SQL.

Para ajudar no entendimento de como funcionam as BASELINES vamos realizar uma simulação do processo de inclusão e evolução dos planos de execução de uma instrução SQL sob o controle do recurso SQL PLAN MANAGEMENT:

01) Habilitar o uso do SQL PLAN MANAGEMENT
02) Executar uma consulta com SPM habilitado
03) Verificar o plano de execução na SQL PLAN BASELINE
04) Executar a consulta novamente usando a BASELINE
05) Criar um índice para melhorar o desempenho da consulta
06) Verificar se a consulta vai usar o índice
07) Verificar existência de mais um plano na BASELINE
08) Verificar qual plano será utilizado numa nova execução
09) Executar o procedimento de evolução do SPM
10) Executar a consulta novamente com o novo plano
11) Eliminar a BASELINE criada

01) Habilitar o uso do SQL PLAN MANAGEMENT

Para utilizar o SQL PLAN MANAGEMENT precisamos habilitar o parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES.

02) Executar uma consulta com SPM habilitado

Observe no plano de execução que o Otimizador utilizou a operação TABLE ACCESS FULL para acessar a tabela CUSTOMERS do esquema SH.

03) Verificar o plano de execução na SQL PLAN BASELINE

Para obter informações sobre as consultas que estão sendo gerenciadas pelo SPM basta consultar a visão DBA_SQL_PLAN_BASELINES.

No resultado da consulta podemos verificar que na BASELINE temos uma instrução SQL sendo gerenciada (SQL_15042ba17146e187) e esta instrução tem somente um plano de execução (SQL_PLAN_1a11bn5sndsc764541f84). Este plano esta habilitado e aceito para ser utilizado na execução desta instrução SQL.
Utilizando o procedure DISPLAY_SQL_PLAN_BASELINE do pacote DBMS_XPLAN podemos conferir que o plano armazenado na BASELINE é o mesmo plano que foi utilizado na execução da consulta.

04) Executar a consulta novamente usando a BASELINE

Vamos executar a mesma consulta novamente e verificar se a BASELINE será utilizada.
 

Observe na seção NOTE do plano de execução que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execução utilizado foi o que esta na BASELINE.

05) Criar um índice para melhorar o desempenho da consulta

Como a consulta que estamos executando possui o campo CUST_LAST_NAME no filtro da cláusula WHERE e não existe um índice desta coluna para a tabela CUSTOMERS, vamos criar um índice neste campo para melhorar o desempenho desta consulta.

06) Verificar se a consulta vai usar o índice

Após a criação do índice vamos executar a consulta novamente para verificar se o índice será utilizado no plano de execução.

Observando o plano de execução acima, constatamos que o índice não foi utilizado, o Otimizador continua utilizando o mesmo plano utilizado nas execuções anteriores. O novo plano com a utilização do índice não foi utilizado pois o SPM está atuando junto ao Otimizador, ele identificou que existe um novo plano com um CUSTO menor do que aquele que está ACEITO na BASELINE, porem ele não vai utilizar este novo plano até que o seu desempenho seja validado.

07) Verificar existência de mais um plano na BASELINE

Vamos consultar a visão DBA_SQL_PLAN_BASELINES novamente e verificar se realmente o SPM já registrou a existência de um novo plano com a utilização do índice que criamos anteriormente.

Verificando o relatório acima podemos constatar que um novo plano (SQL_PLAN_1a11bn5sndsc71a248297) utilizando o índice foi inserido para a consulta (SQL_15042ba17146e187) e está com o campo ACCEPTED igual NO, por esta razão ele não será utilizado até que o seu desempenho seja validado e esse campo passe a ser igual a YES.

08) Verificar qual plano será utilizado numa nova execução

Vamos executar a consulta novamente para confirmar que o novo plano de execução utilizando o índice não será utilizado.

Observe na seção NOTE do plano de execução que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execução utilizado foi aquele que acessa a tabela CUSTOMERS usando operação TABLE ACCESS FULL.

09) Executar o procedimento de evolução do SPM

Ao habilitar o gerenciamento dos planos de execução utilizando o SPM garantimos que nenhum plano de execução será substituído por outro sem que antes o Oracle verifique se o desempenho deste novo plano é melhor que aqueles já utilizados, este mecanismo impede a regressão de desempenho na execução das instruções SQL, para evoluir o plano da nossa consulta vamos executar a procedure EVOLVE_SQL_PLAN_BASELINE do pacote DBMS_SPM.

Observe no relatório na seção EXECUTION STATISTICS que o SPM simula a execução do plano não aceito e compara as estatísticas de desempenho dos dois planos, o que estava ACEITO na BASELINE e o novo plano. Na sequência temos a seção FINDINGS onde o SPM indica que o novo plano foi aceito.

10) Executar a consulta novamente com o novo plano

Vamos executar a consulta novamente e verficar se houve alguma evolução no plano de execução utilizado pelo Otimizador.

Analisando o plano de execução constatamos que o índice CUST_NAME_IDX criado na etapa 05 foi utilizado, este plano de execução utilizando índice é muito mais eficiente, o plano que fazia FULL SCAN na tabela CUSTOMERS estava lendo 1.520 Buffers, o novo plano leu somente 6 Buffers na Memoria do banco.

11) Eliminar a BASELINE criada

Vamos eliminar a BASELINE e o ÍNDICE para deixar o esquema SH como estava antes da simulação.

Referências

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/overview-of-sql-plan-management.html