A coleta de estatísticas no banco de dados é muito importante para o bom desempenho dos SQLs que são executados.
As estatísticas coletadas, ajudam o otimizador do banco de dados a encontrar planos de execução mais eficientes e consequentemente, teremos um melhor desempenho desses SQLs.
A periodicidade dessas coletas vai depender muito da quantidade de dados que as tabelas possuem e de quanto esses dados são modificados. Se temos muita alteração dos dados, essa coleta devrá ser feita em períodos mais curtos.
O Oracle possui uma coleta automática de estatística para o banco de dados.
Essa coleta é feita durante à noite, em um período onde o banco esteja "ocioso", mas em casos de bancos 24X7 que tenham acesso o tempo todo, essa coleta não é realizada e devemos fazer esse procedimento manualmente e/ou agendar um JOB para execução periódica da coleta de estatísticas.
Vale ressaltar, que em alguns casos, a coleta de estatística pode deixar a execução de um SQL mais lento do que ele era a algum tempo. Isso não é uma regra, mas pode acontecer e já tive essa experiência, onde um processamento que tinha uma duração de 2 dias passou para mais de uma semana após a coleta de estatística.
Claro que foi um caso excepcional onde as tabelas envolvidas possuem muitos dados e uma grande quantidade alteração, onde foi necessário encontrar o plano de execução anterior e configurá-lo para ser novamente utilizado pelo processamento, mas isso é assunto para outro dia.
Abaixo, segue uma lista de comandos que podemos utilizar para verificar as tabelas e índices no banco de dados, verificar a data da última coleta de estatística, realizar a coleta manualmente e verificar o seu andamento.
Estatísticas do Banco de dados:
Para coletar estatística de todos os objetos (tablas, índices,...) do banco de dados, podemos utilizar:
EXEC DBMS_STATS.GATHER_DATABASE_STATS
EXEC DBMS_STATS.GATHER_DATABASE_STATS(cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
E para coletarmos estatísticas do dicionário de dados:
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS
Estatísticas dos Schemas:
Podemos fazer a coleta de estatísticas do banco de dados por schema/usuário.
Dessa forma, podemos coletar a estatística de todas as tabelas / índices que pertencem a um usuário / schema específico.
Ex.:
Coletar estatística para todas as tabelas do schema/usuário ABCD:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ABCD', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'GLOBAL AND PARTITION');
Coletar estatística para todas as tabelas do schema/usuário ABCD, com paralelismo:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ABCD', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'GLOBAL AND PARTITION',degree=>8);
Estatísticas das Tabelas:
a) Listar as tabelas do banco de dados com a data da última coleta de estatística
SQL> select owner,table_name,last_analyzed from dba_tables;
b) Listar as tabelas do banco de dados ordenado pela data da última coleta de estatística
SQL> select owner,table_name,last_analyzed from dba_tables by last_analyzed;
c) Listar as tabelas de um determinado schema / usuário com a data da última coleta de estatística
SQL> select owner,table_name,last_analyzed from dba_tables where owner ='ABCD';
d) Listar as tabelas de um determinado schema / usuário ordenado pela data da última coleta de estatística
SQL> select owner,table_name,last_analyzed from dba_tables where owner ='ABCD' order by 3;
Verificar a estatística das tabelas
a) Verificar tabelas especificas:
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') FROM DBA_TABLES WHERE TABLE_NAME IN ('ABC','XYZ','1234');
b) Verificar todas as tabelas do banco de dados:
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') FROM DBA_TABLES;
Coletar a estatística de uma tabela especifica
a) Informando o percentual de coleta:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABCD', TABNAME=>'ABCD_123', ESTIMATE_PERCENT=>10, granularity=>'ALL');
b) Deixando o oracle decidir a coleta:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABCD', TABNAME=>'ABCD_123', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt =>'FOR ALL COLUMNS SIZE AUTO');
c) Definindo paralelismo na coleta:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABCD', TABNAME=>'ABCD_123', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt =>'FOR ALL COLUMNS SIZE AUTO',degree=>8);
Obs.: A Oracle recomenda na versão 11g do banco de dados, a utilização dos parâmetros :
ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt =>'FOR ALL COLUMNS SIZE AUTO'
Estatísticas dos índices:
a) Listar os índices do banco de dados com a data da última coleta de estatística
SQL> select owner,index_name,last_analyzed from dba_indexes;
b) Listar os índices do banco de dados com ordenado pela data da última coleta de estatística
SQL> select owner,index_name,last_analyzed from dba_indexes order by last_analyzed;
c) Listar os índices de um determinado schema / usuário com a data da última coleta de estatística
SQL> select owner,index_name,last_analyzed from dba_indexes where owner ='ABCD';
d) Listar os índices de um determinado schema /usuário ordenado pela data da última coleta de estatística
SQL> select owner,index_name,last_analyzed from dba_indexes where owner ='ABCD' order by 3;
Verificar a estatística das tabelas de índices:
a) Verificar índices de usuário/schema específico:
SQL> SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT", LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL", AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY" FROM DBA_INDEXES WHERE owner = 'ABCD' ORDER BY INDEX_NAME;
b) Verificar todos os índices do banco de dados:
SQL> SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT", LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL", AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY" FROM DBA_INDEXES ORDER BY INDEX_NAME;
Coletar a estatística de um índice específico
a) Informando o percentual de coleta:
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'ABCD', INDNAME=>'ABCD_I', ESTIMATE_PERCENT=>10, granularity=>'ALL');
b) Deixando o oracle decidir a coleta:
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'ABCD', INDNAME=>'ABCD_I', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt =>'FOR ALL COLUMNS SIZE AUTO');
c) Definindo paralelismo na coleta:
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'ABCD', INDNAME=>'ABCD_I', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt =>'FOR ALL COLUMNS SIZE AUTO',degree=>32);
Verificar a estatística de colunas
a) Verificar estatística de Colunas de tabelas especificas:
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME ="ABCD" ORDER BY COLUMN_NAME;
b) Verificar estatística de todas as Colunas do banco:
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY FROM DBA_TAB_COL_STATISTICS ORDER BY COLUMN_NAME;
Verificar execução da coleta de estatística
Aqui, podemos verificar o tempo de andamento da execução da coleta de estatística e também o tempo que falta para sua conclusão:
SQL> SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK and OPNAME like '%Partition Statistics%' order by 1;
Verificar tabela com estatísticas bloqueadas
Com o select abaixo, podemos identificar as tabelas que estão marcadas para não terem a coleta de estatística executada.
Quando temos o parâmetro STATTYPE_LOCKED definido como ALL na tabela dba_tab_statistics, não conseguimos fazer a coleta de estatística para essas tabelas.
Antes de alterarmos seu status, precisamos identificar o motivo desse status (ALL) estar configurado.
SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED from dba_tab_statistics where STATTYPE_LOCKED='ALL' order by 1
Views envolvidos nas coletas de estatísticas:
DBA_TABLES
DBA_TAB_COL_STATISTICS
DBA_INDEXES
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_TAB_COLUMNS
INDEX_STATS
Referencias:
http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41448
https://docs.oracle.com/cd/A97630_01/server.920/a96533/stats.htm
How to Gather Optimizer Statistics on 11g [ID 749227.1]