[oracle_br] Re: Plano diferente quando usa bind
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 oracle_br@yahoogrupos.com.br, Julio Bittencourt 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: oracle_br@yahoogrupos.com.br > [mailto:oracle...@yahoogrupos.com.br] Em nome de jlchiappa > > Enviada em: segunda-feira, 25 de maio de 2009 22:36 > > Para: oracle_br@yahoogrupos.com.br > > 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 > >
[oracle_br] Re: Plano diferente quando usa bind
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: oracle_br@yahoogrupos.com.br [mailto:oracle...@yahoogrupos.com.br] Em nome de jlchiappa Enviada em: segunda-feira, 25 de maio de 2009 22:36 Para: oracle_br@yahoogrupos.com.br 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
[oracle_br] Re: Plano diferente quando usa bind
Fala Gleyson, Sua sugestão bate com uma das que o Chiappa fez, então vou testar e dou um retorno. Valeu! De: oracle_br@yahoogrupos.com.br [mailto:oracle...@yahoogrupos.com.br] Em nome de Gleyson Melo Enviada em: segunda-feira, 25 de maio de 2009 20:29 Para: oracle_br@yahoogrupos.com.br Assunto: Re: [oracle_br] Plano diferente quando usa bind Fala Julio, Se você colocar os valores como constantes nas consultas, o Oracle vai fazer o uso de histogramas para verificar a distribuição das colunas e escolher o melhor plano de execução que ele puder com base nisso. Creio que seja esse o problema. Quantas vezes você chama esse SELECT? As vantagens de você usar binds são reaproveitar planos de execução e diminuir a concorrência na Shared Pool. Se você não ficar muito "incomodado" com um uso imperfeito da Shared Pool, você pode substituir colocar esse SELECT dentro de um execute immediate e concatenar os valores que seriam filtrados através de binds. Assim você força o Oracle a escolher um novo plano de execução adequado para esse caso específico. Se você postar a consulta, os planos (com e sem bind), índices e histogramas da tabela, poderemos te ajudar mais. Abraços. 2009/5/25 Julio Bittencourt > > > 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] > > > -- Atenciosamente, Gleyson Melo Oracle Database 10g Administrator Certified Professional [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]
[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 oracle_br@yahoogrupos.com.br, Julio Bittencourt 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. > > Veri
