Re: SQL*Net waits in a 10046 trace

Peter,

      I believe that if your blobs are big enough, this is very likely. The problem with blobs is that, contrarily to other datatypes, the type tells Oracle nothing about the generic size of the column (with a (n)varchar2 you know that all rows will be at most n bytes long for this column). Therefore you can either issue a first query to get the size of the specific blob you want to get, dynamically allocate a buffer, then fetch. But this assumes that you can allocate as much memory as you want. The safer approach is therefore to have a given buffer size and proceed with a piece-wise fetch, getting blob chunk after blob chunk and pasting the pieces together on the client side. A small buffer/big blob combination should give the symptoms you get, particularly if the server takes time to retrieve the blob and the client takes time to process what it has painfully retrieved.

HTH Stéphane Faroult

Schauss, Peter wrote:
> I am trying to shake out some performance issues in an application which
> we are putting up (Peregrine Service Center) on Oracle 10.2.0.2.0, HPUX.
> One particular query retrieves 67 rows and runs for about 11 seconds.
> The trace file shows multiple pairs of "SQL*Net message to
> client"/"SQL*Net message from client" entries which seem to account for
> a good bit of the elapse time. One of the columns retrieved is a blob.
>
> Is it possible that the multiple SQL*Net waits have something to do with
> transfering the blob data?
>
> Thanks,
> Peter Schauss
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l