ODAC Direct Connection very Slow

flls · Post by **flls** » Tue 21 Oct 2008 18:14

I have the query below:

SELECT   a1.tipo_local LOCAL,               
         nome_tipo_local (a1.tipo_local) nomelocal, i1.DATA, t.descricao,
         SUM (i1.area) cm,
         SUM (calc_proporcional(a1.total_publicacoes,
                                a1.total_cores,
                                a1.total_bonus,
                                a1.desconto,
                                a1.desconto_condicional,
                                i1.valor_total,
                                i1.valor_cor,
                                i1.bonificado,
                                pega_cobrar_cores_contrato (a1.cod_cli,
                                                            a1.numero_contrato)
                               )) valor
    FROM anuncios_itens i1, anuncios a1, tabela_precos_itens t
   WHERE (i1.DATA BETWEEN :data1 AND :data2)
     AND (i1.situacao NOT IN (2, 3))   /* Exclui compensação e reprogramado */
     AND (i1.suspenso = 'N')
     AND (a1.num_pedido = i1.num_pedido)
     AND (a1.produto = :cod_prod)
     AND (a1.cancelado = 'N')
     AND (a1.situacao_bloqueio IN (0, 2))
     AND (t.cod_tab = a1.tabela_precos)
     AND (t.item = a1.tabela_precos_item)
GROUP BY i1.DATA,
         a1.tipo_local,
         nome_tipo_local (a1.tipo_local),
         t.descricao             
ORDER BY i1.DATA, a1.tipo_local, t.descricao

When the run with the Odac using the Oracle OCI 11 it runs in 0.41 sec.

When the run with the Odac using the direct connection (TCP-IP) to Oracle 11 it runs in 28.0 sec.

Why? I have no firewall on the server and not on the client.

With Oracle 9 the direct connection run faster.

Thanks.

Sorry by my BAD english

Plash · Post by **Plash** » Fri 24 Oct 2008 07:37

Maybe you are using LOBs in your query. LOB fields are fetched slowly in the Direct mode. This is a known problem.