TimesTen 应用层数据库缓存学习：19. 理解AWT缓存组的三种模式

概述

本文很好的讲述了AWT三种缓存组的概念和区别，并给出了3种缓存组从建立到摧毁的完整过程。

AWT缓存组有3中类型：
1. AWT 缺省 (Manually load)
2. AWT Dynamic
3. AWT Dynamic Globle (Cache Grid)

各种AWT类型的区别

AWT 缺省 (Manually load)

• TimesTen中inserted/updated/deleted的数据传递到Oracle
• Oracle中新增的数据通过”LOAD CACHE GROUP”同步到TimesTen
• 如果一个表缓存到两个AWT 缺省Cache Group，缓存组之间并不相互知情，因此一个cache instance可以同时存在于两个缓存组中

语法：
create asynchronous writethrough cache group t1_awt_reg
from t1 (c1 number(22) not null primary key, c2 date, c3 varchar(40));

AWT Dynamic

• TimesTen中inserted/updated/deleted的数据传递到Oracle
• Oracle中新增的数据通过”LOAD CACHE GROUP”同步到TimesTen
• Oracle中新增的数据也可以通过SELECT, UPDATE 和 DELETE语句动态加载
• 如果一个表缓存到两个AWT 缺省Cache Group，缓存组之间并不相互知情，因此一个cache instance可以同时存在于两个缓存组中

语法：
create dynamic asynchronous writethrough cache group t2_awt_dyn
from t1 (c1 number(22) not null primary key, c2 date, c3 varchar(40));

AWT Dynamic Globle (Cache Grid)

• TimesTen中inserted/updated/deleted的数据传递到Oracle
• Oracle中新增的数据通过”LOAD CACHE GROUP”同步到TimesTen
• Oracle中新增的数据也可以通过SELECT, UPDATE 和 DELETE语句动态加载
• 如果一个表缓存到两个AWT Dynamic Globle Cache Group，由于缓存组之间相互保持沟通，因此一个cache instance只能存在于一个缓存组中

语法：
create dynamic asynchronous writethrough global cache group t3_awt_dyn_gbl
from t1 (c1 number(22) not null primary key, c2 date, c3 varchar(40));

实验部分

在Oracle中创建表

$ sqlplus tthr/oracle@ttorcl
create table t1 (c1 number(22) not null primary key, c2 date, c3 varchar(40));
create table t2 (c1 number(22) not null primary key, c2 date, c3 varchar(40));
create table t3 (c1 number(22) not null primary key, c2 date, c3 varchar(40));

创建DSN

[cachedb1]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb1
PermSize=32
TempSize=64
LogFileSize=32
LogBufMB=32
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=ttorcl

[cachedb2]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb2
PermSize=32
TempSize=64
LogFileSize=32
LogBufMB=32
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=ttorcl

创建用户

同时在cachedb1和cachedb2中执行：
create user tthr identified by timesten;
grant admin, create session, cache_manager, create any table to tthr;

创建cache group, cache grid并关联到grid

同时在cachedb1和cachedb2中执行：
ttisql−v1−e“setprompt‘cachedb1>‘”“dsn=cachedb1;uid=tthr;pwd=timesten;oraclepwd=oracle” ttisql -v1 -e “set prompt ‘cachedb2> ‘” “dsn=cachedb2;uid=tthr;pwd=timesten;oraclepwd=oracle”

call ttcacheuidpwdset(‘cacheadm’, ‘oracle’);
call ttcachestart;
call ttgriddestroy(‘samplegrid’,1); <- 此命令很好用
call ttgridnodestatus(‘samplegrid’);
call ttgridcreate(‘samplegrid’); <- 在任意一个TimesTen数据库中执行一次即可
call ttgridinfo(‘samplegrid’);
call ttgridnameset(‘samplegrid’);
call ttgridinfo(‘samplegrid’);
call ttgridnodestatus(‘samplegrid’);

三个表分布对应regular, dynamic, dynamic global缓存组
create asynchronous writethrough cache group t1_awt
from t1 (c1 number(22) not null primary key, c2 date, c3 varchar(40));
create dynamic asynchronous writethrough cache group t2_awt_dyn
from t2 (c1 number(22) not null primary key, c2 date, c3 varchar(40));
create dynamic asynchronous writethrough global cache group t3_awt_dyn_gbl
from t3 (c1 number(22) not null primary key, c2 date, c3 varchar(40));

cachedb1>
call ttgridattach(1,’member1’,’127.0.0.1’,5001);
call ttgridnodestatus(‘samplegrid’);
call ttrepstart;

cachedb2>
call ttgridattach(1,’member2’,’127.0.0.1’,5002); <- 使用不同的端口是因为两个TimesTen数据库在同一主机上
call ttgridnodestatus(‘samplegrid’);
call ttrepstart;

在两个数据库中，针对每一个缓存组插入数据

