Soft Parse e Hard Parse - Oracle

Parsing é um processo que está diretamente relacionado ao desempenho do banco de dados e ter seu entendimento é extremamente importante para realizar melhor as instruções SQL. Parse significa análise, e é exatamente isso que ocorre nessa fase.

A fase de parsing é um dos processos que ocorre na execução de instruções SQL. A Figura 1 exibe o fluxo do processo de parse. Quando um process user emite uma instrução SQL, essa instrução será interpretada e analisada nessa fase.

Figura 1 - Etapas do processamento de instruções SQL. (Fonte: Silva, P. H.)

A primeira análise realizada é a análise sintática onde, segundo Paulo Henrique Silva é verificado se existem erros estruturais na instrução ou se alguma regra de SQL foi quebrada, ou seja, se o comando é válido e interpretável. O Quadro 1 apresenta uma instrução com erro estrutural.

No primeiro exemplo ocorreu um erro de digitação, onde deveria constar a palavra reservada da SQL from, constava a palavra errada form.

A segunda análise realizada é a semântica, e para a mesma acontecer, o comando deve necessariamente ter sido analisado sintaticamente com sucesso. Paulo Henrique afirma que nessa etapa são verificadas questões como a existência dos objetos envolvidos na instrução, permissão de acesso a esses objetos, verificação de ambiguidade de campos no código, dentre outras. O Quadro 2 apresenta uma query com erro de ambigüidade de campo.

Um erro: ORA-00918: column ambiguously defined, será gerado devido ao campo id_departamento não possuir indicação de qual tabela pertence, uma vez que campos com esse nome ocorrem nas duas tabelas: DEPARAMENTOS e FUNCIONARIOS.

Seguindo o fluxo do processo de parse, caso o comando não apresente erros (sintáticos ou semânticos), sendo bem sucedido nas análises, o próximo passo é verificar se a instrução já foi executada anteriormente bem como se as informações ainda constam na memória (e podem ser utilizadas).

Ainda segundo estudos de Paulo Henrique, na etapa de busca na shared pool, mais especificamente na Shared SQL Area da Library Cache, a instrução está validada e pronta para ser executada pelo banco de dados. O Oracle gera um plano de execução para todas as instruções SQL que serão executadas. O plano de execução é uma estratégia que o otimizador do Oracle define para acessar os dados da “melhor forma”, baseado nas estatísticas (e outras informações) do banco de dados. Questões como ordem de execução e método de acesso aos objetos são levados em consideração durante a análise do plano de execução. Nesse momento, um código chamado de HASH_VALUE, representando uma instrução SQL, e outro código chamado PLAN_HASH_VALUE, representando o plano de execução já executado são armazenados na memória (library cache) conforme indicado na Figura 2. Esse trabalho é realizado pelo otimizador Oracle, que será abordado mais adiante.

Se o mesmo comando SQL for executado novamente e caso as informações de parse sobre ele ainda estejam na memória, pode-se reutilizar o plano de execução gerado anteriormente. Essa condição indica uma situação chamada de “soft parse”, sinalizando que já existe em memória um plano de execução válido, que poderá ser reutilizado. Além de realizar a comparação do HASH_VALUE para reutilizar um plano de execução, o Oracle também compara a instrução SQL inteira para ter certeza que se trata da mesma, sintaticamente e semanticamente. Para o caso de não existir um HASH_VALUE para uma instrução (o que indica que ela está sendo executada a primeira vez ou que ela foi executada há muito tempo e já saiu da library cache) não haverá informações para serem reaproveitadas, levando o Oracle a realizar o “hard parse” para atender à solicitação. Isso quer dizer que o banco executará algoritmos internos para gerar um plano de execução, alocá-lo em memória e enfim executá-lo (SILVA, 2012).

Figura 2 - Busca de HASH_VALUE na shared pool por uma instrução reincidente. (Fonte: Silva, P. H.)

Dando continuidade aos conceitos envolvidos na Figura 1 temos o otimizador. Segundo a própria Oracle, o otimizador é um mecanismo do banco de dados que determina a maneira mais eficiente de executar uma instrução SQL depois de considerar vários fatores relacionados com os objetos referenciados e as condições especificadas no comando. Esta determinação é um passo importante no processamento de qualquer instrução SQL e pode afetar muito o tempo de execução.

O otimizador é um dos primeiros mecanismos a ser executado quando ocorre um hard parse e é responsável por traçar o plano de execução das instruções SQL enviadas para processamento.

A saída do otimizador é um plano que descreve o método ótimo de execução da instrução SQL que será usado pelo gerador de plano de consulta.

