Estatísticas no banco de dados Oracle ~ Dia a dia na T.I. [Vivência de um DBA]
Hoje quero falar um pouco sobre as estatísticas no banco de dados Oracle e a importância delas para o bom desempenho dos scripts submetidos ao banco.
Inicialmente, vou abordar os métodos existentes para realizar a coleta das estatísticas, com algumas características e tentar estabelecer uma comparação entre eles.
Lembrando que em um post anterior, comentei sobre como verificar se as estatísticas de sua instância Oracle foram atualizadas recentemente.
Vou fazer uma explicação rápida sobre plano de execução (isso porque pretendo fazer um post sobre o assunto em outro momento). O Plano de Execução de uma instrução SQL é uma seqüência de operações que o Banco de Dados Oracle realiza para executar uma instrução. O plano de uma instrução pode se alterar conforme o ambiente em que está sendo executado: versão do Oracle, hardware, parâmetros de servidor (sistema ou sessão), volume de dados e estatísticas. Esse último item é o ponto que quero focar no artigo de hoje.
Estatística é uma coleção de dados detalhados sobre o banco de dados e seus objetos. As estatísticas são usadas pelo otimizador de consultas do Oracle para escolher o melhor plano de execução a ser executado.
Abaixo, seguindo a definição da Oracle, seguem itens inclusos nas estatísticas:
- Para o caso de tabela:
- Número de linhas (registros)
- Número de blocos de dados
- Comprimento médio das linhas
- Para as colunas:
- Número de valores distintos na coluna (NDV – Number of Distinct Values)
- Número de colunas nulas
- Distribuição dos dados (histogramas)
- Estatísticas dos índices:
- Número de blocos folha
- Fator de agrupamento
- Estatísticas de sistema:
- Desempenho e utilização de I/O
- Desempenho e utilização de CPU
Entendendo quais informações são levadas em consideração nas estatísticas, torna de mais fácil entendimento a razão que elas auxiliam na escolha do otimizador do Oracle de "traçar" um plano de execução mais eficiente para acessar um dado (ou um conjunto de dados) da base.
Existem algumas maneiras de se atualizar as estatísticas, inclusive o próprio banco de dados Oracle já realiza diante de algumas circunstâncias, tais como modificação significativa na tabela (mais de 10% dos registros caso o objeto esteja habilitado para monitoramento). As formas de atualização das estatísticas são as seguintes:
Atualização através do comando ANALYZE:
A sintaxe para coletar estatísticas de uma tabela é a seguinte:
ANALYZE TABLE OWNER.TABELA COMPUTE STATISTICS;
Algumas características sobre essa estrutura de atualização das estatísticas, segundo Fábio Prado:
a. Coleta ou exclui estatísticas sobre índices, tabelas ou clusters; de forma exata ou estimada em um número ou percentual de registros;
b. Pode não precisar a informação de cardinalidade da tabela no que diz respeito a valores distintos nas colunas (NDV);
c. Foi bastante eficiente nas versões anteriores do banco de dados Oracle, mas nas versões atuais existem apenas para manter a compatibilidade.
Atualização através do pacote DBMS_UTILITY:
A sintaxe para coletar estatísticas de um esquema é a seguinte:
EXEC DBMS_UTILITY.ANALYZE_SCHEMA (OWNER,'ESTIMATE',99,20,'FOR TABLE');
ou pode-se atualizar as estatísticas de toda a instância através do commando:
EXEC DBMS_UTILITY.ANALYZE_DATABASE ('ESTIMATE',100,20,'FOR TABLE');
É importante lembrar que diferente do comando ANALYZE, o pacote DBMS_UTILITY pode coletar as estatísticas de um esquema ou de todo um banco de dados.
Atualização através do pacote DBMS_STATS:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OWNER', TABNAME=>'TABELA', ESTIMATE_PERCENT=>20);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OWNER',ESTIMATE_PERCENT=> 20);
A sintaxe para coletar estatísticas de toda a instância é a seguinte:
Para coletar estatísticas de sistema (dicionário de dados):
Algumas características sobre essa estrutura de atualização das estatísticas, também segundo Fábio Prado:
a. Foi introduzido no Oracle 8i e hoje é o método mais eficiente para coletar estatísticas para o CBO. É o método de coleta de estatísticas atualmente recomendado pela Oracle;
b. Permite coletar estatísticas exatas ou estimadas de objetos individualmente (tabelas, índices, cluster etc), esquemas, banco de dados completo e de sistema;
c. Permite execução paralela, transferência de estatísticas entre servidores e é mais preciso que os métodos anteriores pois gera histogramas, que são extremamente úteis para otimizar scripts SQL que acessam colunas com valores dispersos.
É importante saber que utilizar o pacote DBMS_STATS é a forma mais completa e justamente por esse motivo pode ser o método mais lento de coleta de estatística entre os apresentados. Para garantir um desempenho otimizado do seu banco, é de fundamental importância que as estatísticas dele estejam atualizadas. Lembro novamente que o Oracle realiza de forma automática a coleta das estatísticas diante de algumas situações (isso nas versões "mais recentes" do banco Oracle – a partir do 10g): diariamente caso as estatísticas estejam desatualizadas ou quando existir muita movimentação dos registros – cerca de 10% do total (configurável) – do objeto (que também significa que as estatísticas estão desatualizadas).
Bom pessoal. Por hoje é isso!
Espero ter conseguido passar o básico sobre coleta de estatísticas no BD Oracle.
ORACLE CORPORATION - MANAGING OPTIMIZER STATISTICS. Disponível em: http://docs.oracle.com/cd/B13789_01/server.101/b10752/stats.htm. Acessado em: 02/03/2013.
ORACLE CORPORATION - DBMS_STATS. Disponível em: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm. Acessado em: 02/03/2013.
PRADO, FÁBIO. Disponível em: http://www.fabioprado.net/2012/04/coletando-estatisticas-para-o.html. Acessado em: 17/03/2013.
DBPEDIAS. Disponível: http://dbpedias.com/wiki/Oracle:DBMS_UTILITY.ANALYZE_DATABASE. Acessado em: 02/03/2013.
DBPEDIAS. Disponível em: http://dbpedias.com/wiki/Oracle:DBMS_UTILITY.ANALYZE_SCHEMA. Acessado em: 02/03/2013.