ORA-600

Bind Variable Peeking e Performance de SQLs

28/03/2009 at 12:33 pm (DBA, Developer)

Pessoal, antes de continuar a série sobre Fragmentação falando sobre índices, vou postar este artigo sobre Bind variable Peeking, pois diversas pessoas andaram me perguntando sobre isso, andaram rolando threads sobre isso nos grupos de discussão, então desencavei o texto que tinha começado sobre o assunto e dei uma garibada, vamos lá…
Primeiro, para entendermos a questão vou (ultra-rapidamente, prometo!) repassar o mecanismo de execução de SQLs no bd Oracle : sempre que vc (ou a sua tools/linguagem) envia um texto com comandos SQL ao banco, a primeira coisa que o banco faz é checar a sintaxe desse texto, se ele for válido (e se os objetos citados estão TODOS presentes, há permissões para eles, SE o ambiente não foi alterado, etc) o texto é transformado num “código” (hashing) e é feita uma busca por ele no cache de SQLs, se for encontrado ok, é esse SQL já em RAM que é executado com o mesmo plano de acesso anterior (que é armazenado junto com o SQL “codificado”), E caso não for encontrado um match exato para o texto do SQL, aí sim acontece o hard parsing, que envolve passos EXTREMAMENTE custosos, tais como a checagem dos planos/opções possíveis, a otimização (que envolve a montagem de muitos planos de acesso possíveis para o SQL e a comparação entre eles pra testar qual é o melhor), etc, resultando (em tese no melhor plano de acesso possível, que vai para o cache junto com o SQL recém-codificado.

OK, isto posto, aí surge um Segundo ponto, E SE o SQL contiver variáveis, não for sempre um texto fixo, for algo como :

SELECT nnn FROM TABELA WHERE colunachave = :X;

aonde X é uma variável, fará diferença se X tiver um ou outro valor qquer ? Pois o CBO (otimizador por Custo de SQLs) é capaz de analisar a quantidade de ocorrências de um valor numa dada coluna, através duma lista de ocorrências chamada HISTOGRAMA, e com ela saber se um dado valor é raro ou não, qual fração do total da tabela um dado valor retorna, isso poderia ser usado para otimização do plano a gerar…    Até a versão 8i não havia escolha, o CBO simplesmente ignorava os Histogramas em qquer caso de presença de variáveis no SQL, então à pedidos a Oracle refinou esse mecanismo, e introduziu no banco 9i o conceito de BIND VARIABLE PEEKING : na primeira vez que o SQL é analisado (com HARD PARSE) o valor da variável é levado em conta, e portanto os histogramas da coluna se houverem são usados – isso é fácil de fazer em tempo de hard parse porque, óbvio, o valor das variáveis ainda está presente em RAM, pois a sessão interessada que enviou o SQL se está fazendo hard parse logicamente acabou de enviar o SQL, ainda está conectada, e variáveis residem na PGA, a área “particular” de cada sessão, então o valor está disponível necessariamente.

Explicados os dois conceitos-chave acima, chegamos então (ufa!) ao ponto deste post : imagine que, por azar total, o primeiro usuário a rodar um dado SQL do sistema que permite informar valores para filtros informa um valor muito frequente – em havendo histogramas na coluna o CBO vai os analisar, vai descobrir que o valor informado se repete muito, corretamente vai optar por varrer a tabela num full table scan ou algo similar. O que acontece se após isso um outro usuário (ou o mesmo, que seja) executa o mesmo SQL somente informando valor diferente, um valor raro que seria de bom-tom usar índices, para pesquisa ? Como nós dissemos acima, o SQL ** está ** em cache, já contém o plano original, é esse plano original com scan na tabela que vai ser usado, é isso, este é o risco do Bind variable Peeking para performance , ele NÃO ocorre sempre e portanto pode ser reusado um plano anterior não-apropriado, risco esse que permaneceu no banco 10g : foi só no banco 11g que a Oracle quase eliminou o risco, disponibilizando para o cache de SQLs permanentemente uma “cópia” dos valores usados nos BINDs de cada SQL anteriormente executado e que está em cache, e alterando a rotina de SQL matching para que, quando buscar por um SQL no cache, compare o valor de bind usado na sessão atual que enviou o SQL com o valor original do hard parse, SE forem diferentes a sub-rotina de avaliação de histogramas é acionada e um novo plano pode ser montado se for considerado proveitoso, essa feature se chama “bind-aware cursor matching”.

Aí vem as perguntas pra bancos pré-11g :

Como identificar se está havendo Bind peeking para um dado SQL ?
Resposta : levantar EXATAMENTE o SQL suspeito, que está apresentando má-performance, INCLUSIVE com os valores de bind variables (isto pode ser feito via TRACE no banco 9i ou via V$ próprias no 10g), e checar se já há um SQL com texto rigorosamente similar no cache (para o banco 10g inclusive dispomos dos BINDs usados em tempo de hard parse, no 9i não), verificar nas V$ e views DBA se há histogramas, testar o SQL suspeito re-executando com outros valores…

Como podemos prevenir o reuso de plano gerado anteriormente por bind variable peeking ?
Resposta : não há uma recomendação única, mas uma vez que vc entendeu a questão, se houver chance do problema ocorrer no seu sistema pode-se :

a) para os normalmente poucos casos onde isso ocorre, não usar bind, enviar para o banco SQLs do tipo :