De acordo com Paulo Henrique Silva, o gerador de plano de consulta (Row Source Generator) é responsável por utilizar o plano de execução ótimo gerado pelo otimizador Oracle e transformá-lo em um código executável pelo banco de dados (um programa binário).

Após essas etapas, tem-se uma versão executável do comando SQL do user process, que processará o objeto de saída do row source generator e buscar as informações solicitadas.

Soft parse e Hard parse

Como mencionado, o processo de parse possui uma interdependência entre suas etapas: a busca na library cache depende do sucesso na análise semântica, que depende do sucesso da análise sintática. Seguindo por essa linha, caso a busca do hash do comando SQL (e do hash do plano de execução) exista na shared pool, configura-se uma situação de soft parse (conhecido também por “library cache hit”) e o processo de parse tem seu curso desviado direto para a execução da instrução SQL. Caso o hash não seja encontrado na shared pool, existe uma situação de hard parse (conhecido também por “library cache miss”) e exigirá que o banco execute as etapas do otimizador e gerador do plano de query (etapas essas que consomem bastante da CPU do servidor).

A ocorrência de soft parse não reduz completamente o gasto de CPU do processo de parse, mas diminui significativamente o custo em relação ao hard parse. Paulo Henrique Silva afirma que existem situações em que é gasto mais tempo realizando a tarefa de parse de uma instrução SQL do que o tempo com a execução dela.

Uma situação que pode ser considerada como ideal é para o caso de todas as instruções SQL que o banco de dados for executar durante o seu funcionamento, já existisse compilada em memória (por exemplo, na inicialização do banco).  Dessa forma, a ocorrência de soft parse seria constante e não existiria uma configuração de hard parse, ganhando-se em processamento no servidor, e consequentemente em tempo de resposta às solicitações. É nítido como esse cenário é utópico, dessa forma admitisse uma boa prática, se possível, um único hard parse, referente à primeira vez da ocorrência de solicitação da instrução SQL, realizando apenas soft parses nas execuções seguintes.

Para facilitar o entendimento, abaixo são citadas situações relevantes para o tema em questão.

Um user process solicita a execução de uma query conforme Quadro 3.

Por ser a primeira vez que está sendo executada, a consulta passará por todas as etapas do parse, configurando-se assim uma situação de hard parse.

Pode-se verificar esse resultado através da view do esquema SYS, conforme Quadro 4.

Com o resultado dessa consulta, pode-se acompanhar como o banco de dados está se comportando diante de uma instrução SQL, verificando se está reutilizando ou não um comando executado anteriormente, ou seja, se o banco de dados está utilizando o soft parse ou hard parse para atender à solicitação. O resultado para a consulta na view está na Figura 3.

Figura 3 - Consulta na view V_$SQLAREA. (Fonte: autores do documento)

Ao se executar a mesma consulta novamente, tem-se uma configuração de reuso de query, podendo assim aproveitar do plano de execução já gerado na primeira ocorrência da instrução, o que implica em um soft parse. A Figura 4 indica que a instrução foi reutilizada.

Figura 4 - Consulta na view V_$SQLAREA indicando reuso de instrução. (Fonte: autores do documento)

Para melhorar o entendimento, a instrução sofreu uma alteração no valor de busca, conforme Quadro 5, e seu resultado pode ser observado na Figura 5.

Figura 5 - Consulta na view V_$SQLAREA após a alteração do parâmetro de busca. (Fonte: autores do documento)

Com isso, conclui-se que uma simples alteração na crítica de busca, pode configurar uma nova instrução no entendimento do banco de dados, porém isso depende de uma configuração.

Existe um parâmetro no banco de dados Oracle 11G que diz respeito a como deve ser seu comportamento em nível de execução quando receber um comando SQL, esse parâmetro é o CURSOR_SHARING, que admite três valores nessa versão do banco: EXACT, FORCE e SIMILAR.

O valor padrão desse parâmetro é EXACT de acordo com a Oracle, nessa configuração a etapa de “busca na shared pool” procura por uma instrução SQL exatamente idêntica a uma instrução que possa ter sido executada anteriormente. Somente em caso positivo, será executado um soft parse, para todas as outras situações, um hard parse será executado. Analisando o reuso das instruções SQL apresentadas no Quadro 6, tem-se o resultado observado na Figura 6.

Figura 6 - Interpretação do Oracle para reuso de instruções SQL. (Fonte: autores do documento)

