Venzi's Tech-Blog

11 January 2008

11g and the new SecureFILE LOB data type (simple quick test)

Filed under: Code,Oracle,Performance,Work — Venzi @ 18:21

As I had to run some comparison test Oracle 10g and 11g I also did a little test with the new and fast (as Oracle promises) SecureFILE LOB data type. The test is really little as there wasn’t any time to test the feature totally out, but also this result is impressive. Of course all test occurred on the same box and the same settings:

First the 10g test:

The first step is to create a table with a CLOB data type:

SQL> create table test1 (created_tms date, data clob) tablespace data1 lob (data) store as (cache);
Table created.
SQL>

I used cache to get as much bytes as possible into the buffer cache. If you need performance on LOB anyone would activate this as first step. As next step I wrote a little PL/SQL program  which inserts 100.000 rows with a 4402 bytes (that’s 4.4GB all together!):

SQL> set serveroutput on;
SQL> declare
2    thedata CLOB := TO_CLOB (
3  ‘somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata
4  somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata
……
49  somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata
50  somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata ‘);
51  begin
52    dbms_output.put_line(‘Begin: ‘ || systimestamp);
53    for n in 1..100000 loop
54      insert into test1 values (sysdate,thedata);
55    end loop;
56    dbms_output.put_line(‘End: ‘ || systimestamp);
57  end;
58  /
Begin: 11-JAN-08 04.30.50.203325000 PM +01:00
End: 11-JAN-08 04.32.50.422759000 PM +01:00

PL/SQL procedure successfully completed.

Checking the time stamp you can see that with 10g the inserts took nearly exactly 2 minutes (the last time, I executed it several times). Also you can see that I inserted the actual sysdate in a column too. So now we can compute a average of inserts per second:

SQL> select count(*)/((max(created_tms)-min(created_tms))*24*60*60) from test1;

COUNT(*)/((MAX(CREATED_TMS)-MIN(CREATED_TMS))*24*60*60)
——————————————————-
833.333333

The last 4 runs made (average inserts/second):

840.336134
826.446281
800
833.333333

So, now we going to 11g. First step: We need a table with the new SecureFILE LOB type:

SQL>
SQL> create table test1 (created_tms date, data clob) tablespace data1 lob (data) store as securefile cache;

Table created.

Next, I executed exactly the same procedure:

SQL> set serveroutput on;
SQL> declare
2    thedata CLOB := TO_CLOB (
3  ‘somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata
……
49  somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata
50  somedata somedata somedata somedata somedata somedata somedata somedata somedata somedata ‘);
51  begin
52    dbms_output.put_line(‘Begin: ‘ || systimestamp);
53    for n in 1..100000 loop
54      insert into test1 values (sysdate,thedata);
55    end loop;
56    dbms_output.put_line(‘End: ‘ || systimestamp);
57  end;
58  /
Begin: 11-JAN-08 04.19.06.534822000 PM +01:00
End: 11-JAN-08 04.20.01.703784000 PM +01:00

PL/SQL procedure successfully completed.

Looking at the time stamps the run took less than a minute! Remember, 10g needed around 2 minutes. So lets look to the average inserts per second:

SQL> select count(*)/((max(created_tms)-min(created_tms))*24*60*60) from test1;

COUNT(*)/((MAX(CREATED_TMS)-MIN(CREATED_TMS))*24*60*60)
——————————————————-
1818.18182

We can see that the program made average 1818 inserts per second. Compared to 833 from 10g it’s more than 100% faster! And that’s not bad without any fine tuning I would say!

The last 4 runs made:

1960.78431
1818.18182
1754.38596
1818.18182

I hope I’ve some time left to provide a good test case, but also this quick one showed that the SecureFILE  data type really speeded-up compared to convention LOB, and that’s what Oracle promised!😉

5 Comments »

  1. Hi,

    A simple example that clearly shows the potential for this new feature. Thanks for taking the time to publish this.

    Mark

    Comment by Mark — 6 January 2012 @ 04:10 | Reply

  2. Deduplication (detect duplicate LOB data and only store one copy).
    This feature may help securefile to make it fast. In real time, CLOB data will be a duplicate. Better consider this in the next test🙂

    Comment by srini — 26 May 2012 @ 21:01 | Reply

    • Srini,

      Yes, you are right! Deduplication might makes it faster and the same with compression. However, this was just a very simple quick test and not a full benchmark (as stated in the heading) so I didn’t not put all the different features into scope.

      Thx,

      Comment by Venzi — 28 May 2012 @ 10:02 | Reply

  3. **In real time, clob is not a duplicate.**

    Comment by srini — 26 May 2012 @ 21:17 | Reply

    • But it can be in case where we have to save dba patterns of thousands or lacs people.

      Comment by pushpjeet — 15 June 2012 @ 10:10 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: