Ok, provavelmente então a suposição sobre a(s) coluna(s) do WHERE serem de distribuição altamente irregular, haver histogramas (E que são altamente necessários por causa da irregularidade) , e o plano ser gerado contra um valor não-significativo por conta do bind variable peeking deve ser isso mesmo.... Sim, uma das opções nos bancos antes do 11 é mesmo essa, forçar um PARSE a cada execução (com SQL dinâmico é uma opção), se te resolveu para esse ponto blz, só aconselho vc a ser proativo, e ** LEVANTAR ** quais pontos mais do sistema/quais SQLs mais mexem com colunas com dados de distribuição irregular, esses caras podem te aparecer no futuro... Quanto ao parâmetro, se NÂO tem nem documentação nem indicação de fornecedor/programador recomendando ter, CERTAMENTE não tem também nada recomendando não ter, então se eu fosse vc e fosse responsável pela bagaça, marcaria uma data pra alterar e ** TESTAR ** o banco com esse param setado tal como recomendado (ie, mesma versão do banco), pois com esse cara inferior **** MUITAS **** melhorias introduzidas no 9i vc não está usando, vc PODE muito bem estar com performance / usabilidade inferiores à toa...
[]s Chiappa --- Em [email protected], Julio Bittencourt <juliobit_...@...> escreveu > > > > Chiappa, Gleyson, > > Valeu pelas dicas. Fiz uma experiencia com execute immediate e deu certo! Os > planos de execução ficaram iguais aos da execução "por fora". A performance > ficou muito boa. Já passei as instruções para os desenvolvedores para > implementação (de quebra ainda descobri uma falha na lógica da procedure que > faz com que a query mais pesada seja exeuctada mais de uma vez, ninguém > merece...) > > Essa procedure não é usada massivamente, só alguns poucos usuários a utilizam > e não é todo dia, então acredito que não irá impactar muito a shared pool. > Ficarei monitorando. > > > Chiappa, > Quanto ao parâmetro, eu até tentei descobrir o motivo, falei com várias > pessoas aqui e ninguém soube responder. Também não encontrei documentação a > respeito. Pelo que consegui descobrir, o banco era 8i e foi migrado para o 9. > Acho que nessa migração é que resolveram manter o compatible=816. Parece que > simplesmente a informação se perdeu, houve muita mudança de pessoal e quem > participou disso já se foi há muito tempo. > Abçs. > > De: [email protected] > [mailto:[email protected]] Em nome de jlchiappa > > Enviada em: segunda-feira, 25 de maio de 2009 22:36 > > Para: [email protected] > > Assunto: [oracle_br] Re: Plano diferente quando usa bind > > > > > > > > > > > > > > Seguem algumas respostas, mas antes uma > observação : quando vc diz que não sabe quem/por que setou esse parâmetro de > compatibility , PRIMEIRO DE TUDO se vc vai ser responsável por esse ambiente, > descubra o porquê : imho é ABSOLUTAMENTE crítico o DBA conhecer EXATAMENTE o > ambiente aonde vai trabalhar, naõ importa que outra pessoa e não vc setou - > abra chamado no Suporte do fornecedor da aplicação, faça (junto com os > Analistas) testes sem o talzinho, mas DESCUBRA o porque, parâmetros do tipo > PRECISAM de uma razão MUITO BOA pra serem setados... > > > > Isto posto, as respostas: > > > > "Porque o Oracle não usa o mesmo plano de execução quando o select tem > > bind e quando tem valores fixos?" : o que acontece é que quando vc informa > o valor diretamente o banco TEM a possibilidade de consultar a distribuição > dos > dados, de saber EXATAMENTE quantos registros distintos um WHERE filtra pro > valor (isso é mantido num elemento chamado HISTOGRAMA das estatísticas de > CBO), > já quando vc usa BIND VARIABLEs ele NÃO faz essa consulta a cada execução, mas > só na primeira - isso porque há um overhead para se checar histogramas, que > poderia ser inaceitável se simplesmente fosse ativado a cada SQL, cegamente. > Isso pode, claro, levar a diferenças gritantes quando se usa bind, tanto se > houver histogramas numa coluna não-apropriada quanto (o que parece ser o seu > caso) os histogramas estão presentes mas não são usados devido à re-execução > de > SQL com binds. Isso faz sentido, já que o objetivo de se usar BINDs é > reaproveitar o mesmo SQL que será executado n vezes (tipicamente em OLTP), > aonde é comum pesquisa indexada em SQLs relativamente simples (mas > re-executados frequentemente), assim a distribuição é regular em > princípio, os histogramas nem deveriam existir, mesmo.... Histogramas servem > para vc mudar a presunção do CBO, de que a distribuição é regular, ie, se vc > tem (digamos) 10 valores distintos no campo e 1000 registros, qquer filtro no > WHERE por esse campo trará 1000/10 REGISTROS, é isso.... > > > > No seu caso, se usando o literal direto a performance é melhor, muito > certamente vc DEVE ter uma distribuição irregular na(s) coluna(s) em filtro e > o > CBO está usando os histogramas, que são benéficos no seu caso, E a diferença > quando vc usa BIND é que com binds os histogramas só são consultados em tempo > de parse... Sendo assim, acho que a sua solução NÃO É o bind peek (que como eu > falei no artigo ele DESLIGA TOTALMENTE a busca por histogramas com bind > variables !!), o que vai ser RUIM pra performance de acordo com a suposição > acima, o que vc quer é o *** CONTRÁRIO ***, é justamente que ele use SEMPRE os > histogramas.... No banco 11i isso já foi mudado (e ficou mais > "esperto") mas no 9i e 10g vc vai ter que aplicar work-arounds na sua > programação, mas antes de discutir eu proponho que vc faça um TESTE pra provar > que é isso. Seria assim : faça uma pequena alteração no texto do SQL da tal > procedure com o bind (adicionando uma coluna a mais ou removendo coluna no > SELECT, botando um comentário, enfim, faça o SQL ficar diferente do que já > está > no cache), aí execute a procedure passando pro bind EXATAMENTE O MESMO valor > que vc usou no teste sem binds que deu boa performance, como o texto é > diferente o banco não vai reusar o SQL anterior e vai recompilar e repesquisar > os histogramas, o que deve dar a mesma performance... > > Se o teste resultar positivo, para vc fazer o SQL usar sempre histogramas, vc > tem as seguintes possibilidades : > > > > a) simplesmente não use binds nesse SQL : prum SQL ser sensível a histogramas, > tipicamente ele não é OLTP (que se caracteriza por busca indexada como método > preferido), então é SIM possível e mesmo recomendável não usar BIND nesse caso > > > > b) se vc conseguir descobrir quais valores estão dando má-performance com > BINDs, e eles forem poucos e previsíveis, vc fazer uma programação específica > pra eles, tipo : > > IF condição ruim then > > SELECT com valores fixos > > ELSE > > SELECT com binds > > > > tipo assim > > > > c) forçar hard parse a cada execução do SQL em questão : por exemplo, vc > poderia usar SQL dinâmico (isso necessariamente causa um hard parse e portanto > uma pesquisa nos histogramas), ou vc poderia fazer (via EXECUTE IMMEDIATE, > talvez) uma alteração inócua numa das tabelas , normalmente quando os objetos > envolvidos mudam um hard parse é inevitável > > > > []s > > > > Chiappa > > --- Em [email protected], > Julio Bittencourt <juliobit_dba@> escreveu > > > > > > Temos um procedimento aqui que está demorando muito para executar. > > > Após gerar um trace com "set events '10046" e analisá-lo, vi que > há três selects que consomem a maior parte do tempo. > > > > > > Peguei cada SELECT e os respectivos valores das variáveis bind e executei > individualmente. > > > Aí fica muito mais rápido, tipo se no procedimento cada um leva um minuto, > individualmente leva menos de 10, isso quando os blocos ainda não estão no > cache, se rodar de novo leva 1,5 segundos. > > > > > > Verifiquei que os planos de execução dos SQLs quando da execução pelo > procedimento são diferentes de quando os executo individualmente com valores > fixos no lugar das variáveis bind. > > > > > > Então fiz um teste: Criei um procedimento de teste a partir do original, > substituindo as variáveis pelos valores fixos e, como esperava, o tempo da > execução caiu drasticamente. > > > Isso indica que o Oracle está usando planos inadequados durante a execução > do procedimento, mas não sei como fazer com que passe a utilizar planos > melhores. > > > > > > Vi um artigo do Chiappa que fala sobre Bind Variable Peeking e tentei > usar ALTER SESSION SET "_optim_peek_user_binds"=FALSE; > , mas não surtiu efeito. > > > > > > Em resumo: Porque o Oracle não usa o mesmo plano de execução quando o > select tem bind e quando tem valores fixos? > > > O que posso fazer para forçá-lo a usar o mesmo plano? > > > > > > Dados do Servidor: > > > Oracle 9.2.0.7 64 bits + Sun Solaris 9 > > > Detalhe: COMPATIBLE = 8.1.6 (não me perguntem porque, pois quando eu > cheguei já estava assim e não posso mudar por enquanto) > > > > > > Agradeço desde já qualquer ajuda. > > > Att. > > > Julio > > > > > > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > > > http://br.maisbuscados.yahoo.com > > > > > > [As partes desta mensagem que não continham texto foram removidas] > > > > > > > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > http://br.maisbuscados.yahoo.com > > [As partes desta mensagem que não continham texto foram removidas] >
