Social Icons

2 de abr. de 2012

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:

19 comments:

  1. Fabio, otimo artigo. Entendi melhor como coletar as estatistica do BD.
    Obrigado.

    ResponderExcluir
  2. Fabio, execute os comando, mas as estatisticas não serão visualizadas??? Como que visualizo se meus objetos esta sendo coletados as estatistica.
    Obrigado

    ResponderExcluir
    Respostas
    1. Alessandro, para este caso é só consultar a coluna LAST_ANALYZED da visão DBA_TABLES, ok?

      Se quiser saber se dados estão sendo atualizados na tabela (insert, update ou delete), consulte a visão DBA_TAB_MODIFICATIONS.

      []s

      Excluir
  3. Fábio eu atualizo as estatísticas ANALYZE INDEX xxx VALIDATE STRUCTURE.
    Consulto a visão SELECT * FROM INDEX_STATS where name = xxxx, como anlizo o momento certo q fazer o rebuild dos ínidces?
    Desde já agradeço!!!

    ResponderExcluir
    Respostas
    1. Anônimo, conforme já respondido via e-mail, a resposta para essa questão em ensino no capítulo 7 das vídeoaulas que vc adquiriu!

      []s

      Excluir
  4. Olá Fabio,
    Dias atrás coletei as estatísticas de uma tabela do oracle 9i com o dbms_utility.analyze_table(...,compute). Agora as operações de update e delete estão muito muito muito lentas. Essa lentidão pode ser fruto do analyze, mesmo as estatísticas sendo utilizadas para performar operações de busca? Se sim, como posso resolver essa questão?
    Desde já agradeço

    ResponderExcluir
    Respostas
    1. Mariângela, vc viu que eu falei neste artigo que o DBMS_STATS é mais eficiente para o CBO?

      Colete estatísticas novamente usando o package DBMS_STATS, ok?

      []s

      Excluir
    2. Olá Fabio, vi sim, mas eu já havia feito a coleta.
      Certamente vou mudar o método de coletar as estatísticas, conforme você explicou.
      Minha dúvida principal é se isso que foi feito realmente pode afetar o desempenho de update e delete.
      Muito obrigada!
      []'s

      Excluir
    3. Se o update e delete possuem cláusula WHERE, o que é bem provável, e se nas colunas utilizadas no(s) filtro(s) existem índices, sim, o desempenho será afetado!

      Excluir
  5. Fábio,
    Quando é feito uma sincronizaçao de bases (pega da produção e restaura na homologação) sendo a capacidade do hardware da holmologação inferior ao da produçao, quais são as recomendaçoes com relaçao às estatisticas?
    Elas são recalculadas durante o restore na homologação ou se faz necessario intervir manualmente para executar o processo de coleta?

    ResponderExcluir
    Respostas
    1. Mateus, a capacidade da máquina não influencia nas estatísticas de objetos, portanto, isso não importa ao levar os dados de produção para homologação. O que importa é que talvez no ambiente de homologação você tenha especificações de armazenamento diferentes no tablespace, e se tiver, aí sim você precisaria atualizar estatísticas no ambiente de homologação após o restore. Procurei algo na documentação da Oracle que informa o que acontece neste caso após o restore, com relação às estatísticas, mas ainda não encontrei. Por via das dúvidas, até não termos uma resposta concisa sobre o assunto, sugiro efetuar uma coleta manual após o restore, ok?
      []s

      Excluir
  6. Fábio,
    Primeiramente, parabéns pelo artigo, muito bom mesmo.
    Agora, me corrija se eu estiver errado, estou com o 11.2.0.4, e a partir do 10G, devemos deixar que as coletas sejam feitas de forma automática, salvo em casos onde alguma tabela sofra alguma carga de dados? Pergunto, pois hoje, eu coleto as estatísticas de algumas instâncias e outras, estão na forma automática.

    ResponderExcluir
    Respostas
    1. Obrigado pelo comentário Márcio!
      A resposta é: deixe o automático se ele estiver funcionando bem! Adicionalmente, até o 11G, você precisará também fazer coletas manuais após determinadas operações no Bd, como por exemplo, após a carga de dados que você mencionou, ok?

      Excluir
  7. Fábio, primeiramente parabéns pelo seu trabalho.

    Uma dúvida: Como altero essa configuração do Oracle para 10% de alterações nas tabelas?

    Muito Obrigado!

    ResponderExcluir
    Respostas
    1. Isso só é possível somente a partir do 11G. Veja os exemplos abaixo:

      a) No nível da tabela: EXEC DBMS_STATS.SET_TABLE_PREFS('ECOMMERCE','PEDIDO','STALE_PERCENT','5');
      b) No nível de schema: EXEC DBMS_STATS.SET_SCHEMA_PREFS('ECOMMERCE','STALE_PERCENT','5');
      c) No nível do BD: EXEC DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT',20);

      Falo mais detalhes disso tudo nos treinamentos de SQL Tuning que leciono na Oramaster (www.oramaster.com.br)

      Excluir
  8. Parabéns pelas explicações precisas. Onde até mesmo iniciantes podem entender

    ResponderExcluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)