Re: SQL*Net waits in a 10046 trace
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: peter.schauss@xxxxxxx
- Date: Wed, 23 May 2007 00:13:04 +0200
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
