Coletando estatísticas de tabelas no Oracle
Postado por Adauto Santos
A coleta de estatísticas é um aspecto fundamental para a performance de um Banco de Dados Oracle, isso se deve ao fato do CBO se basear nos resultados das estatísticas para montar o melhor plano de execução das consultas.
O padrão é que o processo de coleta ocorra durante a janela de manutenção estipulada para o Banco de Dados. Dessa forma, as informações referentes a tabela podem variar muito no decorrer do dia, conforme as operações DML forem sendo executadas. Entretanto, as estatísticas das tabelas também podem ser coletados manualmente pelos comandos listados a seguir.
O comando
Para coletar as informações referentes a uma tabela (e com isso os seus respectivos índices), você pode executar o utilitário DBMS_STATS.GATHER_TABLE_STATS. A quantidade mínima de parâmetros é o schema e o nome da tabela.
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','NOME_TABELA');
Para exemplificar o uso do comando criaremos uma tabela chamada CLIENTES e três índices (CLI_IDX01, CLI_IDX02 e CLI_IDX03), conforme pode ser visto a seguir.
CREATE TABLE CLIENTES ( CODIGO NUMBER, NOME VARCHAR2(50), EMAIL VARCHAR2(50), DATA_NASCIMENTO DATE, UF CHAR(2), CODIGO_MUNICIPIO_IBGE NUMBER(10), NR_CPF NUMBER(11), REGIAO_BRASIL CHAR(2), SEXO CHAR(1), DATA_EXCLUSAO DATE ); SQL> CREATE INDEX CLI_IDX01 ON CLIENTES(CODIGO); SQL> CREATE INDEX CLI_IDX02 ON CLIENTES(UF); SQL> CREATE INDEX CLI_IDX03 ON CLIENTES(SEXO);
Na sequência iremos coletar a estatística da tabela CLIENTES, com o comando que se segue:
BEGIN DBMS_STATS.GATHER_TABLE_STATS('ADAUTO','CLIENTES'); END;
Pronto! Após o término da coleta das estatísticas da tabela você pode consultar as visões do dicionário de dados DBA_TABLES e DBA_INDEXES para confirmar a execução do comando.
SQL> select table_name, last_analyzed from dba_tables dt where dt.table_name='CLIENTES'; TABLE_NAME LAST_ANALY ------------------------------ ---------- CLIENTES 07/11/2016 SQL> select index_name, last_analyzed from dba_indexes where table_name='CLIENTES'; INDEX_NAME LAST_ANALY ------------------------------ ---------- CLI_IDX01 07/11/2016 CLI_IDX02 07/11/2016 CLI_IDX03 07/11/2016
Uma outra forma de se coletar as estatísticas das tabelas é por meio do comando ANALYZE. Porém esse comando retorna resultados menos precisos e foi descontinuado pela Oracle e foi mantido apenas por compatibilidade em relação as versões mais novas.
ANALYZE TABLE NOME_TABELA COMPUTE STATISTICS;
A explicação
Conforme dito na sessão anterior as estatísticas não são coletadas em tempo real, sendo assim é provável que existam divergências entre as informações da estatística e a tabela de fato. Para isso, iremos duplicar os registros na tabela CLIENTES, com isso a tabela passará de 8 para 16 registros.
SQL> INSERT INTO CLIENTES (SELECT * FROM CLIENTES);
SQL> SELECT COUNT(*) FROM CLIENTES;
COUNT(*)
----------
16
Contudo, analisando a DBA_TABLES verificamos que a informação fornecida em NUM_ROWS permanece a mesma.
SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='CLIENTES'; TABLE_NAME NUM_ROWS LAST_ANALY ------------------------------ ---------- ---------- CLIENTES 8 07/11/2016
Dessa forma, é necessário executar o comando para que a quantidade de registros da tabela e as estatísticas da DBA_TABLES estejam alinhadas.
SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS','CLIENTES'); END;
SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='CLIENTES';
TABLE_NAME NUM_ROWS LAST_ANALY
------------------------------ ---------- ----------
CLIENTES 16 07/11/16
Dessa forma, é necessário executar o comando para que a quantidade de registros da tabela e as estatísticas da DBA_TABLES estejam alinhadas.
Um aspecto importante do comando DBMS_STATS.GATHER_TABLE_STATS é que ele pode ser bem complexo se analisarmos todos os seus parâmetros. Note, que apenas o nome do schema e da tabela são obrigatórios, contudo muitas outras podem ser utilizadas para tornar a coleta mais eficiente. Para estudá-las com maior profundidade sugiro que acesso a documentação da Oracle. Mas gostaria de abordar duas opções que julgo bem interessantes.
METHOD_OPT
Com esse parâmetro é possível selecionar apenas algumas colunas nas quais se deseja coletar as informações.
DBMS_STATS.GATHER_TABLE_STATS
(
'ADAUTO', 'CLIENTES', METHOD_OPT => 'FOR COLUMNS (CODIGO, UF)'
);
ESTIMATE_PERCENT
Com esse parâmetro é possível selecionar o percentual de leitura da tabela. O padrão é que o Oracle defina esse percentual, contudo ele pode ser selecionado para permitir uma estatística mas precisa.
DBMS_STATS.GATHER_TABLE_STATS
(
'ADAUTO', 'CLIENTES', ESTIMATE_PERCENT => '50'
);
Referências
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68486
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm
