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.
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).
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.
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.
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.
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.
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.
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
