Baselines e melhores planos

Arup Nanda

Use o gerenciamento de planos de SQL do Banco de Dados Oracle 11g para otimizar os planos de execução.

Você já se deparou com uma situação em que algumas consultas do banco de dados que costumavam ter um bom comportamento repentinamente começaram a ter mau desempenho? Provavelmente você foi atrás do motivo e este foi uma mudança no plano de execução. Uma análise mais detalhada pode ter revelado que a mudança de desempenho se deveu a estatísticas do otimizador recém-coletadas sobre as tabelas e índices aos quais essas consultas se referiam.

Totalmente desconcertado com a situação, você alguma vez tomou a decisão de parar a coleta de estatísticas? Esse curso de ação mantém os planos de execução praticamente inalterados para essas consultas, mas piora outras coisas. O desempenho de algumas outras consultas ou até das mesmas com predicados diferentes (as cláusulas WHERE) cai devido aos planos de execução abaixo do ideal gerados a partir de estatísticas antigas.

Qualquer ação que você adotar em seguida implicará algum risco, portanto, como é possível atenuá-lo e garantir que os planos de execução para as instruções SQL geradas sejam ideais, ao mesmo tempo mantendo um ambiente saudável no qual as estatísticas do otimizador sejam coletadas rotineiramente e todas as instruções SQL tenham bom desempenho sem mudanças significativas (como a adição de dicas)? Você pode recorrer ao uso de stored outlines para congelar o plano, mas isso também significa impedir o otimizador de gerar planos de execução potencialmente benéficos.

Usando o novo recurso de gerenciamento de planos de SQL no Banco de Dados Oracle 11g, agora é possível examinar como os planos de execução vão mudando, fazer o banco de dados confirmar os novos planos executando-os antes de usá-los e fazer a evolução gradual dos planos de maneira controlada.

Gerenciamento de planos de SQL

Quando o gerenciamento de planos de SQL está ativado, o otimizador armazena os planos de execução gerados em um repositório especial: a base de gerenciamento de SQL. Todos os planos armazenados para uma instrução SQL específica são considerados parte de um histórico de planos para essa instrução.

Alguns dos planos do histórico podem ser marcados como “aceito”. Quando a instrução SQL é reanalisada, o otimizador considera apenas os planos aceitos no histórico. Esse conjunto de planos aceitos para essa instrução SQL é chamado de baseline de plano de SQL ou simplesmente baseline.

Porém, o otimizador ainda tenta gerar um plano melhor. Se o otimizador gera um novo plano, este é adicionado ao histórico, mas não é considerado durante a reanálise do SQL, a menos que o novo plano seja melhor do que todos os planos aceitos na baseline. Portanto, com o gerenciamento de planos de SQL ativado, as instruções SQL nunca terão repentinamente um plano menos eficiente que resulte em desempenho inferior.

Com o gerenciamento de planos de SQL, você pode examinar todos os planos disponíveis no histórico para uma instrução SQL, compará-los para ver sua eficiência relativa, promover um plano específico ao status de aceito e até tornar um plano permanente (fixo).

Este artigo mostrará como gerenciar baselines de planos de SQL, incluindo captura, seleção e evolução de baselines, usando o Oracle Enterprise Manager e SQL a partir da linha de comando para assegurar o desempenho ideal das instruções SQL.

Captura

A função de captura do gerenciamento de planos de SQL captura os vários planos do otimizador usados pelas instruções SQL. Como padrão, a captura fica desativada, ou seja, o gerenciamento de planos de SQL não captura o histórico das instruções SQL que estão sendo analisadas ou reanalisadas.

Agora vamos capturar as baselines para alguns exemplos de instruções SQL provenientes de uma única sessão. Utilizaremos um esquema de amostra fornecido com o Banco de Dados Oracle 11g – SH – e a tabela SALES em particular.

Primeiro, ativamos a captura de baseline na sessão: 

alter session 
set optimizer_capture_sql_plan_baselines = true;

