Márcio, eu li a entrada sobre SIZE do seu blog e tenho um item a
adicionar, vou faze-lo aqui pra que mais gente se eventualmente se
beneficie. É o seguinte, quando vc não informa o SIZE, ** não ** é
sempre que o "Oracle faz o trabalho" - o que ocorre é que, SE o número
de valores distintos for nitidamente menor que o número de buckets
mínimo do banco (por volta de uns 70), isso funciona bem, mas se não
for isso, vc pode ter problemas. Exemplo :
Conectado a:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data
Mining options
JServer Release 9.2.0.5.0 - Production
[EMAIL PROTECTED]:SQL>create table TEST (n1 number not null);
Tabela criada.
==> vamos ter apenas 4 valores distintos, pra início
[EMAIL PROTECTED]:SQL>BEGIN
2 FOR I IN 1..5 LOOP
3 INSERT INTO TEST (SELECT I FROM ALL_OBJECTS WHERE ROWNUM <
I*I);
4 COMMIT;
5 END LOOP;
6 END;
7 /
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>exec dbms_stats.gather_table_stats(user, 'TEST',
cascade =>TRUE, method_opt=>'for columns n1');
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>select count(*) from user_histograms where
table_name='TEST';
COUNT(*)
------------------
4
[EMAIL PROTECTED]:SQL>SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE FROM
USER_TAB_COLUMNS WHERE TABLE_NAME='TEST';
COLUMN_NAME NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------ ------------------ ------------------
------------------
N1 4 C103
C106
,01
0 3 50
[EMAIL PROTECTED]:SQL>SELECT MIN(N1), DUMP(MIN(N1), 16) , MAX(N1),
DUMP(MAX(N1), 16) FROM TEST;
[EMAIL PROTECTED]:SQL>/
MIN(N1)
------------------
DUMP(MIN(N1),16)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX(N1)
------------------
DUMP(MAX(N1),16)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
Typ=2 Len=2: c1,3
5
Typ=2 Len=2: c1,6
==> ok, vamos usar :
[EMAIL PROTECTED]:SQL>set autotrace traceonly;
[EMAIL PROTECTED]:SQL>select * from test where n1=3;
8 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=8 Bytes=24)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=8 Bytes=24)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
443 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
[EMAIL PROTECTED]:SQL>set autotrace off
==> vamos recoletar com size maior :
[EMAIL PROTECTED]:SQL>exec dbms_stats.gather_table_stats(user, 'TEST',
cascade =>TRUE, method_opt=>'for columns n1 size 5');
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>select count(*) from user_histograms where
table_name='TEST';
COUNT(*)
------------------
4
[EMAIL PROTECTED]:SQL>SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE FROM
USER_TAB_COLUMNS WHERE TABLE_NAME='TEST';
COLUMN_NAME NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------ ------------------ ------------------
------------------
N1 4 C103
C106
,229230769230769
0 3 50
[EMAIL PROTECTED]:SQL>set autotrace traceonly
[EMAIL PROTECTED]:SQL>select * from test where n1=3;
8 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=11 Bytes=33)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=11 Bytes=33)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
443 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
==> cardinalidade passou de 8 pra 11, é ruído, diferença tão pequena
que é desprezível, teste IGUAIS na prática. AGORA vamos ter mais
valores distintos...
[EMAIL PROTECTED]:SQL>set autotrace off
[EMAIL PROTECTED]:SQL>truncate table test;
Tabela truncada.
[EMAIL PROTECTED]:SQL>BEGIN
2 FOR I IN 1..125 LOOP
3 INSERT INTO TEST (SELECT I FROM ALL_OBJECTS WHERE ROWNUM <
I*I);
4 COMMIT;
5 END LOOP;
6 END;
7 /
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>SELECT MIN(N1), DUMP(MIN(N1), 16) , MAX(N1),
DUMP(MAX(N1), 16) FROM TEST;
MIN(N1)
------------------
DUMP(MIN(N1),16)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX(N1)
------------------
DUMP(MAX(N1),16)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
Typ=2 Len=2: c1,3
125
Typ=2 Len=3: c2,2,1a
==> primeiro vamos coletar como vc sugeriu, sem informar bucket size :
[EMAIL PROTECTED]:SQL>exec dbms_stats.gather_table_stats(user, 'TEST',
cascade =>TRUE, method_opt=>'for columns n1');
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>select count(*) from user_histograms where
table_name='TEST';
COUNT(*)
------------------
64
=> ele fez o número mínimo de buckets - sempre há uma pequena variação
porque o histograma que o Oracle usa é height-based (por "altura"),
isto é, ele sempre quer a mesmo quantidade de valores distintos em
cada "caixa", mais 1 ou menos 1...
[EMAIL PROTECTED]:SQL>SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE FROM
USER_TAB_COLUMNS WHERE TABLE_NAME='TEST';
COLUMN_NAME NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------ ------------------ ------------------
------------------
N1 124 C103
C2021A
,0122118731219246
0 63 658750
==> atenção ao campo DENSITY acima... Vamos fazer uma query :
[EMAIL PROTECTED]:SQL>set autotrace traceonly
[EMAIL PROTECTED]:SQL>SELECT * FROM TEST WHERE N1=20;
399 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=8045
Bytes=32180)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=98 Card=8045 Bytes=32180)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
1032 consistent gets
491 physical reads
0 redo size
5571 bytes sent via SQL*Net to client
785 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
399 rows processed
==> humm, cardinalidade de 8405, tá. vamos agora ** indicar ** o
número de buckets para ser mais próximo do valor real de distintos
(qundo vc não informa o size, o bd Oracle ** não ** faz isso !!) :
[EMAIL PROTECTED]:SQL>set autotrace off
[EMAIL PROTECTED]:SQL>exec dbms_stats.gather_table_stats(user, 'TEST',
cascade =>TRUE, method_opt=>'for columns n1 size 125');
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>select count(*) from user_histograms where
table_name='TEST';
COUNT(*)
------------------
78
[EMAIL PROTECTED]:SQL>SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE FROM
USER_TAB_COLUMNS WHERE TABLE_NAME='TEST';
COLUMN_NAME NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------ ------------------ ------------------
------------------
N1 124 C103
C2021A
,00747264610999656
0 77 658750
==> humm, além de ter mais buckets, a DENSIDADE mudou (claro, os
valores estão mais distribuídos, por mais "caixas"). ok, vamos usar :
[EMAIL PROTECTED]:SQL>set autotrace traceonly
[EMAIL PROTECTED]:SQL>SELECT * FROM TEST WHERE N1=20;
399 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=4923
Bytes=19692)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=98 Card=4923 Bytes=19692)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
1032 consistent gets
519 physical reads
0 redo size
5571 bytes sent via SQL*Net to client
785 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
399 rows processed
===>> SANTO VALOR , Batman!! A mesma query, com os MESMOS dados, mas a
cardinalidade caiu quase pela METADE, de 8045 para 4923 !!! E é claro,
sabemos nós que a CARDINALIDADE é um item-CHAVE pro CBO tomar
decisões... Então, se isto fosse um JOIN, ** muito ** provavelmente o
plano IRIA mudar !! veja só, a ** mesma ** query, com os ** mesmos **
dados, só por ter sido informado mais detalhadamente a info
necessária, muda radicalmente...
Antes que neguinho pergunte, só pra mostrar que não tem ** nada ** a
ver com caches, ordem de execução, nada disso, faço de novo sem
informar size :
[EMAIL PROTECTED]:SQL>exec dbms_stats.gather_table_stats(user, 'TEST',
cascade =>TRUE, method_opt=>'for columns n1');
Procedimento PL/SQL concluído com sucesso.
[EMAIL PROTECTED]:SQL>select count(*) from user_histograms where
table_name='TEST';
COUNT(*)
------------------
64
[EMAIL PROTECTED]:SQL>SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE FROM
USER_TAB_COLUMNS WHERE TABLE_NAME='TEST';
COLUMN_NAME NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------ ------------------ ------------------
------------------
N1 124 C103
C2021A
,0122118731219246
0 63 658750
[EMAIL PROTECTED]:SQL>set autotrace traceonly
[EMAIL PROTECTED]:SQL>SELECT * FROM TEST WHERE N1=20;
399 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=8045
Bytes=32180)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=98 Card=8045 Bytes=32180)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
1032 consistent gets
576 physical reads
0 redo size
5571 bytes sent via SQL*Net to client
785 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
399 rows processed
[EMAIL PROTECTED]:SQL>
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, "Marcio Portes"
<[EMAIL PROTECTED]> escreveu
> Escrevi outra notinha lá.
>
> http://mportes.blogspot.com/2005/11/histograma-definio-size.html
>
> Abraço,
>
> --- Em oracle_br@yahoogrupos.com.br, "Rosiano Vieira de Sales"
> <[EMAIL PROTECTED]> escreveu
> > Marcio... dei uma lida no seu blog a respeito e gostei muito da
> nota que vc publicou ....contudo fiquei com uma dúvida ....qual o
> critério para definir o size do historgrama ??? .... se tiver algo a
> respeito envie para o grupo .. vai ser de muito proveito .....
> >
> > abç.
> >
> > Rosiano
> >
> > -----Mensagem original-----
> > De: oracle_br@yahoogrupos.com.br em nome de Marcio Portes
> > Enviada: qua 23/11/2005 15:06
> > Para: oracle_br@yahoogrupos.com.br
> > Cc:
> > Assunto: [oracle_br] Re: Histogramas.????
> >
> >
> >
> > Welvis, tomei a liberdade de escrever uma pequena
> introdução para sua
> > dúvida no meu blog.
> > Se interessar:
> > http://mportes.blogspot.com/2005/11/histograma-introduo.html
> >
> > abraços,
> >
> > --- Em oracle_br@yahoogrupos.com.br, Welvis Douglas Silva
> Moreto
> > <[EMAIL PROTECTED]> escreveu
> > > Quando devo estar utilizando Histogramas.... quais
> > > beneficios terei utilizando o.
> > >
> > >
> > > att,
> > >
> > > Welvis Douglas
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > _______________________________________________________
> > > Yahoo! Acesso Grátis: Internet rápida e grátis.
> > > Instale o discador agora!
> > > http://br.acesso.yahoo.com/
> >
> >
> >
> >
> >
> > --------------------------------------------------------------
> ------------------------------------------------------------
> > Atenção! As mensagens deste grupo são de acesso público e
> de inteira responsabilidade de seus remetentes.
> > Acesse: http://www.mail-
> archive.com/oracle_br@yahoogrupos.com.br/
> > --------------------------------------------------------------
> ------------------------------------------------------------
> _____________________________________________________________________
> > Area de download do grupo -
> http://www.4shared.com/dir/101727/a4dcc423
> > Links do Yahoo! Grupos
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > [As partes desta mensagem que não continham texto foram removidas]
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira
responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
--------------------------------------------------------------------------------------------------------------------------_____________________________________________________________________
Area de download do grupo - http://www.4shared.com/dir/101727/a4dcc423
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
