TimesTen 11.2 配置cache

TimesTen为内存数据库，05年被oracle收购。内存数据库具有极快的响应速度、极高的处理能力。同时可以将oracle的数据缓存到TimesTen，然后业务访问TimesTen，绕过存储，直接再内存中来处理事务，然后再将数据同步到oracle中，即
Cache Connect to Oracle。Cache Connect to Oracle方式有如下四种：
1.read only
数据从oracle 自动刷新到 TT里面，TT里面无法做修改。
典型应用是在TT里面保存只读的产品信息，用户信息等等，定期从oracle刷新该信息
2.异步写
数据在TT里面更新，异步传递到oracle 。
这种比较适合在TT上面做事务性操作，然后同步到oracle
3.同步写
数据在TT里面更新，同步传递到oracle 。
这种比较适合在TT上面做事务性操作，然后同步到oracle
4.人工管理的cache
在这种模式下，数据可以同时在TT和oracle里面更新，自动同步到对方。 这种方式最灵活，但是需要考虑数据冲突的问题，比如同时在oracle和TT上面修改统一条记录，怎么处理。

关于安装可见：http://www.orasql.com/blog/archives/2014/01/03/tt_install.htm
下面是简单的配置Cache Connect to Oracle和测试


测试中100w的表，同样的查询SQL，Oracle用时12.45s，TT用时0.018471s。
Oracle中第二次执行，直接从buffer cache读数据用时0.73s。
[code][oracle@localhost oraclescripts]$ ls
cacheCleanUp.sql grantCacheAdminPrivileges.sql initCacheGlobalSchema.sql README.TXT
cacheInfo.sql initCacheAdminSchema.sql initCacheGridSchema.sql
[oracle@localhost oraclescripts]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 21 14:41:42 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @initCacheGlobalSchema.sql

Please enter the tablespace where TIMESTEN user is to be created
USERS
The value chosen for tablespace is USERS

******* Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts *******
1. Creating TIMESTEN schema
2. Creating TIMESTEN.TT_GRIDID table
3. Creating TIMESTEN.TT_GRIDINFO table
4. Creating TT_CACHE_ADMIN_ROLE role
5. Granting privileges to TT_CACHE_ADMIN_ROLE
** Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully **

PL/SQL procedure successfully completed.

SQL> create user tt_cache_admin identified by oracle;

User created.

SQL> !ls
cacheCleanUp.sql grantCacheAdminPrivileges.sql initCacheGlobalSchema.sql README.TXT
cacheInfo.sql initCacheAdminSchema.sql initCacheGridSchema.sql

SQL> @grantCacheAdminPrivileges.sql "tt_cache_admin"

Please enter the administrator user id
The value chosen for administrator user id is tt_cache_admin

***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to TT_CACHE_ADMIN
1. Granting the TT_CACHE_ADMIN_ROLE to TT_CACHE_ADMIN
2. Granting the DBMS_LOCK package privilege to TT_CACHE_ADMIN
3. Granting the RESOURCE privilege to TT_CACHE_ADMIN
4. Granting the CREATE PROCEDURE privilege to TT_CACHE_ADMIN
5. Granting the CREATE ANY TRIGGER privilege to TT_CACHE_ADMIN
6. Granting the DBMS_LOB package privilege to TT_CACHE_ADMIN
7. Granting the SELECT on SYS.ALL_OBJECTS privilege to TT_CACHE_ADMIN
8. Granting the SELECT on SYS.ALL_SYNONYMS privilege to TT_CACHE_ADMIN
9. Checking if the cache administrator user has permissions on the default
tablespace
No existing permission.
10. Altering the cache administrator to grant unlimited tablespace on SYSTEM
11. Granting the CREATE TYPE privilege to TT_CACHE_ADMIN
12. Granting the SELECT on SYS.GV$LOCK privilege to TT_CACHE_ADMIN (optional)
13. Granting the SELECT on SYS.GV$SESSION privilege to TT_CACHE_ADMIN
(optional)
14. Granting the SELECT on SYS.DBA_DATA_FILES privilege to TT_CACHE_ADMIN
(optional)
15. Granting the SELECT on SYS.USER_USERS privilege to TT_CACHE_ADMIN
(optional)
16. Granting the SELECT on SYS.USER_FREE_SPACE privilege to TT_CACHE_ADMIN
(optional)
17. Granting the SELECT on SYS.USER_TS_QUOTAS privilege to TT_CACHE_ADMIN
(optional)
18. Granting the SELECT on SYS.USER_SYS_PRIVS privilege to TT_CACHE_ADMIN
(optional)
********* Initialization for cache admin user done successfully *********
SQL> create user tt_test identified by oracle;
SQL> grant resource,connect to tt_test;
SQL> select VALUE from nls_database_parameters where upper(parameter)='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
ZHS16GBK[/code]
修改sys.odbc.ini（字符集要一样）
[code][TT_1122]
Driver=/u01/TimesTen/tt11/lib/libtten.so
DataStore=/u01/TimesTen/tt11/info/TT_1122
DatabaseCharacterSet=ZHS16GBK
PermSize=128