Agora, todas as instruções SQL executadas nessa sessão serão capturadas, junto com seus planos de otimização, na base de gerenciamento de SQL. Toda vez que o plano mudar para uma instrução SQL, ele será armazenado no histórico de planos. Para ver isso, execute o script mostrado na Listagem 1, que executa exatamente o mesmo SQL, mas sob circunstâncias diferentes. Primeiro, o SQL é executado com todos os padrões (incluindo um optimizer_mode = all_rows padrão implícito). Na próxima execução, o valor do parâmetro optimizer_mode é definido com first_rows. Antes da terceira execução do SQL, coletamos estatísticas atualizadas sobre a tabela e os índices.

Listagem 1 do código: Capturando baselines de plano de SQL 

alter session set optimizer_capture_sql_plan_baselines = true;
-- First execution. Default Environment
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Change the optimizer mode
alter session set optimizer_mode = first_rows;
-- Second execution. Opt Mode changed
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Gather stats now
begin
    dbms_stats.gather_table_stats (
        ownname            => 'SH',
        tabname             => 'SALES',
        cascade              => TRUE,
        no_invalidate      => FALSE,
        method_opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
        granularity          => 'GLOBAL AND PARTITION',
        estimate_percent => 10,
        degree                => 4
    );
end;
/
-- Third execution. After stats
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;

Se o plano mudar em cada uma das execuções do SQL na Listagem 1, os diferentes planos serão capturados no histórico dessa instrução SQL. (O comentário /* ARUP */ identifica facilmente as instruções SQL específicas no pool compartilhado.)

A maneira mais fácil de ver o histórico de planos é através do Oracle Enterprise Manager. Na página principal do Banco de Dados, escolha a guia Server e clique em SQL Plan Control. Nessa página, escolha a guia SQL Plan Baseline. Nessa página, procure as instruções SQL que contenham o nome ARUP, como na Figura 1, que mostra o histórico de planos das instruções SQL na parte inferior da tela.

 

figure 1
Figura 1: Histórico de planos de SQL


Se você clicar nos nomes dos planos de SQL, como SYS_SQL_PLAN_27a47aa154bc8843, poderá ver os detalhes do plano armazenados no histórico. As colunas importantes na tela são as seguintes: 

  • Enabled indica se o plano está ativo. 
  • Accepted indica se o plano deve ser considerado pelo otimizador. Se mais de um plano for aceito, o otimizador selecionará o melhor de todos. 
  • Fixed indica se o plano deve ser usado permanentemente para essa instrução SQL. Se mais de um plano for fixo, o otimizador selecionará o melhor de todos. 
  • Auto Purge indica se o plano, se não usado, será excluído automaticamente do histórico após um tempo especificado. Planos não usados são excluídos automaticamente do histórico após um tempo especificado, a menos que o auto purge esteja desativado. O tempo após o qual os planos não usados são excluídos é mostrado na Figura 1, ao lado do rótulo Plan Retention (Weeks). Nesse caso, está ajustado para 53 semanas, mas você pode mudar clicando no botão Configure.

Você também pode ativar a captura e uso das baselines de plano de SQL nesta tela do Oracle Enterprise Manager, clicando nos links apropriados na seção Settings.

Observe que você também pode carregar planos em uma baseline de plano de SQL a partir do cache do cursor ou de um conjunto de ajuste de SQL. Quando você carrega manualmente os planos em uma baseline de planos de SQL, esses planos são adicionados como planos aceitos. Para obter mais informações, consulte o Capítulo 15, “Using SQL Plan Management” do Oracle Database Performance Tuning Guide.

Usando baselines

Com as baselines de plano de SQL capturadas, agora ativamos seu uso pelo otimizador: 

alter session set 
optimizer_use_sql_plan_baselines = true;

Com o uso da baseline ativado, quando o otimizador reanalisa uma instrução SQL, ele examina os planos armazenados na baseline para essa instrução e escolhe o melhor de todos. É aí que entra o benefício mais importante das baselines. O otimizador também ainda reanalisa as instruções SQL – a presença de uma baseline não impede isso – e se o plano recém-gerado não for encontrado no histórico de planos do SQL, será adicionado, mas não “aceito”. Assim, se o plano recém-gerado for pior, o desempenho do SQL não será afetado, pois o plano não será usado. Entretanto, em alguns casos, você pode decidir que o novo plano é melhor, com base no seu conhecimento da distribuição dos dados ou da lógica do aplicativo. Por exemplo, suponha que o plano tenha sido capturado quando a tabela estava praticamente vazia, fazendo o otimizador bem apropriadamente escolher uma verificação de índice. Mas você sabe que o aplicativo preenche a tabela antes de chamar a instrução SQL posteriormente, e que uma verificação completa da tabela será efetivamente melhor para o plano no longo prazo. Nesse caso, você pode examinar o novo plano depois e, se for melhor, pode aceitá-lo, após o que o otimizador o considerará. É por isso que você obtém o melhor dos dois mundos: um bom plano é sempre usado, mas se o otimizador gerar um melhor, ele ficará disponível para comparação.