cachedb1>
insert into t1 values (1, sysdate, ‘t1 data’);
insert into t2 values (1, sysdate, ‘t2 data’);
insert into t3 values (1, sysdate, ‘t3 data’);
unload cache group t1_awt;
unload cache group t2_awt_dyn;
unload cache group t3_awt_dyn_gbl; <- unload后在TimesTen中看不到缓存数据

cachedb2>
insert into t1 values (2, sysdate, ‘t1 data’);
insert into t2 values (2, sysdate, ‘t2 data’);
insert into t3 values (2, sysdate, ‘t3 data’);
unload cache group t1_awt;
unload cache group t2_awt_dyn;
unload cache group t3_awt_dyn_gbl; <- unload后在TimesTen中看不到缓存数据

人工从Oracle中LOAD数据

cachedb1>
load cache group t1_awt where c1 = 1 commit every 10 rows parallel 10;
load cache group t2_awt_dyn where c1 = 1 commit every 10 rows parallel 10;
load cache group t3_awt_dyn_gbl where c1 = 1 commit every 10 rows parallel 10;
select * from t1;
select * from t2;
select * from t3;
unload cache group t1_awt;
unload cache group t2_awt_dyn;
unload cache group t3_awt_dyn_gbl;

cachedb2>
load cache group t1_awt where c1 = 2 commit every 10 rows parallel 10;
load cache group t2_awt_dyn where c1 = 2 commit every 10 rows parallel 10;
load cache group t3_awt_dyn_gbl where c1 = 2 commit every 10 rows parallel 10;
select * from t1;
select * from t2;
select * from t3;
unload cache group t1_awt;
unload cache group t2_awt_dyn;
unload cache group t3_awt_dyn_gbl;

通过SQL动态从Oracle中LOAD数据

cachedb1>
select * from t1 where c1 = 1;
select * from t2 where c1 = 1;
select * from t3 where c1 = 1;
select * from t1;
select * from t2;
select * from t3;

输出：
cachedb1> select * from t1; <- 没有输出，因为需要手工load
cachedb1> select * from t2; <- 有输出因为满足dynamic load条件
< 1, 2016-06-19 22:41:56, t2 data >
cachedb1> select * from t3; <- 有输出因为满足dynamic load条件
< 1, 2016-06-19 22:41:57, t3 data >

cachedb2>
select * from t1 where c1 = 2;
select * from t2 where c1 = 2;
select * from t3 where c1 = 2;
select * from t1;
select * from t2;
select * from t3;

输出：
cachedb2> select * from t1; <- 没有输出，因为需要手工load
cachedb2> select * from t2; <- 有输出因为满足dynamic load条件
< 2, 2016-06-19 22:45:12, t2 data >
cachedb2> select * from t3; <- 有输出因为满足dynamic load条件
< 2, 2016-06-19 22:46:07, t3 data >

通过SQL动态从Oracle或Cache grid中LOAD数据

cachedb1>
select * from t1 where c1 = 2;
select * from t2 where c1 = 2;
select * from t3 where c1 = 2;
select * from t1;
select * from t2;
select * from t3;

输出：
cachedb1> select * from t1;
cachedb1> select * from t2;
< 1, 2016-06-19 22:41:56, t2 data >
< 2, 2016-06-19 22:45:12, t2 data > <- 这条数据是从Oracle中dynamic load而来
cachedb1> select * from t3;
< 1, 2016-06-19 22:41:57, t3 data >
< 2, 2016-06-19 22:46:07, t3 data > <- 这条数据是从Cache Grid的另一个member: cachedb2中load而来

cachedb2>
select * from t1 where c1 = 1;
select * from t2 where c1 = 1;
select * from t3 where c1 = 1;
select * from t1;
select * from t2;
select * from t3;

输出：
cachedb2> select * from t1;
cachedb2> select * from t2;
< 1, 2016-06-19 22:41:56, t2 data > <- 对于普通的dynamic AWT，由于互不知情，因此这两条数据在两个TimesTen数据库中都存在
< 2, 2016-06-19 22:45:12, t2 data > <- 这条数据是从Oracle中dynamic load而来
cachedb2> select * from t3;
< 1, 2016-06-19 22:41:57, t3 data > <- 这条数据是从Cache Grid的另一个member: cachedb1中load而来
cachedb1> select * from t3;
< 2, 2016-06-19 22:46:07, t3 data > <- 对于global awt, cache instance只会在一个TimesTen中出现

删除缓存组并从Grid脱离关系

cachedb1>
call ttrepstop;
call ttgriddetach;
drop cache group t1_awt;
drop cache group t2_awt_dyn;
drop cache group t3_awt_dyn_gbl;
call ttcachestop;

cachedb2>
call ttrepstop;
call ttgriddetach;
drop cache group t1_awt;
drop cache group t2_awt_dyn;
drop cache group t3_awt_dyn_gbl;
call ttcachestop;
call ttgriddestroy(‘samplegrid’,1);

参考

HOWTO : Understand The Three Fundamental Types Of TimesTen Asynchronous (AWT) Cache Groups (Doc ID 1471954.1)