Isso me lembra algo que o Jonathan Lewis fala sempre: Exemplo para explicar um conceito é uma coisa -- sempre funciona! Quando vamos para vida real a coisa muda de figura. Obviamente na grande maioria das vezes, fazemos exemplos redondinhos com o que queremos demonstrar e a partir dai, como ocorreu com esta thread, a coisa caminha e toma profundidade. Bom, o artigo é uma introdução e acho que serviu bem ao propósito. Também estou com tempo limitado, portanto, vou copiar e colar seu comentário como adendo em meu blog (se voce não se opuser) e quando tiver a necessidade de me aprofundar nisso em produção volto com o tema.
--- Em oracle_br@yahoogrupos.com.br, "jlchiappa" <[EMAIL PROTECTED]> escreveu > Eu já tive várias vezes oportunidade de precisar especificar mais > buckets do que o algoritmo default do banco precisa, principalmente > no cliente atual, que é um DW da área de telefonia , chamadas > telefônicas muitas vezes apresentam (principalmente em campos como > cidade origem/destino), mais que algumas dezenas de valores > distintos, e a distribuição costuma ser bem irregular, por isso que > lembrei ao ver o seu caso, acho que vale o toque pra quem for > implementar. Da mesma maneira, sugiro que vc altere outro ponto que > me ocorreu também, vc diz lá no texto que "histogramas são inúteis > com bind variables", faça a ressalva de que no 9i existe o BIND > VARIABLE PEEKING, aonde histogramas podem sir ser levados em > consideração mesmo que se use binds. > Claro, o exemplo não está completo, pra complementar eu deveria ter > várias colunas com grande número de valores distintos, coletar > histograms com bucket size indicado, E ainda fazer a tabela > participar de um join, para que as cardinalidades diferentes levem o > otimizador a fazer escolhas diferentes : fica aí a sugestão > de "brincar" com isso quem tiver estudando CBO, e se conseguir um > caso bom, mandem aí pra lista.... Eu mesmo tive curiosidade de montar > isso, mas não houve mesmo tempo hábil, tou com uma filhinha pequena , > o meu tempo em casa é tomado - não parece, mas essas coisinhas dão um > trabalhão danado.... > > []s > > Chiappa > > > --- Em oracle_br@yahoogrupos.com.br, "Marcio Portes" > <[EMAIL PROTECTED]> escreveu > > Acontece que nos casos de histograma que eu usei, nunca considerei > > mais do que 22 valores distintos, ou seja, no desenho da aplicação, > > nunca tive a oportunidade de fazer um tuning mais fino contando com > > 125 buckets (por exemplo) para influenciar nos cards, sempre usei > > para percorrer um índice corretamente (notei também que voce não > > criou índice no seu exemplo). Mesmo se criasse, ele sempre iria > pelo > > índice porque a tabela só tem uma coluna. Valeu pelo adendo. > > > > > > --- Em oracle_br@yahoogrupos.com.br, "jlchiappa" <[EMAIL PROTECTED]> > > escreveu > > > 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
