May 24, 2004 - 4pm Central time zone
Reviewer: jim
The reason I am asking this questin is because for this specific table, I need insert a lot of row
into it. by using varchar2, I can avoid using index, but it seems to me each and every lob needs an
index on it.
and for this specific table even though the length of row vary, it rarely more than 4000.
are you saying that if it is less than 4000, clob is strored EXACTLY same as varchar2? i mean no
index was created on top of it and insert gives us the same performance?
Followup May 24, 2004 - 6pm Central time zone:
benchmark it?
when less then 4000 bytes, and enable storage in row is set (default), then yes, it is stored right
there in the block, no index segment, no lob pages.
create table t1 ( x int primary key, y varchar2(4000) );
create or replace procedure p1( p_n in number default 100 )
as
l_data varchar2(4000);
begin
for i in 1 .. p_n
loop
l_data := rpad( '*', mod(i,3500)+1, '*' );
insert into t1 values ( i, l_data );
end loop;
end;
/
create table t2 ( x int primary key, y clob );
create or replace procedure p2( p_n in number default 100 )
as
l_data varchar2(4000);
begin
for i in 1 .. p_n
loop
l_data := rpad( '*', mod(i,3500)+1, '*' );
insert into t2 values ( i, l_data );
end loop;
end;
/
INSERT into t1 values ( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 1.37 1.67 4 1214 50656 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.37 1.67 4 1214 50656 10000
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 8 0.00 0.00
control file sequential read 24 0.00 0.00
async disk IO 2 0.00 0.00
db file single write 2 0.00 0.00
control file parallel write 4 0.00 0.00
********************************************************************************
INSERT into t2 values ( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 1.63 1.73 0 1545 51038 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.63 1.73 0 1545 51038 10000
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 12 0.00 0.00
db file sequential read 2 0.00 0.00
async disk IO 1 0.00 0.00
db file single write 1 0.00 0.00
control file parallel write 2 0.00 0.00
********************************************************************************
confused
May 24, 2004 - 7pm Central time zone
Reviewer: A reader
Here is my output, it seems it confirm my thought that varchar2 do better than clob in terms of
performance.
how come my result is so different from yours
I run it twice , here is the result.
first run
====================
********************************************************************************
INSERT into t2
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 4.93 30.55 7 6175 79436 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 4.93 30.55 7 6175 79436 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************
INSERT into t1
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 10000 1.77 5.65 5 1249 55098 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.78 5.65 5 1249 55098 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
second run
==============================
INSERT into t1
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 2 0 0
Execute 10000 2.11 4.89 0 1315 55085 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 2.11 4.96 0 1317 55085 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************
INSERT into t2
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 10000 5.06 30.57 0 6164 79423 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 5.07 30.58 0 6164 79423 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************
Followup May 24, 2004 - 11pm Central time zone:
you would need to provide all relevant information, db version being somewhat "the most important".
OS being relevant.
i used 9204 on linux - as is, pretty much "out of the box". 8k blocksize. 3meg redo log buffer.
64m block buffers.
run it with a 10046 level 12 trace like I did, post the results with waits.
You used my code "as is, no variations" right?
report
May 25, 2004 - 2pm Central time zone
Reviewer: A reader
I used your code "as is"
Then I issue
exec p1(10000) and exec p2(10000)
db is 9204 on linux,8k block size, 10m redo log buffer, 20000 db_block_bufer, here is the result.
********************************************************************************
BEGIN p1(10000); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 1 0
Execute 1 1.88 2.14 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.89 2.17 0 0 1 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.21 0.21
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 6.80 6.80
********************************************************************************
INSERT into t1
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 2.67 2.59 0 1043 34555 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 2.67 2.59 0 1043 34555 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************
********************************************************************************
BEGIN p2(10000); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 1 0
Execute 1 7.47 20.52 0 6195 50939 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.49 20.54 0 6195 50940 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.10 0.10
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.95 1.95
********************************************************************************
INSERT into t2
values
( :b2, :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 5.62 18.67 0 9867 59179 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 5.62 18.67 0 9867 59179 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write (lob) 4055 0.00 0.04
********************************************************************************
Followup May 25, 2004 - 2pm Central time zone:
what linux build? I mean, my numbers were from a *laptop* for linux -- and I got comparable ones
on windows running in vmware on the same.
you see to be getting wildly varying response times.
also, I see direct path write(lob) -- that would indicate to me that the lob was created with
"disable storage in row", that is a write to the lob segment which won't happen until the lob
exceeds about 4000 bytes.
do this,
ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T2')
------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T2"
( "X" NUMBER(*,0),
"Y" CLOB,
PRIMARY KEY ("X")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("Y") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
and
ops$tkyte@ORA9IR2> select min(dbms_lob.getlength(y)), max(dbms_lob.getlength(y)),
sum(dbms_lob.getlength(y))
2 from t2;
MIN(DBMS_LOB.GETLENGTH(Y)) MAX(DBMS_LOB.GETLENGTH(Y))
-------------------------- --------------------------
SUM(DBMS_LOB.GETLENGTH(Y))
--------------------------
1 3500
16758000
May 25, 2004 - 3pm Central time zone
Reviewer: A reader
SQL> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3430
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4259
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
no rows selected
SQL> select min(dbms_lob.getlength(y)), max(dbms_lob.getlength(y)), sum(dbms_lob.getlength(y))
from t2 2 /
MIN(DBMS_LOB.GETLENGTH(Y)) MAX(DBMS_LOB.GETLENGTH(Y)) SUM(DBMS_LOB.GETLENGTH(Y))
-------------------------- -------------------------- --------------------------
1 3500 16758000
Followup May 25, 2004 - 3pm Central time zone:
Lpx, i know that guy -- what is your character set -- i'll betcha that's the culprit.
just for grins:
select dump('*') from dual;
for me.
May 26, 2004 - 1am Central time zone
Reviewer: Ian Matyssik from Japan, Tokyo
Tom,
I have same problem with DBMS_METADATA.GET_DDL, my database is 9.2.0.5 with AL32UTF8 Character
set. Could you please let me know how can that be fixed if possible. And also if you know what
problems can happen when using UTF8 as you database character set. I think I also noticed some
strange things with dbms_obfuscation_toolkit.
Thank you.
Followup May 26, 2004 - 8am Central time zone:
with dbms_obfuscation_toolkit you'll get "strange" things period if you use the varchar2 api (wish
they didn't do that). you want to use ONLY raw, only RAW. else character set conversion will kill
you every time.
for dbms_metadata throwing errors, please contact support.
here it is
May 26, 2004 - 2pm Central time zone
Reviewer: A reader
SQL> select dump('*') from dual;
DUMP('*')
----------------
Typ=96 Len=1: 42
Followup May 26, 2004 - 4pm Central time zone:
what is your character set -- i'll betcha that's the
culprit.
May 27, 2004 - 1pm Central time zone
Reviewer: A reader
it is UTF8, why?
Followup May 27, 2004 - 8pm Central time zone:
it must be multi-byte causing the bytes stored for this test to exceed 4000 (else the lob would
*not* overflow). Interesting.
do you need utf8 or is a single byte characterset more than good enough?
I would still go with LOB personally.
July 23, 2004 - 1pm Central time zone
Reviewer: A reader
Update a varchar2 column
November 3, 2004 - 8pm Central time zone
Reviewer: Robert from CT
Table1(id number, info_target varchar2(2000))
Table2(id number, info_source CLOB)
How can I join-update Table1 so that 2000 bytes of
Table2.info_source is written to Table1.info_target ?
thanks
Followup November 5, 2004 - 11am Central time zone:
assuming a primary/unique constraint on table2(id)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 (id number, info_target varchar2(2000)) ;
Table created.
ops$tkyte@ORA9IR2> create table t2(id number primary key, info_source CLOB);
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 'hello world....' || rpad( '*', 3000, '*' ) );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update (select info_target, dbms_lob.substr( info_source, 2000, 1 ) data
2 from t1, t2
3 where t1.id = t2.id)
4 set info_target = data;
1 row updated.
|