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 puco 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… 🙁