.comment-link {margin-left:.6em;}

Monday, May 02, 2005

Bind Variable - Introdução

Em tecnologia, assim como em qualquer outra área de atividade, é preciso utilizar as ferramentas corretas para cada ocasião. Se em uma marcenaria é necessário enfiar um prego em uma madeira, imagine se o marcineiro pegasse a furadeira elétrica e desse na cabeça do prego? Interessante não? Pois bem, é preciso reconhecer onde as ferramentas são utilizáveis.

Falando em boas práticas, vou começar meu primeiro artigo falando de Bind Variables. Farei uma introdução e nos próximos dias, avançaremos em complexidade.

O que é bind variable? É uma técnica de programação que evita o uso de literais nas sentenças SQL. O programador prepara a sentença com variáveis e passa o valor para as mesmas ao invés de, literalmente, usar o valor. Com isso o Oracle aproveita sua SGA, especialmente a shared pool para compartilhar os cursores que já estejam disponíveis e parseados.

Quando usar bind variable? Sempre que a aplicação for altamente concorrida, ou seja, muitos usuários trabalhando ao mesmo tempo, inclusive com o mesmo cursor. Toda sentença SQL submetida ao Oracle é um cursor, ou seja, toda vez que uma query chega, o banco tem que fazer o parsing, que é a fase mais pesada, grosso modo, envolve checar sintaxe, semântica, permissão e determinar uma rota de como chegar aos dados.

Quanto mais parse fizermos, menos escalabilidade teremos. O que significa que, menos usuários poderão fazer uso do sistema. A decisão de usar bind variable é uma das mais importantes e cruciais no desenho, quando uma aplicação está sendo construída.

Bind variable, permite que as queries sejam genéricas, essas queries recebem valores no momento de execução. Será parseada uma vez e executada dezenas, centenas de milhares. (Discutiremos como isso realmente funciona mais tarde, na verdade o Oracle fará um hard-parse e depois soft-parse para novas sessões, entretanto a relação entre parse/execução deve ser idealmete 1 x n^m).

Vamos tentar traçar um paralelo de como bind variable seria em uma sessão de tiro-ao-alvo. Imagine duas armas de fogo: O propósito da arma é disparar projéteis. Uma delas é uma cartucheira, onde há um disparo a cada vez, porque ela depende de ser aberta e recarregada a cada disparo e outra, uma pistola automática, municiada por pente. Nesse exercício, sem contar a pontaria, qual das duas atira 10 projéteis mais rápido? Considere o desenho abaixo e qualquer semelhança do DBA com o autor é mera coincidência:










Situação A
Cartucheira:

  1. Carrega
  2. engatilha
  3. Dispara, soma 1 em x
  4. Se x > 10 pára -- final
  5. Retorna em 1.
Situação B
Pistola Automática:

  1. Carrega
  2. engatilha
  3. Dispara até x = 10 -- final

É uma analogia válida do ponto de vista do trabalho que é carregar a cartucheira a cada tiro, agora escale isso, imagine que existam 20 pessoas e elas têm que buscar munição em um mesmo recipiente em que, somente uma pode retirar o projétil a cada vez - isso é chamado de latch no Oracle - quando o banco bloqueia qualquer outro processo de entrar em determinada área da memória porque ele está realizando um parse.

Para finalizar, um pequeno exemplo de execução com bind e sem bind. Onde encontraremos uma significante a diferença, note que o teste foi realizado em um ambiente com apenas um usuário e em condições ideais. A diferença foi de 27 segundos para 1 segundo em 10.000 registros criados.
Acompanhe:


ops$marcio@ORA10G> create table t ( x int );

Table created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure com_bind ( p_number in number )
2 as
3 begin
4 insert into t values ( p_number );
5 end;
6 /

Procedure created.

ops$marcio@ORA10G> create or replace procedure sem_bind ( p_number in number )
2 as
3 begin
4 execute immediate 'insert into t values ('|| p_number ||')';
5 end;
6 /

Procedure created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> set timing on
ops$marcio@ORA10G> alter session set sql_trace = true;

Session altered.

Elapsed: 00:00:00.13
ops$marcio@ORA10G>
ops$marcio@ORA10G> begin
2 for x in 1..10000
3 loop
4 sem_bind( x );
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:27.90

ops$marcio@ORA10G>
ops$marcio@ORA10G> truncate table t;

Table truncated.

Elapsed: 00:00:01.39
ops$marcio@ORA10G>
ops$marcio@ORA10G> begin
2 for x in 1..10000
3 loop
4 com_bind( x );
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.82

Comments:
Realmente o uso de Bind Variable ajuda demais no desempenho. Eu por exemplo, escrevi uma rotina java que atualizava informações no Oracle. Na primeira versão eu montava uma string com a inserção ou o Update e executava no banco, fazia isso 2000, 3000 vezes. Aí pelo tkprof vi que estava fazendo 2000, 3000 parses. Na segunda versão utilizando PreparedStatement, para que a setença fosse parseada uma vez e depois executada 2000, 3000 vezes o ganho foi excelente. Diminui o processamento de 2 horas para 30 minutos.
 
[quote]
Eu por exemplo, escrevi uma rotina java que atualizava informações no Oracle. Na primeira versão eu montava uma string com a inserção ou o Update e executava no banco, fazia isso 2000, 3000 vezes.
[/quote]


Outra experiência de java que me vem a cabeça foi passar valores para o java, ou seja, o core do sistema estava no banco, packages de insert/delete/update, então os programas java capturavam as entradas no front-end, populavam uma GTT e chamavam a package.procedure que fazia o CRUD. Muito boa técnica também.
Abraço e obrigado pelo follow up.
 
Só uma coisa quanto as bind variables. A própria Oracle as vezes não usa isso, não é uma incoerência meio grande né ?
 
Muito bem lembrado! Versao 8i - Ferramentas gráficas da Oracle, através do statspack dá pra ver tudinho :)
 
Vou só te sugerir executar um alter system flush shared_pool e alter system flush buffer_cache (este ultimo somente no 10g), assim o resultado de performance diretamente um atras do outro fica viciado por conter os planos ja em memoria ai obviamente o segundo ja vai estar rapido.
 
Post a Comment

Links to this post:

Create a Link



<< Home

This page is powered by Blogger. Isn't yours?