É nítido que as quatro primeiras queries de exemplo relacionadas acima apresentam o mesmo resultado na projeção da informação, mas para o banco de dados são tratadas como instruções diferentes. Isso porque os comandos SQL não seguiram um padrão de escrita. Esse assunto será mais bem abordado na explicação sobre as técnicas para evitar o hard parse. O importante nesse momento é saber que pequenas alterações na escrita da instrução SQL podem fazer a diferença na execução de um soft parse ou hard parse.

Segundo documentação da Oracle, quando setado como FORCE, o banco interpreta variáveis literais como valores de variáveis de ligação (bind variables) em sua execução, fazendo com que a o reuso das consultas sejam mais constantes, ou seja, existam mais soft parse do que hard parse. O conceito e entendimento sobre bind variables deve ficar mais claro quando forem abordadas as técnicas para evitar o hard parse. Com o parâmetro setado para esse valor, o gerador do query plan substituirá os valores literais da instrução SQL por variáveis de ligação fazendo com que na próxima vez que for submetida a mesma instrução com uma crítica de busca diferente da anterior, o banco considere que a instrução já foi executada e reutilizará o mesmo plano de execução, realizando o soft parse.

No exemplo citado no Quadro 3, e com o parâmetro CURSOR_SHARING setado com o valor FORCE, aconteceria o que pode ser observado na Figura 7.

Figura 7 - Execução de query com CURSOR_SHARING=FORCE com valor de busca igual a "1". (Fonte: autores do documento)

A condição de busca da consulta: codigo = 1 foi substituído por codigo = “SYS_B_0”, que se trata de uma variável de ligação. O nome da variável é atribuído automaticamente pelo banco.

Quanto à crítica de busca na consulta sofre uma alteração (conforme exemplo do Quadro 5), a consulta seria reutilizada pois ocorreria a substituição do valor literal por uma bind variable, e na etapa de busca na shared pool, uma instrução idêntica seria encontrada levando à execução de um soft parse como pode ser observado na Figura 8.

Figura 8 - Execução de query com CURSOR_SHARING=FORCE, com valor de busca alterado para "2". (Fonte: autores do documento)

Ainda segundo a Oracle, para o caso do CURSOR_SHARING estar setado como SIMILAR, o banco assume um comportamento muito semelhante a quando o parâmetro está setado para FORCE, diferindo apenas na substituição dos literais. Isso permanecerá acontecendo, assim como no caso anterior (FORCE), a menos que a substituição dos literais afete o significado da declaração ou o grau em que o plano é otimizado, ou seja, caso o plano de execução venha a variar muito com a alteração do literal tem-se a geração de um novo plano, o que acarreta em todos os processos de parsing, o hard parse. Essa comparação entre os planos de execução é realizada através dos histogramas, que assim como as estatísticas auxiliam o otimizador Oracle a buscar a melhor forma de acessar os dados solicitados.

O reuso de instruções SQL deve sempre que possível existir para melhorar o desempenho do banco, mas é importante lembrar que isso varia muito a depender do ambiente em questão. Caso o banco opte por realizar um soft parse e o plano de execução para esse script não esteja otimizado, o tempo de resposta à solicitação pode crescer demasiadamente inviabilizando a utilização da técnica. A escolha de configuração desse parâmetro deve ser realizada com muita cautela e estudo.

Uma característica importante de saber é que no banco de dados Oracle 11G, o valor do CURSOR_SHARING setado para SIMILAR não é tão eficiente. Isso se deve a um recurso que essa versão do banco trás que é o CURSOR_SHARING ADAPTIVE (cursor compartilhado adaptativo), que realiza um tipo de análise se o plano de execução está otimizado antes de reutilizar um script. Essa situação é parecida com o parâmetro setado para SIMILAR (em versões anteriores), só que trás melhorias em desempenho.

Algumas técnicas para evitar o hard parse

Como já sabido, hard parses devem ser evitados, visto que “eliminá-los” tem um custo muito alto e é improvável que uma organização utilize uma política para que esse processo não ocorra, além do mais isso criaria uma limitação na manipulação das informações no banco de dados. Tentando mitigar esse problema, Paulo Henrique Silva elencou algumas técnicas para que a ocorrência de hard parses exista apenas em casos indispensáveis, por exemplo: a primeira vez do uso de uma instrução SQL.

Padronização dos códigos SQL