Se você não quiser usar planos na baseline para uma instrução SQL, poderá usar a seguinte instrução na sessão antes de chamar a instrução SQL para desativar o uso das baselines: 

alter session set 
optimizer_use_sql_plan_baselines = false;

A Listagem 2 executa a mesma consulta duas vezes: primeiro com baselines ativadas e depois com elas desativadas, e você pode ver como o plano muda após a desativação. Inicialmente, o otimizador escolheu BITMAP INDEX FULL SCAN no índice SALES_TIME_BIX. Após a desativação da baseline, o plano mudou para TABLE ACCESS FULL na tabela SALES, porque esse parece ser o melhor plano com base nas estatísticas do otimizador e em outras variáveis que estão afetando o otimizador agora. Antes, quando a baseline estava ativada, o otimizador selecionou o melhor plano do conjunto de planos aceitos armazenados na baseline.

Listagem 2 do código: uso da baseline de plano de SQL 

SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_TIME_BIX  |
--------------------------------------------------------------

-- Now disable baselines and look at the latest plan

SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.

SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));

PLAN_TABLE_OUTPUT
----------------------------
Plan hash value: 3803407550

--------------------------------------
| Id  | Operation             | Name  |
--------------------------------------
|   0 | SELECT STATEMENT      |       |
|   1 |  SORT ORDER BY        |       |
|   2 |   PARTITION RANGE ALL |       |
|   3 |    TABLE ACCESS FULL  | SALES |
--------------------------------------


Administração e evolução

Após as baselines serem criadas para uma instrução SQL específica, você pode examiná-las clicando nos nomes dos planos associados na tela do Oracle Enterprise Manager mostrada na Figura 1 (Oracle Enterprise Manager -> página SQL Plan Control -> guia SQL Plan Baseline) e verificando os detalhes do plano. Se um plano específico nunca vai ser bom, você pode desativá-lo completamente clicando no botão Disable. Você poderá clicar no botão Enable se mudar de ideia posteriormente. O botão Drop elimina completamente um plano da base de gerenciamento de SQL. Observe que se um plano não for usado, será depurado automaticamente após o período de retenção.

Se suspeitar que um plano na baseline atual não for ideal e que um plano diferente do histórico pode ser melhor, você pode comparar o desempenho dos planos usando a função Evolve (no Oracle Enterprise Manager -> página SQL Plan Control -> guia SQL Plan Baseline ou usando a função
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE a partir da linha de comando). Para usar a evolução, na tela do Oracle Enterprise Manager mostrada na Figura 1, selecione o plano que deseja comparar e clique no botão Evolve. A comparação é feita entre o plano escolhido como melhor pelo otimizador e o plano que você selecionar. A função produz um relatório, mostrado na Listagem 3. Observe esta linha no topo do relatório:

Listagem 3 do código: relatório de evolução da baseline 

-----------------------------------------------------
Evolve SQL Plan Baseline Report
-----------------------------------------------------

Inputs:
----
  PLAN_LIST   = SYS_SQL_PLAN_27a47aa15003759b
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY        = YES
  COMMIT       = YES


Plan: SYS_SQL_PLAN_27a47aa15003759b
----------------------
  Plan was verified: Time used 41.06 seconds.
  Failed performance criterion: Compound improvement ratio < .36


                       Baseline Plan       Test Plan   Improv. Ratio
                       --------------      ---------   ------------- 
  Execution Status:          COMPLETE       COMPLETE
  Rows Processed:                   0              0
  Elapsed Time(ms):              5036           1033         4.88
  CPU Time(ms):                   254            700          .36
  Buffer Gets:                   1728          43945          .04
  Disk Reads:                     254             22        11.55
  Direct Writes:                    0              0
  Fetches:                         49             22         2.23
  Executions:                       1              1


