Cadê o registro que estava aqui?
Outro dia um amigo meu foi envolvido num processo que começou a dar problema após uma pequena alteração de melhoria de performance.
Após um belo tempo tentando entender o que estava acontecendo ele resolveu me mostrar o código para tentar desvendar o mistério.
Modelagem de teste bem resumida:
SQL>
SQL> desc tst_temp;
Name Null? Type
------------------------------ -------- ------
N NUMBER
SQL> desc tst_fisica;
Name Null? Type
------------------------------ -------- ------
N NOT NULL NUMBER
SQL>
Simplificação do que o processo fazia:
SQL>
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 --
5 INSERT INTO tst_temp VALUES(1);
6 --
7 INSERT INTO tst_fisica VALUES(1);
8 --
9 EXECUTE IMMEDIATE 'ANALYZE TABLE tst_fisica COMPUTE STATISTICS';
10 --
11 SELECT COUNT(1) qtd_temp
12 INTO n
13 FROM tst_temp t1
14 JOIN tst_fisica t2
15 ON t1.n = t2.n;
16 --
17 dbms_output.put_line('Encontrados ' || n || ' registro(s)');
18 --
19 END;
20 /
Encontrados 0 registro(s)
PL/SQL procedure successfully completed.
SQL>
Veja que a mensagem nos diz: Encontrados 0 registro(s)
A pergunta era: Onde foi para o registro que acabei de inserir?
Essa é a pergunta que eu te faço, meu caro leitor.
Tente descobrir o que ocorre neste processo.
Se não tiver a menor ideia, vou dar uma ajudinha.
Seguem as instruções de criação das tabelas usadas nos testes:
SQL>
SQL> CREATE GLOBAL TEMPORARY TABLE tst_temp(n NUMBER);
Table created.
SQL> CREATE TABLE tst_fisica(n NUMBER PRIMARY KEY);
Table created.
SQL>
Vou começar a explicar o que ocorreu, então, se não descobriu o problema, mas ainda quer tentar, pare de ler.
O processo falha por causa da combinação:
- TABELA TEMPORÁRIA POR TRANSAÇÃO
- INSTRUÇÃO DDL
No Oracle, por default, uma tabela global temporary é criada com a característica “ON COMMIT DELETE ROWS”, que faz com que ela seja esvaziada toda vez que uma transação for finalizada, com um commit por exemplo.
Caso não seja esse o comportamento desejado, é necessário complementar a sua instrução de criação com a cláusula “ON COMMIT PRESERVE ROWS”, neste caso a tabela temporária se esvazia automaticamente a cada nova sessão.
Quando apontei esse detalhe fui logo indagado: “mas no meu processo não tem COMMIT”.
Realmente não há commit explícito no código, mas é uma característica do Oracle que toda instrução DDL comita a transação pendente.
No nosso caso a a instrução DDL é justamente a alteração que prometia melhoria de performance:
“ANALYZE TABLE tst_fisica COMPUTE STATISTICS”
Caso estejam pensando numa maneira de contornar o problema, já vou logo avisando que não adianta substituir o ANALYZE pelo dbms_stats:
SQL>
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 --
5 INSERT INTO tst_temp VALUES(2);
6 --
7 INSERT INTO tst_fisica VALUES(2);
8 --
9 dbms_stats.gather_table_stats(USER,'TST_FISICA');
10 --
11 SELECT COUNT(1) qtd_temp
12 INTO n
13 FROM tst_temp t1
14 JOIN tst_fisica t2
15 ON t1.n = t2.n;
16 --
17 dbms_output.put_line('Encontrados ' || n || ' registro(s)');
18 --
19 END;
20 /
Encontrados 0 registro(s)
PL/SQL procedure successfully completed.
SQL>
Vejam que a API também finaliza a transação.
Na ocasião, perguntei ao meu colega (que não trabalha na mesma empresa que eu, nem nos clientes que eu atendo) qual era o prazo que ele tinha para publicar a alteração de performance que ele estava fazendo.
Ele me respondeu que já tinha publicado em ambiente de produção, mas que não sabia que a inclusão desta linha (ANALYZE) podia dar esse tipo de problema, afinal “que mal pode fazer pedir para o Oracle analisar uma tabela?”
Para ele foi um aprendizado e tanto, mas pra quem ainda não pegou, fica o recado:
1 – Antes de incluir chamadas pouco ortodoxas no código, sempre converse com DBAs e outros desenvolvedores.
2 – Testem, testem, testem
A questão da performance em si, eu gostaria de deixar para um próximo post, então, por hoje é só.
SQL> DROP TABLE tst_temp;
Table dropped.
SQL> DROP TABLE tst_fisica;
Table dropped.
SQL> exit
Muito bom Saziba, parabéns pelo post !
Isso mostra que muitos desenvolvedores PL/SQL simplesmente desconhecem o funcionamento do banco de dados Oracle.
Um dia eu ouvi de um infeliz que comandos DDL executados através de execute immediate não comitavam a transação… 🙁