Let's create a simple test. Oracle DB:
SQL> show user
USER is "ORATT"
SQL> create table awttab ( a number not null primary key, b varchar2(100) );
Table created.
SQL> select count(*) from awttab;
COUNT(*)
----------
0
SQL> grant select, insert, update, delete on awttab to cacheadmin;
Grant succeeded.
SQL>
TimesTen:[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=cacheadmin;PWD=oracle"
Copyright (c) 1996-2011, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=ds_test;UID=cacheadmin;PWD=oracle";
Connection successful: DSN=ds_test;UID=cacheadmin;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> call ttCacheUidPwdSet('cacheadmin','oracle');
Command> call ttCacheStart;
Command> CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP awtcache
> FROM oratt.awttab ( a NUMBER NOT NULL PRIMARY KEY,
> b VARCHAR2(100));
Command> call ttRepStart;
Command>
[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=oratt;PWD=oracle"
Copyright (c) 1996-2011, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=ds_test;UID=oratt;PWD=oracle";
Connection successful: DSN=ds_test;UID=oratt;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> select * from awttab;
0 rows found.
Command>
We created an AWT cache group on a table awttab. Insert a row into the table.
Command> insert into awttab values (1,'test'); 1 row inserted. Command> select * from awttab; < 1, test > 1 row found. Command>Transaction was committed automatically (autocommit on). Check the record inside Oracle DB.
SQL> select * from awttab;
A B
---------- ------------------
1 test
SQL>
Let's update this row 50 times by using the following code. Before that I turn on the 10046 event on cacheadmin Oracle DB user.
Command> begin
> for i in 1 ..50 loop
> update awttab set b = 'test'||i where a=1;
> commit;
> end loop;
> end;
> /
PL/SQL procedure successfully completed.
Command> select * from awttab;
< 1, test50 >
1 row found.
Command>
As you can see, I updated the same row 50 times and used the following order:
update1 -> commit update2 -> commit ... update50 -> commitBasically, this type of transactions (each DML statement ends by commit) is very often used in OLTP systems and can cause the performance problems (see log_file_sync wait event), so let's have a look on tracing files. We can see information about each statement there:
... PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103869999 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t' UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1" END OF STMT PARSE #6328852:c=0,e=307,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103869995 BINDS #6328852: Bind#0 oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0 kxsbbbfp=4b176ea0 bln=32 avl=06 flg=09 value="test1" Bind#1 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0 kxsbbbfp=4b176e80 bln=22 avl=02 flg=09 value=1 EXEC #6328852:c=1000,e=494,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103870584 STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE AWTTAB (cr=1 pr=0 pw=0 time=265 us)' STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=71 us cost=1 size=65 card=1)' CLOSE #6328852:c=0,e=6,dep=1,type=0,tim=1355414103870744 =================== PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103870949 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t' UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1" END OF STMT PARSE #6328852:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103870944 BINDS #6328852: Bind#0 oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0 kxsbbbfp=48b7fb28 bln=32 avl=06 flg=09 value="test2" Bind#1 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0 kxsbbbfp=48b7fb10 bln=22 avl=02 flg=09 value=1 EXEC #6328852:c=1000,e=240,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103871275 STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE AWTTAB (cr=1 pr=0 pw=0 time=50 us)' STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=11 us cost=1 size=65 card=1)' CLOSE #6328852:c=0,e=6,dep=1,type=0,tim=1355414103871426 ...But there is no information about commit. And only after the last statement we see
PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103907559 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t' UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1" END OF STMT PARSE #6328852:c=0,e=100,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103907555 BINDS #6328852: Bind#0 oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0 kxsbbbfp=48b5f340 bln=32 avl=07 flg=09 value="test100" Bind#1 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0 kxsbbbfp=48b5f330 bln=22 avl=02 flg=09 value=1 EXEC #6328852:c=0,e=181,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103907813 STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE AWTTAB (cr=1 pr=0 pw=0 time=38 us)' STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=7 us cost=1 size=65 card=1)' CLOSE #6328852:c=0,e=5,dep=1,type=0,tim=13554141039080 ... XCTEND rlbk=0, rd_only=0, tim=1355414103908136As you can see, TimesTen tries to combine transactions in transactional order and applies them in efficient way. For example instead of execute transactions in the original order like this:
update1 -> commit update2 -> commit ... update50 -> commitTimesTen executed them like the following:
update1 -> update2 ... -> update50 -> commitThis method provides an opportunity to decrease load on Oracle Database. So, TimesTen provides not only the opportunity to achieve SLA and very low response time, additionally you can decrease the load on your production Oracle DB.
