Categoria: banco de dados

Nesse artigo vou fazer um comparativo das opções de implementação com Oracle e Java, usando uma rotina que vai ler umas tabelas para buscar o CEP e depois fazer a carga em outra.

As opções que temos:

– Antes de existir Java: a velha e boa stored procedure em PL/SQL … é um código pré-compilado em p-code que fica armazenado dentro do banco de dados… acesso local e nativo aos dados.

– Java client com driver JDBC Thin – acesso remoto aos dados.

– Java client com driver OCI Thin – acesso remoto e nativo aos dados.

– Java em stored procedure – desde o Oracle 8i temos uma engine em Java dentro do banco, o que nos possibilita executar rotinas em Java dentro do banco, com acesso local e nativo aos dados.

É impressionante a quantidade de profissionais Java que trabalha há anos com Oracle não conhece metade das opções existentes.

A implementação mais usada é a JDBC Thin, pois é a mais fácil de configurar, mas de acordo com a Oracle é a mais lenta de todas.

O Oracle OCI (Oracle Call Interface) é a maneira nativa que os clients utilizam para se conectar ao banco de dados, essa maneira nativa pode ser feita em várias linguagens (C, Cobol , Java) utilizando as bibliotecas do Oracle Client para acessar o banco de dados. Isso significa que para usar OCI, a instalação do Oracle client é obrigatória.

Ferramentas como Oracle SQL*Plus, PL/SQL Developer são rápidas porque usam OCI para acessar o banco de dados.

A configuração adequada do OCI pode ser complicada a primeira vez, e somente nesse fórum russo eu encontrei um simples resumo que explica como configurar o seu ambiente para usar OCI (depois de instalado o Oracle Client):

Em plataforma Windows:

– Adicione [ORACLE_HOME] \ jdbc \ lib \ ojdbc5.jar ao CLASSPATH para JDK 1.5 ou
[ORACLE_HOME] \ jdbc \ lib \ ojdbc6.jar para JDK 1.6.
– Adicione [ORACLE_HOME] \ jlib \ orai18n.jar ao CLASSPATH.
– Adicione [ORACLE_HOME] \ bin ao PATH (que fica nas variáveis de ambiente, nas configurações avançadas do Sistema)

Em plataforma Unix:

– Adicione [ORACLE_HOME] / jdbc / lib / ojdbc5.jar ao CLASSPATH para JDK 1.5 ou
[ORACLE_HOME] / jdbc / lib / ojdbc6.jar para JDK 1.6.
– Adicione [ORACLE_HOME] / jlib / orai18n.jar ao CLASSPATH.
– Adicione [ORACLE_HOME] / jdbc / lib ao LD_LIBRARY_PATH

Para trabalhar com Oracle Stored Procedures, você precisa primeiro ter uma ferramenta como Eclipse para desenvolver suas rotinas, e depois gerar suas classes com compatibilidade para JDK 1.5 (se for Oracle 11i) e JDK 1.4 (se for Oracle 10g).

Além disso, a opção de Java deve estar ativa no banco de dados, verifique com o SQL:

SQL> select value from V$option where parameter='Java';

VALUE
-------------------------------------------------------
TRUE

Essa opção Java não está disponível no Oracle Express Edition, é preciso instalar uma versão Personal Oracle, Standard ou Enterprise Edition.

Para carregar as classes em Java no banco é utilizado o loadjava, como por exemplo para carregar a nossa classe para o teste da corrida:

C:\workspace\corridaOracle\bin\>loadjava -user=boaglio1/boaglio1@boaglio1 -verbose TesteDeInternalProcedure.class

arguments: '-user' 'boaglio1/***@boaglio1' '-verbose' 'TesteDeInternalProcedure.
class'
creating : class com/boaglio/corridaOracle/TesteDeInternalProcedure
loading  : class com/boaglio/corridaOracle/TesteDeInternalProcedure
Classes Loaded: 1
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

E depois amarramos essa classe com uma procedure, dessa maneira:

create or replace procedure teste_java
 as
 language java 
 name 'com/boaglio/corridaOracle/TesteDeInternalProcedure.main(java.lang.String[])';

Bom, vamos falar agora do processo de carga… temos o primeiro exemplo que é executado inteiramente em PL/SQL:

create or replace procedure teste_proc
is
 cursor todos_ceps is select * from cep;
 id_corrida integer;
begin

select corrida.nextval into id_corrida from dual;

for linha in todos_ceps loop
 if loweR(linha.cidade) like '%paulo%' then
  INSERT INTO TESTE_PROCEDURE (ID,UF,CIDADE,BAIRRO,ENDERECO_CEP,LOGRADOURO,ID_CORRIDA)
  VALUES (seq1.nextval,linha.uf,linha.cidade,linha.bairro,linha.endereco_cep,linha.logradouro,id_corrida);
 end if;
end loop;
  
end teste_proc;

Depois temos o outro exemplo que será chamado por todas as outras classes Java:

