Blog do Marcio Portes

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: Carrega engatilha Dispara, soma 1 em x Se x > 10 pára -- final Retorna em 1.

Situação B Pistola Automática: Carrega engatilha 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