--------------------------------------------------------------------
Report Summary
--------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
 
Failed performance criterion: 
Compound improvement ratio < .36. 

A linha mostra claramente que o plano recém-considerado teve desempenho inferior ao do plano original, portanto foi rejeitado como substituto para a opção de melhor plano do otimizador. Se o índice de comparação tivesse gerado um fator de melhoria maior do que 1, o gerenciamento de planos de SQL teria aceitado esse plano como candidato para o otimizador considerar.

E se você sentir que a decisão tomada pela função Evolve não for precisa e preferir forçar o otimizador a usar um plano específico? Isso é possível tornando o plano fixo na baseline. Você pode tornar um plano fixo executando a função alter_sql_plan_baseline no pacote dbms_spm, conforme mostrado na Listagem 4.

Listagem 4 do código: fixando uma baseline de plano 

declare
   l_plans pls_integer;
begin
   l_plans := dbms_spm.alter_sql_plan_baseline (
      sql_handle         => 'SYS_SQL_f6b17b4c27a47aa1',
      plan_name         => 'SYS_SQL_PLAN_27a47aa15003759b',
      attribute_name   => 'fixed',
      attribute_value  => 'YES'
   );
end;

-- Now examine the plan:
SQL> explain plan for select * /* ARUP */ from sales
  2  where quantity_sold > 1 order by cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));

Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX |
--------------------------------------------------------------

Pelo resultado, você pode ver que o novo plano usou o índice SALES_PROMO_BIX em vez do SALES_TIME_BIX usado no plano anterior (e mostrado na Listagem 2). Agora o novo plano será fixado.

Onde você pode usar planos fixos? Suponha que o plano para uma instrução SQL não seja ideal, como um plano que esteja usando o índice SALES_PROMO_BIX enquanto o índice SALES_TIME_BIX seria mais eficiente, mas você não possa mudar o código para colocar dicas. Nesse caso, você pode seguir estes passos:

1. Em uma sessão diferente, mude o parâmetro optimizer_mode para o valor que produza o plano desejado, conforme mostrado na Listagem 1.

2. Execute a instrução SQL, capture a baseline como na Listagem 1 e desconecte a sessão.

3. Marque o plano, usado o índice SALES_TIME_BIX como fixo, conforme mostrado na Listagem 4. Lembre-se de substituir o handle SQL e o nome do plano pelo que eles são no seu caso.

Após o plano ser marcado como fixo, a instrução SQL usará somente esse plano e não aquele gerado pelo otimizador. Se houver mais de um plano fixo, o otimizador selecionará o melhor de todos.

Você também pode usar a mesma técnica para garantir caminhos de execução estáveis para as instruções SQL durante os upgrades de banco de dados. Primeiro você coleta baselines para todas as instruções SQL do banco de dados definindo o parâmetro do sistema optimizer_capture_sql_plan_baselines como verdadeiro e marca apenas um plano como fixo para cada uma das instruções SQL críticas. Em seguida, gradualmente você deve “desfixar” os planos e usar a função Evolve para verificar se há algum outro plano ideal. Se um plano gerado posteriormente pelo otimizador for pior, você sempre poderá reverter para o plano fixo anterior.

Conclusão

Stored outlines também tornam um plano estável, mas ele fica rígido. O otimizador identifica que há um outline para uma instrução SQL e para de gerar novos planos. As baselines, por outro lado, nunca interrompem a geração de novos planos pelo otimizador.

O recurso de gerenciamento de planos de SQL lhe permite armazenar planos validados ou bem conhecidos para as instruções SQL na forma de baselines, o que pode ser muito útil no diagnóstico de degradações repentinas de desempenho. Como as baselines (e os planos correspondentes) ficam armazenados em um repositório, você também pode compará-los e decidir usá-los da forma mais eficiente.


 


Arup Nanda ( arup@proligence.com ) é DBA Oracle há mais de 14 anos, lidando com todos os aspectos de gerenciamento de banco de dados – como ajuste de performance, segurança e recuperação de catástrofes, dentre outros. Foi eleito DBA do Ano da Oracle Magazine em 2003.