SELECT nnn FROM tabela WHERE colunachave = 10;
SELECT nnn FROM tabela WHERE colunachave = 20;

Desvantagem :  como sabemos nós, se vc abusar do envio de SQLs literais, cad um ocupa nova posição no cache, o cache será esgotado rapidamente, cada SQL terá texto diferente e implicará portanto em hard parse…. Muuuito cuidado !! ===>>> Óbvio, repito : usar isso nos POUCOS e RAROS casos aonde vc sabe que pode haver peeking !! E PREFERENCIALMENTE em sistemas DW/Batch, onde há POUCOS SQLs (embora muito complexos) rodando, E as tabelas são lidas em grandes porcentagens (para sumarizar/analisar dados), o que aumenta em muito a chance de diferenciação/não uso de informação na otimização por bind peeking

b) desabilitar peeking, via ALTER SESSION SET “_optim_peek_user_binds”=FALSE; em SQLs que exijam BINDs, que sejam frequentes

Desvantagem : isto volta ao status pré-9i, ie, o otimizador DESPREZARÁ as chances de planos diferentes (eventualmente talvez mais eficientes) com uso dos histogramas, o otimizador sem bind peek vai sempre CHUTAR, vai ESTIMAR a quantidade de linhas prum dado valor numa coluna – é TESTAR MUITO BEM SE no seu sistema, com os SEUS dados, esse “Chute” do CBO é aceitável.
OBS : uma variação desta técnica é simplesmente NÃO coletar histogramas nos objetos em questão, pode ser aceitável em alguns casos

c) usar HINTs, indicando o plano desejado qquer que sejam os inputs de usuário

Desvantagem : na prática isso “engessa” o CBO, perdemos a razão de ser do CBO, que é se adaptar ás novas circunstâncias – com HINTs, ainda que uma dada tabela ficou “grande” ou “pequena”, permitindo outras abordagens mais eficientes, com HINTs o plano SEMPRE é o mesmo, se um full table scan (digamos) é usado hoje nesse cenário e vai bem porque a tabela é “pequena”, com HINTs se a tabela crescer o FTS *** ainda *** vai ser usado, chances há de se obter performance insatisfatória.
===>> HINTs só devem ser por isso usadas ESPARSAMENTE, apenas em SQLs aonde NÃO se prevê alterações de volume significativas ** E ** Realmente outras opções não resultaram

d) “Forçar” um hard parse, que é onde pode haver bind peek : implica usar SQL dinâmico parcial, acrescentando um caracter qquer, uma diferenciação inócua qualquer (comentário talvez) a cada execução do SQL

Desvantagem : a óbvia, em caso de abuso da técnica a performance geral pode cair, pois cfrme dito cada hard parse é mais custoso do que um reuso, com hard parses frequentes o consumo (principalmente de CPU) cresce muito rapidamente

OBS : uma variação desta técnica é forçarmos uma invalidação de ambiente do SQL (pois cfrme dito no início do texto qquer alteração de ambiente/objeto implica em não reuso de SQL) : essa invalidação pode ser uma recoleta de estatísticas, um DDL qualquer (até um pequeno ALTER nalgum atributo secundário de alguma das tabelas)…

É isso, espero ter esclarecido esse mecanismo Oracle, que não é complexo mas é uma das causas comuns de “instabilidade” de performance em SQLs.

Abraços,

Chiappa

Link Permanente 1 Comentário