Home » SQL & PL/SQL » SQL & PL/SQL » difference between dbms_lob.substr and SUBSTR
| difference between dbms_lob.substr and SUBSTR [message #123760] |
Tue, 14 June 2005 14:02  |
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
|
Senior Member |
|
|
I have a table t1 and field f1 is defined as CLOB.
I do a select in 2 different way and get the same result.
create table t1
(f1_id number,
f2_text CLOB);
insert into t1 values (1, 'This is a test record');
select substr(f2_text,1,14) from t1;
Result: This is a test.
select dbms_lob.substr(f2_text,14,1) from t1;
Result: This is a test.
Is there a difference then between dbms_lob.substr and substr really.....
Or it my performace improve if I use dbms_lob.substr when I have 100,000 records and I do some operation after doing the sub string....
Anyone has any knowledge on this, Please share.
Thanks much.
Ravi
|
|
|
|
| Re: difference between dbms_lob.substr and SUBSTR [message #123773 is a reply to message #123760] |
Tue, 14 June 2005 15:49  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Well, what were the results of your testing?
For example, I used a test harness by Oracle guru Tom Kyte, freely available from here.
Then I ran a test, comparing the SUBSTR approach to the DBMS_LOB.SUBSTR approach:SQL> EXEC runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_clob CLOB;
3 l_text VARCHAR2(30);
4 BEGIN
5 l_clob := 'This is a test record';
6 FOR i IN 1..10000
7 LOOP
8 l_text := SUBSTR(l_clob,1,14);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_clob CLOB;
3 l_text VARCHAR2(30);
4 BEGIN
5 l_clob := 'This is a test record';
6 FOR i IN 1..10000
7 LOOP
8 l_text := DBMS_LOB.SUBSTR(l_clob,14,1);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC runstats_pkg.rs_stop(500);
Run1 ran in 6387 hsecs
Run2 ran in 109 hsecs
run 1 ran in 5859.63% of the time
Name Run1 Run2 Diff
LATCH.redo writing 847 5 -842
LATCH.session allocation 1,003 0 -1,003
LATCH.dml lock allocation 1,055 0 -1,055
LATCH.simulator lru latch 1,071 0 -1,071
LATCH.undo global data 1,090 6 -1,084
LATCH.redo allocation 1,673 10 -1,663
LATCH.enqueue hash chains 1,812 2 -1,810
LATCH.messages 2,025 10 -2,015
LATCH.session idle bit 2,450 18 -2,432
LATCH.multiblock read objects 2,794 0 -2,794
LATCH.library cache pin alloca 4,282 22 -4,260
LATCH.simulator hash latch 4,982 0 -4,982
LATCH.SQL memory manager worka 6,228 67 -6,161
STAT...CPU used by this sessio 6,340 122 -6,218
STAT...CPU used when call star 6,340 122 -6,218
STAT...Elapsed Time 6,406 127 -6,279
LATCH.library cache pin 8,925 68 -8,857
LATCH.checkpoint queue latch 9,032 105 -8,927
LATCH.shared pool 9,083 85 -8,998
STAT...free buffer requested 10,002 8 -9,994
STAT...consistent gets 20,005 10,008 -9,997
LATCH.cache buffers lru chain 12,798 1 -12,797
LATCH.library cache 15,414 113 -15,301
LATCH.row cache enqueue latch 24,072 0 -24,072
LATCH.row cache objects 24,225 0 -24,225
STAT...consistent changes 40,014 19 -39,995
STAT...db block changes 40,021 26 -39,995
STAT...calls to get snapshot s 50,004 10,004 -40,000
STAT...session pga memory 65,536 0 -65,536
STAT...session uga memory 96,304 0 -96,304
STAT...db block gets 123,872 28 -123,844
STAT...session logical reads 143,877 10,036 -133,841
LATCH.cache buffers chains 469,095 20,379 -448,716
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
605,908 20,899 -585,009 2,899.22%
PL/SQL procedure successfully completed.
SQL> |
|
|
|
| Re: difference between dbms_lob.substr and SUBSTR [message #123791 is a reply to message #123773] |
Tue, 14 June 2005 17:24 |
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
|
Senior Member |
|
|
Thanks Art for the valuable information.
Additing to this I want to know if I can assign this to a variable which is type of VARCHAR2 like this.....
select dbms_lob.substr(f2_text, 14, 1)
into : var1 ***** this is defiened as VARCHAR2(100)
|
|
|
|
|
|
|
|
| Re: difference between dbms_lob.substr and SUBSTR [message #124127 is a reply to message #124045] |
Thu, 16 June 2005 09:14 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Barbara,
Very interesting. Even when I run the DBMS_LOB solution first, it outperforms the SUBSTR solution by a country mile:SQL> EXEC runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_clob CLOB;
3 l_text VARCHAR2(30);
4 BEGIN
5 l_clob := 'This is a test record';
6 FOR i IN 1..10000
7 LOOP
8 l_text := DBMS_LOB.SUBSTR(l_clob,14,1);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_clob CLOB;
3 l_text VARCHAR2(30);
4 BEGIN
5 l_clob := 'This is a test record';
6 FOR i IN 1..10000
7 LOOP
8 l_text := SUBSTR(l_clob,1,14);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC runstats_pkg.rs_stop(500);
Run1 ran in 144 hsecs
Run2 ran in 7472 hsecs
run 1 ran in 1.93% of the time
Name Run1 Run2 Diff
LATCH.row cache objects 67 591 524
LATCH.row cache enqueue latch 60 588 528
LATCH.simulator lru latch 4 636 632
LATCH.session allocation 6 906 900
STAT...dirty buffers inspected 0 1,126 1,126
STAT...free buffer inspected 0 1,126 1,126
LATCH.dml lock allocation 0 1,282 1,282
STAT...messages sent 0 1,313 1,313
LATCH.undo global data 6 1,349 1,343
LATCH.library cache pin alloca 253 1,616 1,363
LATCH.session idle bit 27 1,433 1,406
LATCH.sort extent pool 4 2,373 2,369
LATCH.active checkpoint queue 1 2,640 2,639
LATCH.shared pool 601 3,640 3,039
LATCH.redo allocation 11 3,231 3,220
LATCH.library cache pin 391 3,717 3,326
STAT...hot buffers moved to he 0 3,516 3,516
LATCH.redo writing 4 3,592 3,588
STAT...enqueue requests 7 3,752 3,745
STAT...enqueue releases 6 3,752 3,746
LATCH.library cache 838 5,838 5,000
LATCH.simulator hash latch 156 5,601 5,445
STAT...CPU used by this sessio 128 5,750 5,622
STAT...CPU used when call star 128 5,750 5,622
STAT...Elapsed Time 160 7,489 7,329
LATCH.enqueues 13 7,663 7,650
LATCH.SQL memory manager worka 0 8,599 8,599
LATCH.messages 8 8,939 8,931
LATCH.enqueue hash chains 10 9,598 9,588
STAT...consistent gets 10,172 20,015 9,843
STAT...free buffer requested 34 10,014 9,980
LATCH.cache buffers lru chain 1 15,945 15,944
STAT...calls to get snapshot s 10,038 50,634 40,596
STAT...db block changes 48 52,709 52,661
STAT...consistent changes 39 52,703 52,664
STAT...db block gets 54 157,549 157,495
STAT...session logical reads 10,226 177,564 167,338
LATCH.checkpoint queue latch 64 195,036 194,972
LATCH.cache buffers chains 21,035 560,295 539,260
STAT...session pga memory max 0 3,965,024 3,965,024
STAT...session uga memory 0 4,643,968 4,643,968
STAT...session uga memory max 0 4,643,968 4,643,968
STAT...session pga memory 0 4,653,056 4,653,056
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
23,597 846,217 822,620 2.79%
PL/SQL procedure successfully completed.
SQL> |
|
|
|
| Re: difference between dbms_lob.substr and SUBSTR [message #124384 is a reply to message #124127] |
Fri, 17 June 2005 17:27 |
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
|
Senior Member |
|
|
I tried this test couple of times using 15000 and then 50000 records. Performance is tremendous when using DBMS_LOB.substr as opposed to just substr on CLOB field.
I am doing a data migration on this where I have more than 3 million records. I will have to be sure that this works on my production and expect it should not slow down the process.
Thanks gurus.....
Ravi
[Updated on: Fri, 17 June 2005 17:28] Report message to a moderator |
|
|
|
| Re: difference between dbms_lob.substr and SUBSTR [message #124513 is a reply to message #124127] |
Sun, 19 June 2005 17:35 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Art,
It turns out that my test was a bit different. I was selecting a substring of a clob into a clob, instead of into a varchar2. However, it is still interesting to note that whichever is run second is faster. I am still baffled. I have included the results of a test run below.
scott@ORA92> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
scott@ORA92> EXEC runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@ORA92> DECLARE
2 l_clob CLOB;
3 BEGIN
4 l_clob := 'This is a test record';
5 FOR i IN 1..1000
6 LOOP
7 l_clob := DBMS_LOB.SUBSTR(l_clob,14,1);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
scott@ORA92> EXEC runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
scott@ORA92> DECLARE
2 l_clob CLOB;
3 BEGIN
4 l_clob := 'This is a test record';
5 FOR i IN 1..1000
6 LOOP
7 l_clob := SUBSTR(l_clob,1,14);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
scott@ORA92> EXEC runstats_pkg.rs_stop
Run1 ran in 44 hsecs
Run2 ran in 30 hsecs
run 1 ran in 146.67% of the time
Name Run1 Run2 Diff
LATCH.active checkpoint queue 0 1 1
LATCH.cache buffers lru chain 2,006 2,005 -1
LATCH.redo writing 0 1 1
LATCH.session timer 0 1 1
STAT...recursive cpu usage 3 2 -1
STAT...free buffer requested 1,004 1,005 1
STAT...cursor authentications 1 0 -1
LATCH.undo global data 5 6 1
LATCH.post/wait queue 2 0 -2
STAT...consistent gets - exami 3 5 2
STAT...consistent gets 1,003 1,005 2
STAT...active txn count during 3 5 2
STAT...calls to kcmgcs 3 5 2
STAT...cleanout - number of kt 3 5 2
LATCH.simulator hash latch 193 191 -2
LATCH.cache buffers chains 36,528 36,532 4
STAT...consistent changes 4,495 4,499 4
STAT...db block changes 4,985 4,990 5
STAT...db block gets 12,511 12,517 6
LATCH.library cache pin alloca 24 16 -8
STAT...CPU used by this sessio 29 21 -8
STAT...CPU used when call star 29 21 -8
STAT...session logical reads 13,514 13,522 8
LATCH.library cache pin 56 46 -10
STAT...bytes received via SQL* 1,025 1,014 -11
LATCH.shared pool 69 49 -20
LATCH.library cache 97 67 -30
LATCH.checkpoint queue latch 0 32 32
STAT...redo size 60,568 60,628 60
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 65,536 0 -65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
39,546 39,513 -33 100.08%
PL/SQL procedure successfully completed.
scott@ORA92> EXEC runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@ORA92> DECLARE
2 l_clob CLOB;
3 BEGIN
4 l_clob := 'This is a test record';
5 FOR i IN 1..1000
6 LOOP
7 l_clob := SUBSTR(l_clob,1,14);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
scott@ORA92> EXEC runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
scott@ORA92> DECLARE
2 l_clob CLOB;
3 BEGIN
4 l_clob := 'This is a test record';
5 FOR i IN 1..1000
6 LOOP
7 l_clob := DBMS_LOB.SUBSTR(l_clob,14,1);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
scott@ORA92> EXEC runstats_pkg.rs_stop
Run1 ran in 48 hsecs
Run2 ran in 36 hsecs
run 1 ran in 133.33% of the time
Name Run1 Run2 Diff
LATCH.messages 1 2 1
LATCH.simulator lru latch 64 63 -1
LATCH.channel operations paren 0 2 2
LATCH.post/wait queue 2 0 -2
LATCH.redo writing 0 2 2
LATCH.enqueue hash chains 0 4 4
LATCH.enqueues 0 4 4
LATCH.shared pool 46 50 4
LATCH.cache buffers lru chain 2,006 2,011 5
LATCH.library cache pin 44 50 6
STAT...bytes received via SQL* 1,016 1,023 7
LATCH.library cache 64 72 8
STAT...active txn count during 12 4 -8
STAT...calls to kcmgcs 12 4 -8
STAT...cleanout - number of kt 12 4 -8
STAT...consistent gets 1,012 1,004 -8
STAT...consistent gets - exami 12 4 -8
LATCH.undo global data 13 4 -9
LATCH.checkpoint queue latch 0 16 16
STAT...consistent changes 4,513 4,497 -16
STAT...db block changes 5,004 4,988 -16
STAT...db block gets 12,531 12,515 -16
STAT...redo size 60,628 60,604 -24
STAT...session logical reads 13,543 13,519 -24
LATCH.cache buffers chains 36,600 36,559 -41
LATCH.SQL memory manager worka 0 67 67
LATCH.simulator hash latch 322 193 -129
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 65,536 0 -65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
39,677 39,614 -63 100.16%
PL/SQL procedure successfully completed.
scott@ORA92>
|
|
|
|
Goto Forum:
Current Time: Sun Apr 27 07:29:53 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