[oracle@localhost info]$ ttisql tt_1122

Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=tt_1122";
Connection successful: DSN=TT_1122;UID=oracle;DataStore=/u01/TimesTen/tt11/info/TT_1122;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/TimesTen/tt11/lib/libtten.so;PermSize=128;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user tt_cache_admin identified by oracle;

User created.

Command> grant create session,cache_manager, create any table to tt_cache_admin;
1001: Syntax error in SQL statement before or at: "?", character position: 7
grant ?create?session,cache_manager,?create?any?table?to?tt_cache_admi...
^
The command failed.
Command> grant create session,cache_manager, create any table to tt_cache_admin;
1001: Syntax error in SQL statement before or at: "?", character position: 7
grant ?create?session,cache_manager,?create?any?table?to?tt_cache_admi...
^
The command failed.
Command> grant create session,cache_manager, create any table to tt_cache_admin;
1001: Syntax error in SQL statement before or at: "?", character position: 7
grant ?create?session,cache_manager,?create?any?table?to?tt_cache_admi...
^
The command failed.
Command> grant create session,cache_manager,create any table to tt_cache_admin;
Command> create user tt_test identified by oracle;

User created.

Command> grant create session,create any table to tt_test;
Command> call ttgridcreate ('tt_grid');
Command> call ttgridinfo;
< TT_GRID, TT_CACHE_ADMIN, Linux Intel x86, 32-bit, 11, 2, 2 >
1 row found.

Command> call ttGridNameSet('tt_grid');

[oracle@localhost linux86]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 21 16:44:55 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table tt_test.tt_obj(objid number not null primary key,objname VARCHAR2(30));

Table created.

SQL> insert into tt_test.tt_obj select OBJECT_ID,OBJECT_NAME from all_objects;

12780 rows created.

SQL> grant select on tt_test.tt_obj to tt_cache_admin;

Grant succeeded.

Command> call ttcachestart;
Command> create readonly cache group tt_grid autorefresh interval 5 seconds mode incremental from tt_test.tt_obj(objid number not null primary key,objname VARCHAR2(30));
Command> load cache group tt_grid commit every 1000 rows;
12780 cache instances affected.
Command>


[oracle@localhost ~]$ ttIsql "dsn=tt_1122;uid=tt_test;pwd=oracle;oraclepwd=oracle"

Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "dsn=tt_1122;uid=tt_test;pwd=oracle;oraclepwd=oracle";
Connection successful: DSN=TT_1122;UID=tt_test;DataStore=/u01/TimesTen/tt11/info/TT_1122;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/TimesTen/tt11/lib/libtten.so;PermSize=128;TypeMode=0;OracleNetServiceName=ORASQL;
(Default setting AutoCommit=1)
Command> tables
TT_TEST.TT_OBJ
1 table found.
Command> select count(*) from TT_OBJ;
< 0 >
1 row found.
Command> select count(*) from TT_OBJ;
< 12780 >
1 row found.
Command>


SQL> select avg(objid) from tt_test.tt_obj;

AVG(OBJID)
----------
6589.89006

SQL> delete from tt_test.tt_obj where objid<7000;

6832 rows deleted.

SQL> commit;

Commit complete.

SQL>

Command> set timing on
Command> select count(*) from TT_OBJ;
< 5948 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.000105 seconds.

Command>

Command> grant drop any table to TT_CACHE_ADMIN;
Command> exit
Disconnecting...
Done.
[oracle@localhost ~]$ ttIsql "dsn=tt_1122;uid=tt_cache_admin;pwd=oracle;oraclepwd=oracle"

Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "dsn=tt_1122;uid=tt_cache_admin;pwd=oracle;oraclepwd=oracle";
Connection successful: DSN=TT_1122;UID=tt_cache_admin;DataStore=/u01/TimesTen/tt11/info/TT_1122;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/TimesTen/tt11/lib/libtten.so;PermSize=128;TypeMode=0;OracleNetServiceName=ORASQL;
(Default setting AutoCommit=1)
Command>drop cache group tt_grid ;



SQL> alter database datafile '/u01/oradata/undo1.dbf' resize 400m;

Database altered.

Elapsed: 00:00:25.30


begin

for i in 1 .. 10000000
loop
insert into tt_test.tt_obj values(i,'awen'||i);
commit;
end loop;
end;
/
SQL> select count(*) from tt_obj;

COUNT(*)
----------
1000000

Elapsed: 00:00:12.45

Command> select count(*) from tt_obj;
< 1000000 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.018471 seconds.[/code]