domingo, 11 de dezembro de 2011

Otimização de parsing usando bind variables

Hoje vou apresentar a vocês um dos grandes responsáveis pela degradação de performance nos bancos de dados Oracle: trata-se do excessivo uso de hard parsing,  ou seja, a pequena reusabilidade dos planos nas diretivas SQL.

Para quem não conhece, o hard parse ocorre quando o banco vê uma query pela "primeira vez" e não tem uma estratégia definida sobre como irá acessar os dados. Logo, ele precisa analisar todas as combinações possíveis de métodos de acesso para obter aquela informação solicitada. Esta análise inclui, mas não está limitada a, definir a ordem de join das tabelas, análise de estatísticas (de tabelas e do sistema), a decisão de fazer a leitura por índices ou full table scans (FTS), a reescrita da query para uma equivalente mais rápida, criar ou não tabelas intermediárias para views, enfim, uma infinidade de operações que, quando mais complicada for a query, mas possibilidades existem e mais recursos são consumidos para estabelecer este plano de execução.

Uma vez estabelecido, o plano de execução tende a ser reutilizado todas as vezes que a query for executada, desde que este plano consiga se manter em memória. Acontece que, se muitas querys distintas forem executadas, a área disponível para armazenamento de planos na shared pool pode não ser suficiente, e os planos mais antigos começam a ser descartados. Numa situação destas, um plano previamente estabelecido pode ser sobrescrito por um plano de outra query e ao reexecutarmos uma query mais antiga ela acabará tendo que passar pelo processo de hard parse novamente (e por sua vez sobrescrevendo o plano de outra query).

Agora, o que determina a quantidade de hard parsing no banco, e como evitar? Bom, primeiro, um dos critérios é o tamanho em memória da shared pool. Esta área armazena todos os planos atualmente disponíveis para o Oracle. Não vou entrar em detalhes neste momento sobre quanta memória é o ideal para esta área, pois para os fins deste artigo é o critério seguinte que nos interessa: a quantidade de querys distintas que existem no banco em um dado momento.

Para responder esta questão a primeira coisa que precisamos levar em conta é: como o Oracle faz para identificar que uma query já existe ou não na shared pool? E a reposta é, de modo geral, com uma comparação textual exata da query solicitada com as querys disponíveis na shared pool. Digo de modo geral porque este comportamento pode ser modificado com o parâmetro de sistema cursor_sharing (voltarei a falar sobre ele adiante).

Suponha por exemplo, que você executou a query a seguir:

select * from t where x = 1;

E em seguida executou a query:

select  * from t where x = 1;

A primeira vista as duas querys podem parecer iguais, mas na segunda eu incluí um espaço a mais antes do asterisco (*). Isto por si só já é o suficiente para que a segunda query falhe na comparação textual com  a primeira, e portanto as duas serão submetidas a um hard parse.

Este conceito fica mais evidenciado quando estamos lidando com SQL dinâmico, pois é muito comum as pessoas codificarem a passagem dos valores para a query utilizando a concatenação de valores:

execute immediate 'select * from t where x = ' || valor;

Agora considere este código dentro de uma stored procedure que recebe a variável 'valor' como parâmetro. Esta SP vai ter o poder de gerar infinitos cursores, pois cada valor concatenado vai forçar uma query nova (vai falhar na comparação textual) e toda chamada desta SP, além de passar por um hard parse, vai ainda retirar um cursor mais antigo da shared pool que poderia ser reaproveitado. Ou seja, além da lentidão do parse, corremos o risco de destruir a shared pool inteira, se por exemplo, executarmos esta SP dentro de um loop.

No próximo artigo irei apresentar um caso de performance de um código com bind variables contra um código sem binds. Fiquem ligados!