Estatísticas: Diferença de coleta de histogramas em AUTO e SKEWONLY

Na coleta de histogramas (opção METHOD_OPT da Procedure DBMS_STATS.GATHER_…_STATS) o padrão é FOR ALL COLUMNS SIZE AUTO.

De acordo com a documentação:
– AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
– SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.

Fonte: http://docs.oracle.com/database/122/ARPLS/DBMS_STATS.htm

O que isto significa? Que em AUTO só serão coletados histogramas em colunas que foram utilizadas no WHERE de algum SQL executado. Isto significa que se for executado um novo SQL que precise deste histograma, este só será criado na próxima coleta de estatísticas.

Abaixo eu crio uma tabela, que possui uma coluna que precisa de histogramas – a OBJECT_TYPE. Mas a coleta com AUTO não cria nenhum histograma. Após executar um SQL que utiliza esta coluna, uma nova coleta cria o histograma.

08:48:24 SCOTT@PROD> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

Elapsed: 00:00:17.97
08:48:53 SCOTT@PROD> SELECT HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OBJECT_TYPE';

HISTOGRAM
---------------
NONE

Elapsed: 00:00:00.68
08:49:42 SCOTT@PROD> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.26
08:49:46 SCOTT@PROD> SELECT HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OBJECT_TYPE';

HISTOGRAM
---------------
NONE

Elapsed: 00:00:00.01
08:49:49 SCOTT@PROD> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE = 'TABLE';

COUNT(OBJECT_TYPE)
------------------
              1613

Elapsed: 00:00:00.29
08:50:01 SCOTT@PROD> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.36
08:50:09 SCOTT@PROD> SELECT HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OBJECT_TYPE';

HISTOGRAM
---------------
FREQUENCY

Elapsed: 00:00:00.01

E agora vou fazer este mesmo teste em uma nova tabela, mas a coleta será com a opção SKEWONLY – o histograma é coletado de imediato, ates da execução de qualquer SQL.

08:50:12 SCOTT@PROD> DROP TABLE T;

Table dropped.

Elapsed: 00:00:04.52
08:50:40 SCOTT@PROD> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

Elapsed: 00:00:14.28
08:51:06 SCOTT@PROD> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.74
08:51:36 SCOTT@PROD> SELECT HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OBJECT_TYPE';

HISTOGRAM
---------------
FREQUENCY

Elapsed: 00:00:00.00
08:51:41 SCOTT@PROD>

2 comments

  1. Muito obrigado! Eu sempre tive essa dúvida mas nunca me aprofundei no caso. Lendo seu texto me pergunto como não me realizei disso antes.

    Em geral gosto de usar o SKEWONLY combinado com o REPEAT. O SKEWONLY para coletas mais pesadas e extraordinárias e o REPEAT para as coletas ordinárias.

    1. Para usar o REPEAT, acho que só com controle completo sobre os dados – por exemplo, o DBA sabe que nenhum produto novo foi adicionado na coluna PRODUCT_ID da tabela VENDAS.
      E para usar o AUTO, não é necessário ter controle sobre os dados, desde que eles tenham sido consultados por algum SQL. Ou seja, pode ficar lento até a próxima coleta.
      Já o SKEWONLY é potencialmente mais pesado, pois coletará mais histogramas, mesmo em colunas não consultadas. Deve ser utilizado em um banco novo / migrado.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.