Olá pessoal!
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
-
Níveis
- 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.
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:
A sintaxe
para coletar estatísticas de uma tabela é a seguinte:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OWNER', TABNAME=>'TABELA', ESTIMATE_PERCENT=>20);
A sintaxe
para coletar estatísticas de um esquema é a seguinte:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OWNER',ESTIMATE_PERCENT=> 20);
A sintaxe
para coletar estatísticas de toda a instância é a seguinte:
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Para coletar
estatísticas de sistema (dicionário de dados):
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
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.
Até a próxima!
Referências:
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.
