MySQL – Where can I find metrics on the performance of Blob vs file system

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.

1. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).

2. Call expdp as usual, adding:

   REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION
   

Short example (schema: mat):

create or replace 
package remap as 
  function null_lob(col blob) return blob;
end remap;
/

create or replace
package body remap as
  function null_lob(col blob)
    return blob is
    eblob blob;
  begin
    return null;
  end null_lob;
end remap;
/

create table foo(a number, b blob);
-- insert a line with a 10M blob, commit

select a, length(b) from foo;

     A  LENGTH(B)
---------- ----------
     1   10485760

Plain export:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO 
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 10.00 MB       1 rows
...

Dump file is ~11M.

With the data remap:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO \
                REMAP_DATA=MAT.FOO.B:MAT.remap.null_lob
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 5.398 KB       1 rows
...

Dump file is ~100k, importing that dump does indeed import a null in the column.

Another option would be to use an external table to do the load/unload, with the ORACLE_DATAPUMP driver (I think this was introduced in 10g, see external table concepts).

For the unload, use something like:

create table foo_unload
  organization external (
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  )
as select a, empty_blob() b from foo;

(You can drop the external table right after creating it, that doesn't - by default anyway - delete the dump file.)

Then move the foo.dmp file to your target server & directory, and create the external table there:

create table foo_load (a number, b blob)
  organization external
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  );

And finally load your target table (CTAS or truncate ...; insert /*+ append */ ... for instance):

create table foo as select * from foo_load;

Drop the external loader table once that's done.

There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor.

If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this trigger. Or you can set tracing in other session using dbms_monitor.

You should first "prove" that the root cause is BLOB handling. Server side log should give you some numbers. (Just beware - it's not easy to read).

PS: there is also a way how you can register your "interceptor" library in OCI.dll. This will give detailed tracing for the client's side and will tell you how your DAO is working. But this really requires some Oracle experience.