Re: SQL*Net waits in a 10046 trace

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: sfaroult@xxxxxxxxxxxx
  • Date: Wed, 23 May 2007 09:13:04 +0100

I may be wrong, will knock up a test in a bit, but I'd expect that scenario
to show as "SQL*Net more data from client" waits.

On 5/22/07, Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:


      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.


Niall Litchfield
Oracle DBA

Other related posts: