| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Clobs and DBMS_LOB
BicycleRepairman wrote:
> This feature was introduced in 9.0.1, and is not really implicit
> conversion -- at least as far as the developer is concerned. From the
> 9.2 Application Developer's Guide -- Large Objects:
>
> "Using SQL Semantics with LOBs
>
> In this release, for the first time, you can access (internal
> persistent) LOBs using SQL VARCHAR2 semantics, such as SQL string
> operators and functions. By providing you with an SQL interface, which
> you are familiar with, accessing LOB data can be greatly facilitated.
> These semantics are recommended when using small-sized LOBs (~
> 10-100KB)."
>
> Obviously, there are situations where the dbms_lob interface is still
> necessary. But particularly for the type of CLOB data you typically see
> -- document text, comments, etc. -- it makes everything so much easier.
OK, I knew I should have done this from the start - three ways of writing to a CLOB benchmarked, and the results are interesting ...
On 9.2.0.6:-
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
for i in 1 .. 1000 loop
v_clob := v_clob||v_my_data;
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.72 0.98 0 5459 55542
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.72 0.98 0 5459 55542
1
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
dbms_lob.createTemporary(v_clob, true);
for i in 1 .. 1000 loop
dbms_lob.writeappend (v_clob, length(v_my_data),v_my_data);
end loop;
dbms_lob.freeTemporary(v_clob);
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.06 0.07 0 999 2070
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.06 0.07 0 999 2070
1
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
dbms_lob.createTemporary(v_clob, false);
for i in 1 .. 1000 loop
dbms_lob.writeappend (v_clob, length(v_my_data),v_my_data);
end loop;
dbms_lob.freeTemporary(v_clob);
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.44 1.66 1005 999 1087
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.44 1.66 1005 999 1087
1
So the 'implicit' version is much less efficient that uncached temporary clob - which is understandably much less efficient than the cached version. The cached version probably uses more memory, but if its lots lof smallish clobs you are dealing with, then this probably is something you can live with.
In 10G R2 (well actually Oracle XE) things are different. The uncached temporary is the looser, and the cached and implicit version of the code are fairly equal in terms of CPU, although the implicit version does more I/O. Not this is a totally different machine/architecture than the first test, but its the same code.
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
for i in 1 .. 1000 loop
v_clob := v_clob||v_my_data;
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.02 0.02 0 2004 2082
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.02 0.02 0 2004 2082
1
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
dbms_lob.createTemporary(v_clob, true);
for i in 1 .. 1000 loop
dbms_lob.writeappend (v_clob, length(v_my_data),v_my_data);
end loop;
dbms_lob.freeTemporary(v_clob);
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.02 0.02 0 999 2070
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.02 0.02 0 999 2070
1
declare
v_my_data varchar2(100) := 'This is some string that I want to add to
a clob';
v_clob clob;
begin
dbms_lob.createTemporary(v_clob, false);
for i in 1 .. 1000 loop
dbms_lob.writeappend (v_clob, length(v_my_data),v_my_data);
end loop;
dbms_lob.freeTemporary(v_clob);
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.27 0.40 1998 1998 1093
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.27 0.40 1998 1998 1093
1
Cheers,
Stephen. Received on Thu Mar 02 2006 - 05:49:46 CST
 |
 |