Coletando estatísticas para o otimizador de queries do Oracle

ATUALIZADO EM 09/02/2017

Olá pessoal,

  

     Como muitos alunos me perguntam sobre o tema, resolvi escrever no artigo de hoje sobre como coletar estatísticas de objetos do Banco de Dados para o otimizador de queries do Oracle, considerando os métodos atuais existentes e as principais diferenças entre eles.

     Até o Oracle Database 7, só existia um tipo de otimizador, que era o Otimizador Baseado em Regras (RBO). Não vou entrar em mais detalhes sobre o RBO neste artigo, pois ele já está obsoleto nas versões atuais do Oracle. A partir da versão 7 foi criado outro tipo de otimizador, o Otimizador Baseado em Custo (CBO), e a partir da versão 11G, somente o CBO pode ser utilizado (na versão 10G ele ainda existia somente para manter compatibilidade com versões anteriores). O CBO foi criado com o objetivo de melhorar a performance da execução das instruções SQL (em relação ao RBO,) criando planos de execução mais dinâmicos que se baseiam em custo, ao invés de regras. 

     Para verificarmos uma amostra de que o CBO normalmente é melhor que o RBO nos SGBDs atuais, encontrei a monografia de "Especialização em Administração de Banco de Dados" de Carlos Eduardo Borges, aluno da PONTIFÍCIA UNIVERSIDADE CATÓLICA DO PARANÁ. Neste trabalho ele apresenta vários testes e gráficos comparando a performance do CBO X RBO em diversas consultas executadas em um BD Oracle Database 10g Enterprise Edition Release 10.2.0.1.0. 

     Segue abaixo um gráfico (ver Figura 1) que demonstra um comparativo de tempo de execução de uma consulta executada em uma tabela que tinha as seguintes características:                

          - Contém uma coluna PK e mais 3 colunas com distribuição de valores uniforme, contendo 3.020.730 linhas;
          - 1 índice composto criado em 2 colunas da tabela, onde 1 dessas colunas é utilizada como filtro da instrução SQL.

Figura 1 - Gráfico comparativo de tempo "RBO X CBO"

Obs.: De acordo com o gráfico da Figura 1, no CBO o tempo de execução foi 44,4% menor, pois nesta consulta o CBO foi capaz de utilizar o índice composto e o RBO não! Para mais detalhes, sugiro consultar os links da seção de referências deste artigo.

 
     Para montar um plano de execução, o CBO baseia-se, resumidamente, em estatísticas de objetos (quantidade de linhas, cardinalidade, seletividade) e custo de hardware (memória, cpu, I/O). Para que ele monte planos de execução otimizados, é necessário que as estatísticas dos objetos estejam sempre atualizadas. Para atualizar as estatísticas dos objetos, podemos usar os métodos abaixo:

     1- Comando ANALYZE:

          - Calcula estatísticas globais de tabelas, índices e clusters;

          - Permite coletar estatísticas exatas ou estimada em um número ou percentual de linhas;

          - Não é tão preciso ao calcular, por exemplo, a cardinalidade, ao envolver valores distintos;

          - Devido ao fato de não ser muito preciso, não é recomendado para coletar estatísticas para o CBO, mas pode ser útil para coletar informações sobre linhas encadeadas e blocos livres;

          - Era bastante eficiente até a versão 7 do Oracle Database ou para o RBO. É suportado na versões atuais do Oracle somente para manter a compatibilidade com as versões anteriores;          

          - Exemplo p/ coletar estatísticas exatas de uma tabela: 

               ANALYZE TABLE TABELA COMPUTE STATISTICS;

     2- Package DBMS_UTILITY:

          - As procedures desta package diferem do comando ANALYZE apenas pela possibilidade de permitir coletar estatísticas de um schema ou do banco de dados completo;

          - Exemplo p/ coletar estatísticas de um schema todo:

               EXEC DBMS_UTILITY.ANALYZE_SCHEMA('OWNER','COMPUTE');

   

     3- Package DBMS_STATS:

          - Foi introduzido no Oracle 8i e hoje é o método mais eficiente para coletar estatísticas para o CBO;

          - Permite coletar estatísticas exatas ou estimadas de objetos individualmente (tabelas, índices, cluster etc), schemas, banco de dados completo e de sistema;

          - Permite execução paralela, transferência de estatísticas entre servidores e é mais preciso que os métodos anteriores;

          - Gera histogramas, que são extremamente úteis para otimizar queries que efetuam pesquisas em colunas que possuem valores distribuídos não uniformemente;

          - É o método de coleta de estatísticas atualmente recomendado pela Oracle e por especialistas no assunto;

          - Exemplos:

              a) Para coletar estatísticas estimadas (1%) de uma tabela:

                 EXEC DBMS_STATS.GATHER_TABLE_STATS(

OWNNAME=>'OWNER', TABNAME=>'TABELA', ESTIMATE_PERCENT=>1);  

              b) Para coletar estatísticas estimadas (20%) de um schema:

                 EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OWNER', estimate_percent=> 20);

              c) Para coletar estatísticas de todo o banco de dados: 

               EXEC DBMS_STATS.GATHER_DATABASE_STATS;

  

              d) Para coletar estatísticas de sistema (DD): 

               EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

  

    

       Dicas para quem pretende coletar estatísticas de objetos:

  

        1- A partir do Oracle Database 10G, as estatísticas de objetos são coletadas automaticamente pelo Oracle, diariamente de 2ª à 6ª, em um horário compreendido geralmente entre 22h e 2h, e aos sábados começa às 6h e termina somente no Domingo, às 2h. É importante ressaltar que ela só ocorre nos objetos que tiveram mais que 10% de atualizações (inclui INSERT, UPDATE e DELETE). A partir do 11G, esse valor de 10% é configurável.

           Pelo motivo dela ocorrer automaticamente, colete estatísticas manualmente somente quando você identificar alguma necessidade extra, como por exemplo, após uma carga de dados ou em BDs que trabalham 24X7 e que nunca ficam ociosos. Nestes casos, recomendo criar uma stored procedure contendo o código para coletar estatísticas de objetos do BD, e criar em seguida, um scheduler job para executar esta procedure periodicamente;

  

        2- Se o seu BD usa o CBO, evite coletar estatísticas através do comando ANALYZE TABLE e através da package DBMS_UTILITY.Se você fizer isso, suas estatísticas serão menos precisas para o CBO e você não terá histogramas;

        3- Estatísticas desatualizadas são inimigas de boa performance. Aprenda a verificar se os seus objetos estão com as estatísticas atualizadas (apenas o valor da coluna LAST_ANALYZED da visão DBA_TABLES não  é suficiente para determinar isso). Existem muitas variantes que podem influenciar na execução da coleta de estatísticas e de como verificar se os objetos estão atualizados, mas estes itens eu guardo para apresentar nos treinamentos de SQL Tuning;

        4- É importante verificar também se as estatísticas de sistema e do DD estão atualizadas. Para mais informações consulte o post Coletando Estatísticas de sistema para otimizar o seu Banco de Dados.

Por hoje é só pessoal!

[]s

Referências:

   - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525

   - http://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm

   - http://www.oracle-base.com/articles/misc/CostBasedOptimizerAndDatabaseStatistics.php

   - http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm
   - http://www.geocities.ws/carlos.gorges/Monografia-CarlosGorges.pdf