package com.boaglio.corridaOracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CargaDeDadosDoCEP {

    static final String todos_ceps = "select * from cep";

    static final int DRIVER_JDBC_THIN=1;
    static final int DRIVER_JDBC_OCI=2;
    public  static final int INTERNAL_JAVA_PROC=3;

    public static void runLoad(Connection conexao,int tipoDeDriver) {

        Statement stmt = null;
        ResultSet rset = null;
        try {
            int idCorrida = buscaIdCorrida(conexao);
            stmt = conexao.createStatement();
            rset = stmt.executeQuery(todos_ceps);
            while (rset.next())
                validaDados(rset,conexao,tipoDeDriver,idCorrida);
        } catch (Exception ignore) {
        } finally {
            try {
                rset.close();
                stmt.close();
                if (tipoDeDriver!=CargaDeDadosDoCEP.INTERNAL_JAVA_PROC)
                   conexao.close();
            } catch (Exception ignore) {
            }
        }
    }

    private static int buscaIdCorrida(Connection conexao) {

        Statement stmt = null;
        ResultSet rset = null;
        int idCorrida =0;
        try {
            stmt = conexao.createStatement();
            rset = stmt.executeQuery("select corrida.nextval from dual");
            while (rset.next())
                idCorrida = rset.getInt(1);
        } catch (Exception ignore) {
        } finally {
            try {
                rset.close();
                stmt.close();
            } catch (Exception ignore) {
            }
        }
        return idCorrida;
    }

    private static void validaDados(ResultSet rset,Connection conexao,int tipoDeDriver,int idCorrida) throws SQLException {

        String cidade = rset.getString("cidade");
        String busca = "paulo";

        String SQL_CARGA="";
        if (tipoDeDriver==DRIVER_JDBC_THIN) {
            SQL_CARGA="INSERT INTO TESTE_DRIVER_THIN(ID,UF,CIDADE,BAIRRO,ENDERECO_CEP,LOGRADOURO,ID_CORRIDA) "+
                       "VALUES (seq2.nextval,?,?,?,?,?,?)";
        } else if (tipoDeDriver==DRIVER_JDBC_OCI) {
            SQL_CARGA="INSERT INTO TESTE_DRIVER_OCI (ID,UF,CIDADE,BAIRRO,ENDERECO_CEP,LOGRADOURO,ID_CORRIDA) "+
            "VALUES (seq3.nextval,?,?,?,?,?,?)";
        } else	if (tipoDeDriver==INTERNAL_JAVA_PROC) {
            SQL_CARGA="INSERT INTO TESTE_JAVA_PROC (ID,UF,CIDADE,BAIRRO,ENDERECO_CEP,LOGRADOURO,ID_CORRIDA) "+
            "VALUES (seq4.nextval,?,?,?,?,?,?)";
        }
        if (cidade==null) return;
        if (cidade.toLowerCase().indexOf(busca)>=0) {
            PreparedStatement pstmt = null;
            try {
                    pstmt = conexao.prepareStatement(SQL_CARGA);
                pstmt.setString(1,rset.getString("uf"));
                pstmt.setString(2,cidade);
                pstmt.setString(3,rset.getString("bairro"));
                pstmt.setString(4,rset.getString("endereco_cep"));
                pstmt.setString(5,rset.getString("logradouro"));
                   pstmt.setInt(6,idCorrida);
                pstmt.executeUpdate();

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                pstmt.close();
                } catch (Exception ignore) {
                }
            }
        }
    }
}

Em ambos os casos temos o mesmo processo: buscar em uma View de todos os endereços do Brasil os que contém “paulo” no nome da cidade e armazenar em uma tabela separada.

Temos 57789 de 699307 registros (8,25%) em cada execução da rotina.

O cálculo foi feito da diferença do primeiro e o último insert (o tempo de 57789 inserções na base).

O teste foi feito sem envolver rede, no próprio servidor para tirar essa dúvida da nossa corrida.

Em todos os quatro casos as rotinas foram executadas dez vezes, com a seguinte performance:

1. Em primeiro lugar não é nenhuma surpresa que foi o PL/SQL, com tempo mínimo de 10 segundos, máximo de 12 e médio de 11.3
2. Em segundo lugar, pertinho vieram as stored procedures em Java, com tempo mínimo de 23 segundos, máximo de 25 e médio de 23,9
3. Em terceiro lugar, bem atrás vieram os drivers JDBC Thin, com tempo mínimo de 59 segundos, máximo de 79 e médio de 67
4. Em último lugar vieram os drivers JDBC OCI, com tempo mínimo de 64 segundos, máximo de 95 e médio de 71,9

Vamos fazer algumas comparações com os tempos médios… a mesma rotina em PL/SQL roda 2 vezes mais rápido que uma stored procedure Java e 6 vezes mais rápido uma rotina de OCI, isso mesmo 6 vezes!

O interessante é que a Oracle recomenda usar o driver Thin para maior portabilidade e o driver OCI para melhor performance, mas nessa minha corrida e em outras literaturas dizem exatamente o oposto.

Quer testar você mesmo? Acesse o fonte do projeto e tire suas próprias conclusões!