By Franck Pachot

.
In the previous blog post I measured how inserting 1 million rows takes 5 minutes instead of 45 seconds when we commit at each row inserted. In Oracle we can improve that down to 2 minutes if we can accept (and manage) the loss of transactions following an instance crash.
Let’s go further. For OLTP that need to face a high rate of transactions, Oracle has TimesTen which is a Database In-Memory – meaning that it is optimized for in-memory structures. My testcase has a 1 million rows table that can fit in memory, so let’s try it.

Test case

I am still using the Oracle Developer Day 11g VM which has TimesTen installed.
I’ve configured my database in /home/oracle/app/oracle/product/TimesTen/tt1122/info/sys.odbc.ini

[sampledb_1122]
Driver=/home/oracle/app/oracle/product/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/sampledb_1122
PermSize=400
TempSize=320
PLSQL=1
DatabaseCharacterSet=US7ASCII

I used the sample one but I have increased the size of memory to 400MB and 320MB for PERM_ALLOCATED_SIZE and TEMP_ALLOCATED_SIZE.

ttIsql

So I run the TimesTen command line interpreter:

[oracle@localhost ~]$ ttisql

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

check the version

Command> version
TimesTen Release 11.2.2.2.0

connect to my database

Command> connect "dsn=sampledb_1122";
Connection successful: DSN=sampledb_1122;UID=oracle;DataStore=/home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/app/oracle/product/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)

and I want to manage commits myself

Command> set autocommit 0;

and then I’m doing the same as in previous posts:

Command> set echo on;

Command> create table DEMO ("id" number primary key, "text" varchar2(15), "number" number);

Command> set timing on;

There is something that is very nice about TimesTen: it supports PL/SQL. I’m running exactly the same as I did in Oracle:

Command> declare
 type people_array is varray(12) of varchar(15);
 people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
 people_count number :=people.COUNT;
 n number;
begin
 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit;
 end loop;
 commit;
end;
/

PL/SQL procedure successfully completed.

Execution time (SQLExecute) = 21.010554 seconds.

21 second. Yes that’s fast…

Durable commits

Do you remember how we improved the commits in the last post? When accepting the risk to have non durable commits the response time was faster on Oracle database because we don’t have to wait for persistence of commits.

Here in TimesTen, it’s an In-Memory database and by default transactions are not durable. Transactions are logged for rollback, but not for recovery. Which means that nothing goes to disk. If the instance crashes, data is lost. If I want to have the durability of ACID properties then we have to write transaction logging to disk.
This is activated by the following property in the sys.odbc.ini:

DurableCommits=1;

I connect:

Command> connect "dsn=sampledb_1122";
Connection successful: DSN=sampledb_1122;UID=oracle;DataStore=/home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DurableCommits=1;DRIVER=/home/oracle/app/oracle/product/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)

and run exactly the same as before:

PL/SQL procedure successfully completed.

Execution time (SQLExecute) = 31.316902 seconds.

It’s longer but still faster than the Oracle database. TimesTen is optimized for that.

Conclusion

Note that we have the durability here, thanks to the persistence of transaction log, as long as we set DurableCommits=1. However, in case of instance crash, the recovery may be long because lot of transaction must be re-done. It’s an In-Memory database, it’s fast when it’s up but you don’t have the same availability than a database that is optimized to store data on disk. You have to tune the checkpoints in order to balance between the performance and the availability.

TimesTen In-Memory database can be a good solution to accelerate some use-cases. Its compatibility with Oracle SQL and PL/SQL is very good. You can install it standalone or as a cache for Oracle Database. Licencing is 50% of Oracle Enterprise Edition.

TimesTen is also called ‘Database In-Memory’. Which is a bit misleading because Oracle has also the 12c In-memory option which is very different. With competitors, TimesTen can be compared to SQL Server In-Memory OLTP. Because it’s a unique event – a cross technology one (MSSQL, ORA, HANA) – I don’t hesitate to link again to our free Event about In-Memory: boost your IT performance! In Switzerland (Lausanne, Basel and Zürich).

Talking about cross technology expertise, don’t miss Daniel’s variation on 1M rows insert into PostgreSQL and with synchronous commit .