Blz ?? Sorry pelo atraso (resposta tão em delay absolutamente não está de
acordo com a netiqueta, Ou vc responde logo Ou não se responde), mas vou
colocar alguns coments em cima...
Antes de mais nada, acho que é Importantíssimo a gente explicitar o que é o
Histograma, dentro aqui do nosso mundinho Oracle : ele é uma LISTA de valores,
registrando para uma determinada coluna numa determinada tabela, Quais são os
seus valores possíveis e Quantos registros/linhas existem para cada valor
possível nessa coluna dentro da Tabela, é isso.... A idéia é que, quando um SQL
usar uma condição de filtro/join/o que for comparando uma coluna com um valor
(ou mesmo com o valor de outra coluna), o CBO saiba mais ou menos quantas
linhas ele vai ter que processar/ler/ordenar para esse valor X comparado com a
coluna.
Só essa simples introdução ao conceito já tira um monte de dúvidas : por
exemplo, uma coluna que é PK - por definição qquer acesso a um valor qquer
nessa coluna SEMPRE retorna uma e só uma linha, é a Definição de PK - tá mais
que óbvio pelo Conceito que não faz sentido ter Histogramas aí, a função do
Histograma é saber quantas linhas um valor X retorna... Outra, digamos que vc
tenha colunas que só são usadas para retornar dados na linha do SELECT (não
participam de JOIN, de oRdenação, de WHERE (filtragem), nem nada assim - será
que é útil ter Histograma nesse caso ? è meio óbvia a resposta NEGATIVA, Em
Cima do Conceito.... Esse é o Primeiro ponto a considerar, o Histograma é
útil/aplicável para a coluna/tabela em análise ?????
Outro ponto Crucial quando se discute Histogramas, também derivado do
Conceito : é Ululantemente Óbvio que a Utilidade de vc saber quantas linhas
existem para cada valor possível é vc poder Responder aos casos de Distribuição
Irregular, ie, onde algum/alguns valor(es) estão presentes em Muito Mais ou
muito menos linhas... O exemplo classico - digamos que vc numa tabela (de
funcionários, talvez) vc tem um campo SEXO que pode ser M ou F : via de regra
vc esperaria ter mais ou menos metade de homens e metade de mulheres, então se
um SQL consultar registros com SEXO='M' ou se consultar SEXO='F' ele mais ou
menos vai ter que acessar/ler metade da tabela em qquer caso, né ? Então o
palpite padrão do CBO (ie, que X valores distintos retornam [total / x] linhas)
funcionaria joinha - no nosso exemplo, se a tabela tiver 500 linhas, seja a
pesquisa por M seja por F vai retornar algo próximo de 500 / 2 = 250 linhas....
Veja que estamos falando ESTATISTICAMENTE, interessa que é algo próximo - se na
realidade são 300 M e 200 F não faz mal, ainda tá Estatisticamente próximo....
PORÉM, nem sempre essas regrinhas simples valem : com essa mesma tabela, nesse
mesmo campo SEXO, suponha que na verdade ela representa Fichas de pacientes num
hospital que trate câncer de mama - o CBO não sabe ** mas ** nesse cenário a
realidade vc vai ter muuuuuitos mais registros com F do que com M devido á
raridade que é cancer de mama em homens, aí as regrinhas furam COMPLETAMENTE,
aí SIM é hora total de se usar HISTOGRAMAS para indicar pro CBO que há uma
diferença Vital entre as qtdades de linhas retornadas por cada valor
possível.... yep ??
====> ESTE é o Segundo ponto Crucial para te apoiar na decisão de usar ou não
Histogramas , vc CONHECE a distribuição de valores ?? Ela É altamente irregular
, ou não ??? Só e apenas VOCÊ, que conhece os dados que são SEUS, que conhece o
SEU AMBIENTE, é que pode responder isso.... Blz ?? Vc até pode, se não tiver a
informação, usar as rotinas/opções de AUTO para os histogramas, ou fazer
consultas manualmente, para tentar levantar a informação, MAS a confirmação /
avaliação da Qualidade dos histogramas e/ou das regrinhas automáticas do CBO É
POR SUA CONTA, totalmente...
Mas não acabou ainda : outra questão a considerar é que vc Nem Sempre usa
valores nos SQLs (tipo WHERE SEXO='M') , há ocasiões (principalmente em OLTP)
aonde vc usa BIND VALUES (ie, algo tipo SEXO = :X , onde X é uma variável) ....
Isso é complexo de otimizar porque a cada execução X pode ter valores
diferentes e em o SQL sendo o mesmo (só mudou o valor da variável, mas o texto
do SQL não) o SQL executado anteriormente está em cache e nós queremos o
re-utilizar, mas sem que tenhamos que o re-otimizar, o que é Custoso - então o
que fazer ??
Até a versão 10g o RDBMS fazia BIND PEEKING, ie : antes do SQL ir pro cache
o CBO guardava em memória e analisava o valor das variáveis BIND, usando
histogramas e tudo que tinha direito, e é essa otimização que é imposta ao SQL
sendo cacheado.... O problema é quando a primeira execução/otimização do SQL
era com as binds tendo um valor "ruim"/atípico - digamos, alguém roda um SQL
numa condição SEXO = :X tendo o valor 'F' para a variável X, aí (CORRETAMENTE,
dado o grande número de linhas a se processar/ler) o CBO otimiza o SQL com um
FULL TABLE SCAN, aí o coitado que imediatamente depois rodar o mesmo SQL mas
com 'H' na variável recebe o mesmo plano de full table scan que está no cache,
o que Não É o ótimo pra ele.... A solução para esse cenário no 10g pode ser não
bindar, pode ser gerar um SQL ligeiramente diferente (para que ocorra nova
otimização) a cada mudança da variável, ou mesmo DESLIGAR o BIND PEEK...
NO 11g a Oracle deu um pouco mais de inteligência para o uso de binds,
passando a fazer diferente : a cada otimização de um SQL, se havia histogramas
e se um valor de histograma foi usado para a Otimização, esse SQL (esse cursor,
na verdade) fica "marcado" internamente como 'sensível a binding', então as
próximas execuções dele vão passar a levar em conta o histograma e se preciso
criam um child cursor com um plano diferente para o novo valor da variável, e é
esse child que é usado e cacheado.... Isso minimiza a chance de planos errados
MAS pode levar à criação de múltiplos planos (e múltiplas entradas no cache de
SQL, que é um overhead) , entre outras questões....
==> Então ESTE é o Terceiro ponto sobre usar ou não histogramas, a pessoa
TEM que saber como estão sendo usados os histogramas, se pode haver efeitos
relacionados com peeking se ela usa bindings.....
E finalmente, a questão de QUANTIDADES de valores e amostragem : não é
fisicamente possível/viável vc coletar listas de centenas e centenas de valores
distintos possíveis para uma coluna (não há poder computacional Nem janela
hábil pra isso, via de regra), Nem é viável vc ler/ordenar/processar
não-sei-quantosGBs para as tabelas grandes... O IDEAL seria que vc coletasse
TODAs as possibilidades de valores distintos lendo/processando a tabela inteira
sempre, mas em muitos casos isso não é possível, assim :
- o Oracle coloca um LIMITE na qtdade de valores distintos para uma coluna
(o bucket size) de 254
- para as tabelas maiores vc (por questões computacionais/de tempo) precisa
dar uma Porcentagem a ler/processar, não dá para o fazer com 100%
==> Este é o Quarto ponto sobre histogramas : já que nem sempre vc pode fazer
a coleta Integral dos histogramas, vc TEM que encontrar um ponto razoável para
vc.... Até pode se começar com as opções de AUTO, mas SE a sua análise da
qualidade/efetividade dos histogramas resultar negativa, vc TEM que testar e
encontrar tanto bucket sizes quanto porcentual de estimate que te atendam...
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, Régis Pradela <pradelarf@...> escreveu
>
> Senhores, bom dia!
>
> O intuito desta mensagem não é tirar dúvida, mas sim abrir uma discussão
> para que possamos "trocar figurinhas" sobre casos de estudo, trabalhos,
> testes, etc, envolvendo diferentes tipos de coletas de estatísticas e seu
> reflexo na perfomance do ambiente.
> A um bom tempo estou estudando as mudanças de performance que o tipo de
> coleta de estatísticas causa nos ambientes, porem ainda não consegui chegar
> a uma resposta certa.
> Na verdade estou achando que a resposta correta seria "Varia de ambiente
> para ambiente, alguns ambientes o histograma melhora, e muito, e em outros
> ambiente fica muito pior. Temos que testar e homologar a coleta de
> Estatísticas para cada ambiente."
> Segue alguns dados que levantei:
> ==> Caso 1
> Ambiente Linux 64bit Oracle 10gr2 10.2.0.3.
> Com a coleta de estatísticas com histogramas tivemos diversas melhorias na
> performance do ambiente.
> Após a migração deste ambiente para Oracle 11gr2 (11.2.0.3) algum processos
> ficaram mais rápidos, porem alguns ficaram muito mais lentos.
> Após remover os histogramas os processos passaram a executar com muita
> rapidez, processos de fechamento que executavam em 26 horas passaram a
> executar em 4horas.
> Alguns processos de fechamento do financeiro que demoravam em torno de 30
> minutos para executar, passaram a executar em 4 minutos.
> Processos que faziam import de arquivos externos, que demoram, em média 40
> minutos para importar um arquivo de 100MB passaram a executar em menos de um
> minuto.
>
> Resultado => No Oracle 10gr2 o histograma melhorou o ambiente, porem após
> migrar para o 11gr2 a melhor opção foi não utilizar histogramar.
> Neste caso não tivemos migração de hardware, apenas de software.
>
> ==> Caso 2
> Ambiente AIX 6 com Oracle 11gr2 11.2.0.3 Extended RAC
> As coletas de estatísticas com histograma foram implementadas no ambiente e
> geraram diversas melhoras de performance.
> Recentemente após particionar as maiores tabelas da folha de pagamento os
> fechamentos que demoravam em torno de 2 horas, passaram a demorar 9 horas.
> Alguns fechamentos que demoravam em torno de 20 minutos por período passou a
> demorar mais de 4 horas.
> Observando o ambiente notamos que após o particionamento não foi coletado os
> histogramas das tabelas, após a coleta novamente os processos voltaram a
> executar rapidamente.
>
> Resultado=> Neste caso os histogramas fizeram uma grande diferença positiva
> no ambiente.
>
> Nos ultimas eventos da Oracle que tive oportunidade de participar a
> recomendação dos palestrantes está sendo , "Use a coleta default, se ela não
> te atender, então pense em muda-la".
> Ultimamente estou acreditando que, como dito acima, depende muito do
> ambiente, distrubuição de dados, aplicação, enfim, temos que homologar a
> melhor coleta de estatísticas para cada ambiente.
>
> Agora gostaria de saber de vocês, sobre experiências e se vocês já tem
> alguma ideia formada sobre este assunto.
> Não coloquei muitas evidencias e/ou testes, apenas falei pois a idéia seria
> mais um bate papo sobre o "feeling" de cada um sobre este assunto.
>
> --
> R.P.
> DBA Oracle
> Blog: www.rpradela.com.br
>
> Oracle Database 11g Administrator Certified Professional
> Oracle Database 11g Administrator Certified Associate
> Oracle Database 10g Real Applications Clusters Administrator Certified
> Expert (OCE)
> Oracle Enterprise Linux Certified Implementation Specialist (OCE)
> Oracle Database 11g Data Warehousing Certified Implementation Specialist
> Oracle Exadata 11g Certified Implementation Specialist
>
>
>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