De acordo com estudos de Paulo Henrique Silva, a padronização dos scripts SQL tem um impacto muito grande e importante no desempenho do banco de dados. Para entender o motivo de se ter uma padronização na escrita dos códigos SQL e seu impacto na performance do servidor de banco de dados Oracle, basta imaginar uma equipe onde cada um dos técnicos tem forma diferente de criar scripts. O que pode acontecer num cenário como esse (e provavelmente acontecerá) são duas instruções, que tem como objetivo a projeção de um mesmo resultado, possuírem planos de execução diferentes, ou seja, realizando dois hard parses ao invés de reutilizar o plano já compilado. Isso é muito comum na maioria das empresas, tendo em vista que um simples espaço em branco na criação da consulta, ou se o script SQL foi escrito em caixa alta ou baixa, diferencia duas instruções.

Visando aumentar o reuso dos planos compilados, ou seja, aumentar o soft parse, criar na política da empresa um padrão para escritas dos scripts de banco torna-se algo muito útil para garantir um menor consumo de CPU e melhor alocação de memória do servidor Oracle, o que tornaria o banco de dados mais performático.

Variáveis de ligação (Bind Variables)

Mesmo fazendo uso de uma política de padronização de código SQL, não se garante que sua eficiência atinja o objetivo desejado: a redução de hard parse. Nesse momento é importante agregar um conceito que complemente o trabalho que a primeira técnica propõe: bind variables ou variáveis de ligação.

Para compreender o funcionamento das bind variables, o Quadro 7 apresenta dois exemplos já vistos anteriormente.

As escritas dos comandos estão em um mesmo padrão, porém a crítica de busca é diferente criaria dois planos de execução diferentes. Isso faria com que ocorressem dois hard parses ao invés de um. Uma solução para que isso não ocorra é o uso de bind variables, que terá seu conceito explicado através de exemplos a seguir.

A presença do uso de bind variables, pode ser vista nos chamados: “script compilados”, que são as instruções SQL residentes em functions, procedures e triggers. Nessas situações, independente dos argumentos ou parâmetros passados existirá uma substituição desses valores literais por bind variables, conforme indicado no Quadro 8.

Para efeito de confirmação, o Quadro 9 apresenta um script para verificar o valor do parâmetro CURSOR_SHARING.

Tem-se o resultado exibido na Figura 9.

Figura 9 - Checagem do valor do parâmetro CURSOR_SHARING. (Fonte: autores do documento)

Sabido que quando o CURSOR_SHARING está setado para o valor EXACT, onde não existe a substituição dos literais por bind variables, o comportamento para “scripts compilados” é diferenciado. O Quadro 10 apresenta chamadas à procedure com parâmetros distintos.

Verificando se ocorreu o reuso da consulta existente na procedure com a query exibida no Quadro 4, tem-se o resultado exibido na Figura 10.

Figura 10 - Análise de reuso de query da procedure de teste: PRC_QUERYCOMPILADA. (Fonte: autores do documento)

Dessa forma fica evidente que mesmo com o CURSOR_SHARING=EXACT tem-se a substituição dos literais por bind variables para o caso de scripts compilados em procedimentos (funções e triggers também seguem esse padrão).

Existe outra situação, onde dentro de um bloco lógico, seja ele uma procedure, function ou trigger, pode não ocorrer o reuso de um script SQL, como pode se observar no Quadro 11.

A instrução EXECUTE IMMEDIATE é uma forma de se construir scripts dinâmicos no Oracle, e basicamente sua função é submeter ao banco o comando passado como argumento.

Executando chamadas à nova procedure criada acima (Quadro 11), conforme Quadro 12.

Tem-se o resultado exibido na Figura 11.

Figura 11 - Análise de reuso de script da procedure de teste: PRC_INSERTDINAMICO1. (Fonte: autores do documento)

Pode-se concluir que não existiu reuso da instrução INSERT quando o parâmetro sofreu alteração de valor entre as duas chamadas à procedure. Dessa forma ocorreram dois hard parses ao invés de apenas um, pois se trata de uma mesma instrução com variação nos campos literais.

Para atender ao conceito de script dinâmico juntamente com o conceito de reuso de instruções, existe uma variação no comando EXECUTE IMMEDIATE, que atende aos dois requisitos, conforme exibido no Quadro 13.

Executando chamadas à procedure do Quadro 13, conforme script do Quadro 14.

Tem-se o resultado exibido na Figura 12.

Figura 12 - Análise de reuso de script da procedure de teste: PRC_INSERTDINAMICO2. (Fonte: autores do documento)

Nesse caso, diferente do que ocorreu na chamada à procedure PRC_INSERTDINAMICO1, existiu reuso da instrução INSERT mesmo com parâmetros de valores distintos, ocorrendo assim apenas um hard parse e um soft parse.

Post Escrito por:

Fonte : Raphael Fernandes