Wednesday, March 21, 2007
Como Otimizar um PL/SQL
Um colega me disse que é difícil otimizar programas. Realmente, se pensarmos bem, não é uma tarefa que se ensina em curso. A maioria dos DBAs também têm dificuldade em otimizar sentenças SQL ou códigos PL/SQL, porque não é tarefa de seu dia-a-dia. Quanto mais próximo do código, mais são as chances do profissional aprender como escrever programas eficientes em PL/SQL. Também existem os códigos egípcios puxados para o grego, cujo autor é o único que os entende. Já viu!? As variáveis são definidas como "x", "y" e "z" sem dizer coisa alguma além de não oferecer sequer um comentário. Terrível! "Indentação"? Para quê? Algumas vezes dá vontade de chamar o Tank para decifrar.
Antes de começar, um lembrete: O que voce lerá daqui para frente serviu a mim, isso não quer dizer que servirá a voce. Tome as dicas aqui como referência e não deixe de fazer o seu guia para otimização.
Por Onde Começar?
Entendimento! Esse é o começo. Entender o código, o que faz, qual o caminho está percorrendo, quais as tabelas envolvidas, quantidade de linhas, índices, seletividade, ou seja, todo o conhecimento que provavelmente qualquer profissional acumula durante a carreira é usado na otimização de código.
No começo, é usar o que já sabemos. Vou usar meu exemplo: na minha carreira até agora, tudo que eu posso fazer usando SQL puro, faço (em termos de programação em banco de dados Oracle). Então, quando me pedem para olhar (otimizar) uma procedure, vou primeiro entender o programa. Validar com o negócio se realmente eles precisam da rotina (muitas vezes, uma rotina é desnecessária e está lá, porque ninguém nunca perguntou). Verificar se eu não poderia escrever aquilo de outra forma, usando SQL puro. A técnica é parecida com tirar cascas de cebola, uma camada de cada vez. Nunca vou direto no tkprof ou autotrace, essa parte é do refinamento, quando já reescrevi o procedimento.
Vamos desenvolver um pequeno exemplo: as tabelas envolvidas serão EMP e DEPT do esquema SCOTT, elas estão disponíveis em $ORACLE_HOME/utlsampl.sql. Farei um backup da dept na dept_old (create table dept_old as select * from dept), depois removo todas as linhas da dept e tomando a dept_old como base, irei repopular a dept caso o departamento (deptno) esteja em uso na emp. Bastante simples. Agora vamos ver o código para isso.
Agora vou tentar explicar o que vejo, iniciando com o switch de contexto, que é a troca seguida entre a engine de SQL e PL/SQL. Exatamente como acontece no código acima, dentro de um cursor utiliza-se sentenças SQL.
Isso deve ser evitado, melhor, deveria ser proibido! Então, já descobrimos o primeiro problema do nosso código e, só isso, já seria argumento suficiente para reescrevê-lo. Seguindo, temos um teste de existência. Honestamente, eu NUNCA testo nada para inserir ou atualizar, eu simplesmente envio o comando, se não existe, paciência! E quando tenho que testar, coloco uma restrição para não haver problema de performance. Em nosso exemplo, temos:
Concluindo
Agora que avaliamos o código e entendemos o que deve ser feito, vimos todas as suas fraquezas, hora de tomar uma decisão. Apenas retomando, não precisei de tkprof nem de autotrace para avaliar esse código e notar que está muito mal feito. A solução para esse procedimento é a reescrita! Então, relembrando a semântica: inserir na dept caso exista em emp da dept_old.
Antes de começar, um lembrete: O que voce lerá daqui para frente serviu a mim, isso não quer dizer que servirá a voce. Tome as dicas aqui como referência e não deixe de fazer o seu guia para otimização.
Por Onde Começar?
Entendimento! Esse é o começo. Entender o código, o que faz, qual o caminho está percorrendo, quais as tabelas envolvidas, quantidade de linhas, índices, seletividade, ou seja, todo o conhecimento que provavelmente qualquer profissional acumula durante a carreira é usado na otimização de código.
No começo, é usar o que já sabemos. Vou usar meu exemplo: na minha carreira até agora, tudo que eu posso fazer usando SQL puro, faço (em termos de programação em banco de dados Oracle). Então, quando me pedem para olhar (otimizar) uma procedure, vou primeiro entender o programa. Validar com o negócio se realmente eles precisam da rotina (muitas vezes, uma rotina é desnecessária e está lá, porque ninguém nunca perguntou). Verificar se eu não poderia escrever aquilo de outra forma, usando SQL puro. A técnica é parecida com tirar cascas de cebola, uma camada de cada vez. Nunca vou direto no tkprof ou autotrace, essa parte é do refinamento, quando já reescrevi o procedimento.
Vamos desenvolver um pequeno exemplo: as tabelas envolvidas serão EMP e DEPT do esquema SCOTT, elas estão disponíveis em $ORACLE_HOME/utlsampl.sql. Farei um backup da dept na dept_old (create table dept_old as select * from dept), depois removo todas as linhas da dept e tomando a dept_old como base, irei repopular a dept caso o departamento (deptno) esteja em uso na emp. Bastante simples. Agora vamos ver o código para isso.
O que pensar de um código assim? Horrível não? Começando com o temido switch de contexto, caminhando com count(*) sem restrição para testar se o registro existe e finalizando com um select que popula variáveis para finalmente inserir na dept. Ok, muita calma, leia o código e entenda o que faz. Mas entenda a semântica, não a sintaxe, ou seja, entenda o significado. Resumindo: insira um registro na dept caso exista na emp da dept_old. Concorda? Ora, ele vem de um cursor da emp, testa com count(*) para saber se já existe na dept e se não existir insira usando dados da dept_old.
declare
l_exist number(1) default 0;
l_dname dept_old.dname%type;
l_loc dept_old.loc%type;
begin
for x in ( select deptno from emp )
loop
select count(*) into l_exist
from dept
where dept.deptno = x.deptno;
if ( l_exist = 0 ) then
select dname, loc into l_dname, l_loc
from dept_old
where dept_old.deptno = x.deptno;
insert into dept values ( x.deptno, l_dname, l_loc );
end if;
end loop;
end;
/
Agora vou tentar explicar o que vejo, iniciando com o switch de contexto, que é a troca seguida entre a engine de SQL e PL/SQL. Exatamente como acontece no código acima, dentro de um cursor utiliza-se sentenças SQL.
(1) OPEN x in ( SELECT ... )Em (1) a engine de SQL executa a query e envia para a engine de PL/SQL que fará o looping. O problema é que em (2) há novamente uma sentença em SQL que a engine de PL/SQL não executa, então novamente há cessão de execução à engine de SQL, que executa e VOLTA para engine de PL/SQL, para seguir com o looping... mas... em (3) novamente uma sentença SQL e a engine de SQL é chamada, executa e devolve para engine de PL/SQL, para a finalização da primeira linha do looping. Basicamente, isso é switch de contexto.
loop
(2) SELECT y INTO ... FROM ...;
(3) UPDATE z SET w=... WHERE x.m =...
end loop;
Isso deve ser evitado, melhor, deveria ser proibido! Então, já descobrimos o primeiro problema do nosso código e, só isso, já seria argumento suficiente para reescrevê-lo. Seguindo, temos um teste de existência. Honestamente, eu NUNCA testo nada para inserir ou atualizar, eu simplesmente envio o comando, se não existe, paciência! E quando tenho que testar, coloco uma restrição para não haver problema de performance. Em nosso exemplo, temos:
select count(*) into l_existNão há limite para o count(*), se a tabela dept tivesse 1 milhão de linhas ele faria a contagem de 1 milhão de linhas (POR NADA) e devolveria em l_exist. Realmente o que é preciso para saber que um dept existe? Apenas uma linha, portanto, seria muito mais eficiente se puséssemos no predicado uma restrição como: AND ROWNUM=1, porque basta achar 1 registro. O otimizador conhece o rownum=1 e faz um COUNT STOPKEY.
from dept
where dept.deptno = x.deptno;
Seguindo em nossa avaliação eu pergunto: por que selecionar os valores em variáveis e depois inserir? Faça o insert direto (se fosse o caso!).
ops$marcio:LX10G> select count(*) from t1;
COUNT(*)
-------------
53094
1 row selected.
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IT1_OBJECT_ID | 53094 | 28 (4)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
124 consistent gets
...
ops$marcio:LX10G> select count(*) from t1 where rownum=1;
COUNT(*)
-------------
1
1 row selected.
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | INDEX FAST FULL SCAN| IT1_OBJECT_ID | 53094 | 28 (4)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
...
select dname, loc into l_dname, l_locEntão, nesse caso, o correto seria apenas um comando SQL (pensando em recursos, cada sentença SQL movimenta CPU, evitando desperdícios de sentenças, estamos economizando recurso de máquina).
from dept_old
where dept_old.deptno = x.deptno;
insert into dept values ( x.deptno, l_dname, l_loc );
insert into deptA instrução acima faria a mesma coisa, economizando CPU e memória.
select deptno, dname, loc
from dept_old
where dept_old.deptno = x.deptno;
Concluindo
Agora que avaliamos o código e entendemos o que deve ser feito, vimos todas as suas fraquezas, hora de tomar uma decisão. Apenas retomando, não precisei de tkprof nem de autotrace para avaliar esse código e notar que está muito mal feito. A solução para esse procedimento é a reescrita! Então, relembrando a semântica: inserir na dept caso exista em emp da dept_old.
insert into dept
select deptno, dname, loc
from dept_old
where exists ( select null
from emp
where emp.deptno = dept_old.deptno )
/
Labels: Performance
