Re: Copy blob portion to another blob

Can you benchmark the original procedure on various amounts of data in BLOB? Something like

SQL> set timing on
SQL> exec TransferBlobData(1,1,2,1,5000) Elapsed: nn:nn:nn
SQL> exec TransferBlobData(1,1,2,1,10000) Elapsed: nn:nn:nn
etc.

and compare this to times elapsed copying BLOB to client, trimming and sending it back? Any chance setting timed_statistics=true, sql_trace=true and TKPROFing the trace - maybe you'll see some unusual figures there? Also, try monitoring v$session_wait for waits in your session, these may reveal the contention.
And ensure you have the latest patchset installed on your 8.0.5 instance (8.0.5.2 with some platform-specific patches on NT up to 8.0.5.2.6) - this may be a bug after all...

As of temporary LOB emulation in 8.0, you can try the following (I didn't test it so I am not sure it will work as I expect it to):

create global temporary table tmp_lob
 ( id INT primary key,
   lobc BLOB )
on commit preserve rows
/
create sequence sq$tmp_lob_id
start with 1
increment by 1
/
create or replace procedure createTempLob( lob_loc in out nocopy BLOB ) as

   lob_id INT;
begin
  insert into tmp_lob values(sq$tmp_lob_id.nextval, empty_blob())    returning id into lob_id;
  select lobc into lob_loc from tmp_lob where id = lob_id for update; end;
/

I am not sure if 8.0 supported RETURNING clause, so you may have to populate lob_id in another select before insert. I think this will emulate temporary LOBs, though I don't have a 8.0 instance to verify my claim. Experts are welcome to correct me if I am wrong. :)

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Mike Kluev" <kluev_at_stalker.-deIete-.gamma.ru> wrote in message
news:B92E2B7A.30DF%kluev_at_stalker.-deIete-.gamma.ru...


> Vladimir M. Zakharychev (bob_at_dpsp-yes.com) wrote:

>

> > Dunno for your original question - there may be a lot of reasons

> > why dbms_lob.copy works slowly, starting with poorly tuned

> > database with heavy I/O contention and ending with some bug

> > in the routine - you didn't specify the exact version of Oracle you

> > are using so we're guessing here.

>

> I am using 8.05 and 8.1

>

> The procedure is:

>

> create or replace procedure TransferBlobData(dstID in int, dstOffset in int,

> srcID in int, srcOffset in int, amount in int) is

> src blob;

> dst blob;

> begin

>   select blobData into src from BlobTable where id = srcID;

>   select blobData into dst from BlobTable where id = dstID for update;

>   dbms_lob.copy(dst, src, amount, dstOffset, srcOffset);

>   commit;

> end TransferBlobData;

>

> BlobTable is defined as:

> blobData - blob

> id - int

>

> It works 5-10 times slower than downloading blob to the client,

> trimming it there and uploading it back to the server.

>

> > As of temporary LOBs, they were introduced in 8.1. The only way

> > I can think of for implementing similar (but not exactly)

> > functionality is to use global temporary table with LOB column and

> > ON COMMIT DELETE ROWS, insert an EMPTY_xLOB() into it and

> > immediately select it back FOR UPDATE, then do whatever you

> > please with it, finally copy it to some permanent LOB and COMMIT

> > to dispose the temporary copy. This would roughly emulate

> > a temporaty LOB with dbms_lob.call duration. Roughly, because it

> > will actually emulate inexistent 'transaction' duration. If you need

> > dbms_lob.session duration, use temporary table with ON COMMIT

> > PRESERVE ROWS, this will keep your data in the table for duration

> > of your session.

>

> Here is what I tried first:

>

> create or replace function ReadBlobData(key in int, offset in int, amount in

> int) return blob is

> src blob;

> dst blob;

> begin

>   dbms_lob.createtemporary(dst, TRUE, dbms_lob.call);

>   select blobData into src from ShBlobData where id = key;

>   dbms_lob.copy(dst, src, amount, 1, offset);

>   return dst;

> end ReadBlobData;

>

>

> Obviously, it doesn't work under Oracle 8.05 (there is no createtemporary).

> I tried to change it to:

>

> create or replace function ReadBlobData(key in int, offset in int, amount in

> int, tempBlobID in int) return blob is

> src blob;

> dst blob;

> begin

>   select blobData into dst from ShBlobData where id = tempBlobID for update;

>   select blobData into src from ShBlobData where id = key;

>   dbms_lob.copy(dst, src, amount, 1, offset);

>   return dst;

> end ReadBlobData;

>

> But now I am getting (quite reasonable) "ORA-06571: Function READBLOBDATA

> does not guarantee not to update database" error.

>

> --

> Mike Kluev.

> PS. Remove "-delete-." part of my e-mail address to reply.

>

>