Blog do Marcio Portes

Para melhor entendimento deste artigo, lei primeiro este

Size é o número máximo de buckets definido para cada coluna.

O que é um bucket?
É uma "caixinha" (registro/coluna) onde o Oracle organiza as informações que pertence ao grupo de valores distintos. Ou seja, para cada valor distinto, o Oracle tem condição para decidir sobre o plano de execução (CBO).

Qual o critério para definir size?
Size deveria ser o número de valores distintos do agrupamento. No exemplo dos torcedores eu tinha 5 valores distintos, lembra? São Paulo, Corinthians, Palmeiras, Santos e o VOCEM. Então ficou fácil não? Size = 5.

Mas e se em nosso conjunto entrasse alguém que torce para o Juventos. Opa, problema! Se eu voltasse a analizar e informar size menor que o número de valores distintos, a coleta de estatística vai fazer o melhor possível para calcular as informações dentro dos buckets, porém nem sempre é o bastante. Portanto, quando for definir histograma, é preciso pensar bem o quanto o número de valores distintos vai crescer ou mudar.

Para coletar a estatística e o size devido, eu deixo o Oracle fazer o trabalho. Eu particularmente uso o seguinte:

begin
dbms_stats.gather_table_stats
(
 user, 'T',
 method_opt => 'for columns x'
 cascade => true
)
end;
/

Porque dessa forma, estarei seguro de que o dbms_stats não vai coletar estatística a mais de onde não quero. Informo o nome da coluna e deixo que ele calcule o numero de buckets necessários. Não uso o SIZE AUTO porque o cálculo é baseado em workload da coluna além dos valores distintos (pelo menos na versão 9i não tive ganho quando usei esta cláusula) então, deixo como demonstrado acima.

E agora, quando usar histogramas?
Sempre que NÃO exista uniformidade na distribuição de valores sobre o grupo. Voltando ao exemplo dos torcedores, se em nosso conjunto não tivéssemos os torcedores do VOCEM, não seria interessante usar histogramas, porque não ganharíamos nada, haveria necessidade de estudar melhor os dados e talvez usar bitmap, cluster, etc. Tudo depende, mas se há distruibuição uniforme no grupo ou constante variação de valores distintos, a coluna não é candidata ao histograma.
Outra ocasião onde deveríamos evitar o histograma é quando a coluna é comparada com bind variable. De novo o exemplo, se o campo torce_para for comparado com bind variable, ele não é candidado a histogramas. O otimizador precisa conhecer o valor literal da comparação para percorrer os buckets(endpoint_number, endpoint_value).
Ex.

where torce_para = 'VOCEM'     - usará o histograma
where torce_para = 'SAO PAULO' - usará o histograma
where torce_para = :time       - NÃO usará histograma.
               (9ir2 confirmado) - ainda não fiz o teste na 10gr2

Adendo 12/12/2005
Quando usamos bind-variable, a partir da versão 9i foi introduzido o peeking para minimizar o efeito no uso de histogramas com distribuição de dados irregular através de bind-variable. Eu, particularmente, ainda prefiro manter literal qual eu sei que isso irá beneficiar o uso de índice. Em uma próxima oportunidade comentarei o uso do peeking (vantagens e desvantagens).
Fim adendo

Onde eu investigo mais?
No exemplo abaixo, a tabela T tem duas colunas a e x, onde a é sequencial de 1 a 10000 e x tem valores distintos de 1, 2, 3, 4, 5, 9992, 9993, 9994, 9995 e 9996. A distribuição é a seguinte:

            X      COUNT(*)
------------- -------------
            1             1
            2             1
            3             1
            4             1
            5          9991
         9992             1
         9993             1
         9994             1
         9995             1
         9996             1

10 rows selected.

Então podemos notar que o para x=5 deveríamos fazer FTS e para os demais usar índice. Sabemos também que x tem 10 valores distintos. Porém, não vamos informar ao Oracle o size 10, vamos deixá-lo descobrir sozinho.

SQL> begin
  2    dbms_stats.gather_table_stats(
  3        user, 'T',
  4        cascade => true,
  5        method_opt => 'for columns x');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> col column_name format a15
SQL>
SQL> select column_name, endpoint_number, endpoint_value,
  2         endpoint_number - nvl(lag(endpoint_number) over (order by endpoint_value),0) qdt_rows
  3    from user_histograms
  4   where table_name = 'T';

COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE      QDT_ROWS
--------------- --------------- -------------- -------------
X                             1              1             1
X                             2              2             1
X                             3              3             1
X                             4              4             1
X                          9995              5          9991
X                          9996           9992             1
X                          9997           9993             1
X                          9998           9994             1
X                          9999           9995             1
X                         10000           9996             1

10 rows selected.

Como esperado, 10 buckets e para ajudar-nos a entender como as informações são dispostas a coluna QDT_ROWS fiz justamente subtraindo o valor anterior do endpoint_number para saber a quantidade de registros distribuídos (Notaram que é idêntico ao count(*) agroupado por x) - Dessa forma o Oracle sabe onde a literal está e calcula se é vantajoso o uso de FTS ou Índice.

Agora vamos aos testes:

SQL> set autotrace traceonly
SQL> select * from t where x = 3;

1 row selected.


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=17)
2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)

SQL> select * from t where x = 5;

9991 rows selected.


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9991 Bytes=169847)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=9991 Bytes=169847)

Perfeito, como esperado, x=5 FTS e x=3 Índice.
Um último teste para provar que se utilizamos bind variable, o histograma é inútil.

SQL> var n number
SQL> exec :n := 3

PL/SQL procedure successfully completed.

SQL> select * from t where x = :n;

1 row selected.


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=17000)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=17000)

ops$mportes@FCCUAT9I> exec :n := 5

PL/SQL procedure successfully completed.

ops$mportes@FCCUAT9I> select * from t where x = :n;

9991 rows selected.


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=17000)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=17000)

Full Table Scan nos 2 casos.
Para leitura complementar sugiro o metalink docs:
1031826.6
72539.1

E para quem quiser reproduzir o teste acima eu usei o seguinte script

set echo on
drop table t;

create table t nologging as
with
v as ( select level l
     from dual connect by 1=1 and level <= 10000 )
select l a, 5 x from v
/
commit;
update t
set x = rownum
where rownum <= 4 and x = 5;

update t
set x = 9991+rownum
where rownum <= 5 and x = 5;

select x, count(*)
from t
group by x
/

create index t_idx on t (x);

begin
dbms_stats.gather_table_stats(
  user, 'T',
  cascade => true,
  method_opt => 'for columns x');
end;
/
col column_name format a15

select column_name, endpoint_number, endpoint_value,
   endpoint_number - nvl(lag(endpoint_number) over (order by endpoint_value),0) qdt_rows
from user_histograms
where table_name = 'T';


set autotrace traceonly
select * from t where x = 3;
select * from t where x = 5;

var n number
exec :n := 3
select * from t where x = :n;
exec :n := 5
select * from t where x = :n;
set autotrace off
set echo off

Labels: how to