Hi Tom, a real pleasure to be able to submit a question to you...
My original question was going to be why is "method B" below so slow? Then I found a "method A" and was going to ask which is faster:
A) create a temporary clob, put data in it, then insert the clob into a table,
or
B) insert a clob into a table, open it, then write data into it
but then I did my own homework and came up with my own test cases and realized that of course method A is much faster, so now my questions are:
1) if I had these clob operations from method B in a procedure in between a lot of SQL statements, and noticed slow execution times, how would I be able to quickly diagnose that the clob operations (and not the SQL) are the cause of the problem? i.e. These slow clob operations don't show up in a trace & tkprof report, and don't even seem to show up in one of the profiler tools I use either.
2) why is method B so much slower? is there a way to enable cached writing to the clob so it is faster too?
setup:create table temp_clob (id number, text clob);
method A:declare
lc clob; n number := 1;
begin
dbms_lob.CREATETEMPORARY(lc,TRUE);
for rec in (select s.text from dba_source s where rownum <= 1000) loop
dbms_lob.writeappend(lc,length(rec.text),rec.text);
end loop;
INSERT INTO temp_clob VALUES (n, lc);
dbms_lob.FREETEMPORARY(lc);
end;
This took only 0.187 seconds to run. Woohoo!method B:declare
lc clob; n number := 1;
begin
EXECUTE IMMEDIATE 'INSERT INTO temp_clob VALUES (:i1, empty_clob()) RETURNING text INTO :o1'
using IN n, OUT lc;
dbms_lob.open(lc, DBMS_LOB.lob_readwrite);
for rec in (select s.text from dba_source s where rownum <= 1000) loop
dbms_lob.writeappend(lc,length(rec.text),rec.text);
end loop;
dbms_lob.close(lc);
end;
This took a whopping 24 seconds to run!
I do not see the same personally, however, I can say that working on the item in temp will in general work in memory (until it gets large then temp), without redo and without using direct path IO.
by default, your table clob is NOCACHE, every read, every write is a direct IO (there were lots of direct read/write waits in the method B, not so in method A).
lobs are versioned in the lob segment - not undo. So, each modification you made had to version itself in the lob segment using direct IO as well.
By doing it in "temp", you did it mostly in memory - without physical IO.
Probably, if you created your table:
create table temp_clob( n number, text clob ) lob (text) store as ( cache );
you would see more "similar" results - but doing the thing "in memory" and then putting it into the table would make sense for something like this.
Just like if I was to use utl_file to read lines and write them out - I would find it to be faster to build up a big string with as much data as I could and call utl_file.fwrite less often....