TimesTen 应用层数据库缓存学习：4. 仅仅读缓存

最新推荐文章于 2023-09-07 14:14:47 发布

weixin_33697898

最新推荐文章于 2023-09-07 14:14:47 发布

阅读量221

点赞数

文章标签：数据库操作系统

在运行本文样例前。首先先运行TimesTen 应用层数据库缓存学习：2. 环境准备中的操作。

Read-only Cache Group的概念

仅仅读缓存组例如以下图：

仅仅读缓存组(Read-Only Cache Group)的cache table不能直接改动。在Oracle数据库中的数据更新后自己主动同步到Timesten。

仅仅读缓存的原理是对Oracle中须要缓存的表建立触发器来捕捉其变化

For an autorefresh cache group, TimesTen creates a change log table and trigger in the Oracle database for each cache table in the cache group. The trigger is fired for each committed insert, update, or delete operation on the cached Oracle Database table. The trigger records the primary key of the updated rows in the change log table. The cache agent periodically scans the change log table for updated keys and then joins this table with the cached Oracle Database table to get a snapshot of the latest updates.

cache table虽不能直接改动，但更新语句能够通过passthrough模式下发到Oracle运行。

假设TimesTen暂不可用。在Oracle中的更改能够暂存，待恢复正常后再同步到TimesTen。

如上图。仅仅读缓存组的操作通过cache agent实现，cache agent通过ttCacheUidPwdSet设置的username和口令连接到Oracle运行操作。
仅仅读缓存组不须要启动replication agent，仅仅有AWT缓存组才须要。

Read-only Cache Group的属性

从SQL Developer中，我们来看Read-only Cache Group的定义界面

Global表示是否为cache grid，read-only缓存组不支持，因此无法勾选。

选中Dynamic。表示载入方式为Dynamic Load，否则为Explicitly Load，以下的样例中会说明差别。
Autorefresh的属性中。incremental表示依据Oracle中记录的cached table的变化日志来同步，Full表示每次都unload全部数据后在全然同步。

Incremental autorefresh mode incurs some overhead to refresh the cache group for each committed update on the cached Oracle Database tables. There is no overhead when using full autorefresh mode.

上面这句的意思是。incremental autorefresh会在Oracle中记录日志，有开销。而full模式不会。可是。因为full模式是全同步。在refresh时的开销会非常大。而incremental模式在refresh时开销小。

假设每天夜间才须要同步一次。full模式也是合适的选择。

refresh interval是同步的周期。太密影响性能，太疏则数据实时性差。详细取决于业务需求。
手工load/refresh时，要求refresh state为paused。手工load/refresh和autorefresh完毕后，会将state改为on。

refresh state设置为off或paused时。autorefresh不会运行。

准备工作

在建立cache group之前，首先配置cache grid。

这里概念easy混淆，因为我们说cache group和cache grid是不同的。而这里的操作又是必需的。我们能够理解这里的配置工作为建立一个cache grid的框架，没有不论什么TimesTen节点attach到它上面。仅仅是便于以后扩展。

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> call ttGridCreate ('samplegrid');
Command> call ttGridInfo;
< SAMPLEGRID, CACHEADM, Linux Intel x86, 32-bit, 11, 2, 2 >
Command> call ttGridNameSet ('samplegrid');
Command> call ttGridNameGet;
< SAMPLEGRID >

然后启动cache agent。

$ ttisql -v1 cachedb1_1122
Command> call ttCacheStart;
或者
$ ttAdmin -cacheStart cachedb1_1122
然后能够通过ttStatus查看cache agent的状态：
$ ttstatus cachedb1_1122
TimesTen status report as of Sun Apr 10 06:24:24 2016

Daemon pid 2046 port 53392 instance tt1122
No TimesTen server running
------------------------------------------------------------------------
Data store /home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
There are 14 connections to the data store
Shared Memory KEY 0x3a41077e ID 8650765
PL/SQL Memory KEY 0x3b41077e ID 8683534 Address 0x10000000
Type            PID     Context     Connection Name              ConnID
Cache Agent     17221   0x09a41200  Handler                           2
Cache Agent     17221   0x09b70538  Timer                             3
Cache Agent     17221   0xa7a0c560  Aging                             4

Load和Refresh的差别

这时非常重要的概念，详述请參见Cache User Guide, Chapter 5, 5Cache Group Operations,Loading and refreshing a cache group

LOAD CACHE GROUP only loads committed inserts on the cached Oracle Database tables into the TimesTen cache tables. New cache instances are loaded into the cache tables, but cache instances that already exist in the cache tables are not updated or deleted even if the corresponding rows in the cached Oracle Database tables have been updated or deleted. A load operation is primarily used to initially populate a cache group.

REFRESH CACHE GROUP replaces cache instances in the TimesTen cache tables with the most current data from the cached Oracle Database tables including cache instances that are already exist in the cache tables. A refresh operation is primarily used to update the contents of a cache group with committed updates on the cached Oracle Database tables after the cache group has been initially populated.

If the cache table definitions use a WHERE clause, only rows that satisfy the WHERE clause are loaded or refreshed into the cache tables even if the LOAD CACHE GROUP or REFRESH CACHE GROUP statement does not use a WHERE clause.

To prevent a load or refresh operation from processing a large number of cache instances within a single transaction, which can greatly reduce concurrency and throughput, use the COMMIT EVERY n ROWS clause to specify a commit frequency unless you are using the WITH ID clause. If you specify COMMIT EVERY 0 ROWS, the load or refresh operation is processed in a single transaction.

Dynamic Read-Only Cache Group

$ cd $TT_HOME/quickstart/sample_scripts/cachegrid/

$ ls
create_global_awt.sql  create_local_awt.sql  create_local_dyn_ro.sql  create_local_ro.sql

$ cat create_local_dyn_ro_bak.sql 
create dynamic readonly cache group d_ro 
   autorefresh interval 1 second
from 
tthr.jobs ( job_id       varchar2(10) not null primary key,
          job_title    varchar2(35) not null,
          min_salary   number(6),
          max_salary   number(6));

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle" -f create_local_dyn_ro_bak.sql 
 5140: Could not find TTHR.JOBS in Oracle.  May not have privileges.

$ sqlplus tthr/oracle@ttorcl 

SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 06:45:32 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20000      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20000
SA_REP     Sales Representative                      6000      12000
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2000       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected.

SQL> grant select on jobs to cacheadm;

Grant succeeded.
...
$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle" -f create_local_dyn_ro_bak.sql
$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten"
Command> cachegroups;

Cache Group CACHEADM.D_RO:

  Cache Group Type: Read Only (Dynamic)
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 1 Second
  Autorefresh Status: ok
  Aging: LRU on

  Root Table: TTHR.JOBS
  Table Type: Read Only
# 在Oracle中，也能够看到缓存组的信息
$ cd $TT_HOME/oraclescripts
$ sqlplus cacheadm/oracle@ttorcl

SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 20:40:42 2016

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


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

SQL> @cacheInfo.sql
*************Autorefresh Objects Information  ***************
Host name: localhost.localdomain
Timesten datastore name:
/home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
Cache table name: TTHR.JOBS
Change log table name: tt_06_114329_L
Number of rows in change log table: 1
Maximum logseq on the change log table: 1
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
****************************
*************No DDL Tracking objects are found*************

PL/SQL procedure successfully completed.

基本行为

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten"
Command> desc jobs;

Table TTHR.JOBS:
  Columns:
   *JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    JOB_TITLE                       VARCHAR2 (35) INLINE NOT NULL
    MIN_SALARY                      NUMBER (6)
    MAX_SALARY                      NUMBER (6)
  Aging: LRU on
Command> select * from jobs;
Command> select * from jobs where job_id = 'FI_MGR';
 5213: Bad Oracle login error in OCISessionBegin(): ORA-01017: invalid username/password; logon denied rc = -1
 5131: Cannot connect to backend database: OracleNetServiceName = "TTORCL", uid = "TTHR", pwd is hidden, TNS_ADMIN = "/home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin", ORACLE_HOME= "/home/oracle/app/oracle/product/11.2.0/dbhome_2"
 5109: Cache Connect general error: BDB connection not open.
Command> exit
# 这点特别不能理解，明明之前用ttCacheUidPwdSet设置了username和口令，但在进行dynamic load时，仍然须要指定oraclepwd才干成功。
# 看一下我的KM
$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten;oraclepwd=oracle"
# 以下的语句满足dynamic load要求，Dynamic Load每次仅仅载入一行数据，并且仅当数据和主键/唯一索引全然匹配时
Command> select * from jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 8200, 16000 >

# 以下的语句不会引起dynamic load
Command> select * from jobs where MIN_SALARY = 3000;
Command> delete from jobs;
 8225: Table JOBS is read only 
#仅仅读缓存组不同意写

通过passthrough模式訪问Oracle数据库

因为仅仅读缓存组不同意改动。我们可通过passthrough模式对Oracle进行操作

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten;oraclepwd=oracle"
Command> select * from jobs;
< FI_MGR, Finance Manager, 8200, 16000 >
Command> passthrough 3
Command> select * from jobs;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< AD_ASST, Administration Assistant, 3000, 6000 >
< FI_MGR, Finance Manager, 8200, 16000 >
< FI_ACCOUNT, Accountant, 4200, 9000 >
< AC_MGR, Accounting Manager, 8200, 16000 >
< AC_ACCOUNT, Public Accountant, 4200, 9000 >
< SA_MAN, Sales Manager, 10000, 20000 >
< SA_REP, Sales Representative, 6000, 12000 >
< PU_MAN, Purchasing Manager, 8000, 15000 >
< PU_CLERK, Purchasing Clerk, 2500, 5500 >
< ST_MAN, Stock Manager, 5500, 8500 >
< ST_CLERK, Stock Clerk, 2000, 5000 >
< SH_CLERK, Shipping Clerk, 2500, 5500 >
< IT_PROG, Programmer, 4000, 10000 >
< MK_MAN, Marketing Manager, 9000, 15000 >
< MK_REP, Marketing Representative, 4000, 9000 >
< HR_REP, Human Resources Representative, 4000, 9000 >
< PR_REP, Public Relations Representative, 4500, 10500 >
Command> delete from jobs where job_id = 'FI_MGR';
 5224: Oracle foreign key violation error in OCIStmtExecute(): ORA-02292: integrity constraint (TTHR.EMP_JOB_FK) violated - child record found rc = -1
Command> update jobs set min_salary = 9000 where job_id = 'FI_MGR';
Command> commit;
Command> passthrough 0
Command> select * from jobs;
< FI_MGR, Finance Manager, 9000, 16000 >

在上例中，我们通过passthrough更改min_salary。然后更改同步到TimesTen

Dynamic Load 缓存组的Load和Refresh

以下的描写叙述仅针对Dynamic Load 缓存组

For a dynamic cache group, a refresh operation only refreshes committed updates and deletes on the cached Oracle Database tables into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables so after the refresh operation completes, the cache tables contain either the same or fewer number of cache instances. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See “Dynamically loading a cache instance” for more information about a dynamic load operation.

Load和Refreh的最大差别在于, Load针对的是TimesTen缓存表中没有的记录。对于已有的记录不会影响。而Refresh仅仅更新TimesTen缓存表中已有的记录。

Load通经常使用于数据初始化载入，而Refresh用于更新缓存表中的数据，仅仅有当refresh interval较长时有意义。否则autorefresh会自己主动更新。

以下的操作中，为了方便，我们在timesten中将tthr.jobs的SELECT权限赋予了cacheadm，在生产环境中，请不要这样做。

$ ttisql -v1 cachedb1_1122
Command> grant select on tthr.jobs to cacheadm

先来看Load操作

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> select * from tthr.jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 9000, 16000 >
Command> load cache group CACHEADM.D_RO where min_salary > 10000 commit every 256 rows;
Command> select * from tthr.jobs;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< FI_MGR, Finance Manager, 9000, 16000 >
# 这里尽管FI_MGR的最低工资并不是>10000。但仍然在结果集中，我们以下改动为20000
$ sqlplus tthr/oracle@ttorcl 

SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 22:25:27 2016

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


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

SQL> update jobs set min_salary = 20000 where job_id = 'FI_MGR';

1 row updated.

SQL> commit;

Commit complete.

# 回到ttisql会话
Command> load cache group CACHEADM.D_RO where min_salary > 10000 commit every 256 rows;
Command> select * from tthr.jobs;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< FI_MGR, Finance Manager, 20000, 16000 >
# 奇怪，这回FI_MGR的min_salary更新了，但事实是，这不是因为load动作引起的。而是由autorefresh引起的
# 为了看的更清楚些。我们须要将refresh interval设置更长一些或者是停止autorefresh以避免autorefresh的干扰。我们先将其设置为1小时。
Command> unload cache group CACHEADM.D_RO;
Command> select count(*) from tthr.jobs;
< 0 >
Command> alter cache group CACHEADM.D_RO set autorefresh interval 3600 seconds;
Command> exit

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten;oraclepwd=oracle"
Command> passthrough 3;
Command> update jobs set min_salary = 9000 where job_id = 'FI_MGR';
Command> commit;
Command> passthrough 0;
Command> select * from jobs;
Command> select * from jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 9000, 16000 >
Command> exit

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> load cache group CACHEADM.D_RO where min_salary > 10000 commit every 256 rows;
Command> select * from tthr.jobs;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< FI_MGR, Finance Manager, 9000, 16000 >

# 在Oracle中将FI_MGR的min_salary改为20000
$ sqlplus tthr/oracle@ttorcl 

SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 22:40:53 2016

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


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

SQL> update jobs set min_salary = 20000 where job_id = 'FI_MGR';

1 row updated.

SQL> commit;

Commit complete.

SQL> exit

# 回到ttisql会话，再次load，因为refresh interval为1小时，此时还未发生，这时看到的结果没有autorefresh的影响。
# FI_MGR的min_salary仍不变。说明load仅仅载入满足条件，但不在cache table中的数据
Command> load cache group CACHEADM.D_RO where min_salary > 10000 commit every 256 rows;
Command> select * from tthr.jobs ;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< FI_MGR, Finance Manager, 9000, 16000 >
Command> unload cache group CACHEADM.D_RO;

再看Refresh操作，相同，我们须要避免autorefresh的干扰，这里我们将禁止掉autorefresh。

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> unload cache group CACHEADM.D_RO;
Command> select * from tthr.jobs;
Command> select * from tthr.jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 20000, 16000 >
# 禁止autorefresh
Command> alter cache group CACHEADM.D_RO set autorefresh state paused;
# 不同意带where条件
Command> refresh cache group CACHEADM.D_RO where min_salary > 10000 commit every 256 rows;
 8304: Manual REFRESH operation with WHERE clause is not allowed on cache group CACHEADM.D_RO because it is specified as dynamic AUTOREFRESH

Command> refresh cache group CACHEADM.D_RO commit every 256 rows;

Command> select * from tthr.jobs;
< FI_MGR, Finance Manager, 20000, 16000 >

# 在Oracle中，将20000改为9000
$ sqlplus tthr/oracle@ttorcl 

SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 23:04:40 2016

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


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

SQL> update jobs set min_salary = 9000 where job_id = 'FI_MGR';

1 row updated.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

# 回到ttisql会话
Command> refresh cache group CACHEADM.D_RO commit every 256 rows;
 8289: Manual LOAD/REFRESH of cache group requires AUTOREFRESH STATE to be PAUSED, but the STATE of CACHEADM.D_RO is ON. The LOAD is not allowed
# 前面refresh运行后。autorefresh会自己主动同意，因此我们仍需将其禁止
Command> alter cache group CACHEADM.D_RO set autorefresh state paused;
Command> refresh cache group CACHEADM.D_RO commit every 256 rows;
Command> select * from tthr.jobs;
< FI_MGR, Finance Manager, 9000, 16000 >

**这里我们应该了解了refresh和load的差别，load相似于insert语句，仅仅载入timesten中没有的数据。而refresh相似于update。仅仅更新timesten中已有的数据。
autorefresh会触发refresh动作。**

Explicit Load Cache Group

$ cd $TT_HOME/quickstart/sample_scripts/cachegrid/

$ ls
create_global_awt.sql  create_local_awt.sql  create_local_dyn_ro.sql  create_local_ro.sql

$ cat create_local_dyn_ro_bak.sql 
$ cat create_local_ro.sql
create readonly cache group ro 
   autorefresh 
       interval 5 seconds
       mode incremental
from
    tthr.departments (
            department_id   number(4) not null primary key,
            department_name varchar2(30) not null,
            manager_id      number(6),
            location_id     number(4))
where department_id < 100;


# 将hr.改动为tthr。加上where条件。

并另存为create_local_ro_bak.sql

 $ sqlplus tthr/oracle@ttorcl SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 23:42:55 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from departments where department_id < 100; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 9 rows selected. SQL> grant select on departments to cacheadm; Grant succeeded. SQL> exit $ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle" -f create_local_ro_bak.sql $ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten" Command> cachegroups; Cache Group CACHEADM.RO: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: TTHR.DEPARTMENTS Table Type: Read Only # 在Oracle中，也能够看到缓存组的信息 $ cd $TT_HOME/oraclescripts $ sqlplus cacheadm/oracle@ttorcl SQL*Plus: Release 11.2.0.2.0 Production on Sun Apr 10 20:40:42 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @cacheInfo.sql *************Autorefresh Objects Information *************** Host name: localhost.localdomain Timesten datastore name: /home/oracle/app/oracle/product/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122 Cache table name: TTHR.DEPARTMENTS Change log table name: tt_06_114326_L Number of rows in change log table: 1 Maximum logseq on the change log table: 0 Timesten has autorefreshed updates upto logseq: -1 Number of updates waiting to be autorefreshed: 1 Number of updates that has not been marked with a valid logseq: 0 **************************** *************No DDL Tracking objects are found************* PL/SQL procedure successfully completed.

基本行为

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten"
Command> select * from departments;
Command> select * from departments where DEPARTMENT_ID = 10;
# 没有不论什么数据，须要手工load

缓存组的Load和Refresh

以下的操作中，为了方便。我们在timesten中将tthr.departments的SELECT权限赋予了cacheadm。在生产环境中，请不要这样做。

$ ttisql -v1 cachedb1_1122
Command> grant select on tthr.departments to cacheadm

先来看Load

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> load cache group cacheadm.ro commit every 256 rows;
Command> select count(*) from tthr.departments;
< 9 >
Command> unload cache group cacheadm.ro;
Command> select count(*) from tthr.departments;
< 0 >
Command> alter cache group CACHEADM.RO set autorefresh state paused;
# 对于非dynamic的仅仅读缓存组，load时不同意带where条件
Command> load cache group cacheadm.ro where department_id < 50 commit every 256 rows;
 8287: Manual LOAD/RFRESH operations with WHERE or WITHID clauses are not allowed on cache group CACHEADM.RO because it is specified as AUTOREFRESH
Command> load cache group cacheadm.ro commit every 256 rows;
 8289: Manual LOAD/REFRESH of cache group requires AUTOREFRESH STATE to be PAUSED, but the STATE of CACHEADM.RO is OFF. The LOAD is not allowed
Command> alter cache group CACHEADM.RO set autorefresh state paused;
Command> load cache group cacheadm.ro commit every 256 rows;
Command> select count(*) from tthr.departments;
< 9 >
# 在cache table非空时，不同意load
Command> load cache group cacheadm.ro commit every 256 rows;
 8288: Manual LOAD of cache group CACHEADM.RO is not allowed because it is specified as AUTOREFRESH and it is not empty

再看Refresh

# 在Oracle中以tthr用户插入新记录
SQL> insert into departments values(25, 'SC', NULL, 1700);
SQL> commit;

# 在timesten中，立即能够看到这条记录
Command> select * from tthr.departments;
< 10, Administration, 200, 1700 >
< 20, Marketing, 201, 1800 >
**< 25, SC, <NULL>, 1700 >**
< 30, Purchasing, 114, 1700 >
< 40, Human Resources, 203, 2400 >
< 50, Shipping, 121, 1500 >
< 60, IT, 103, 1400 >
< 70, Public Relations, 204, 2700 >
< 80, Sales, 145, 2500 >
< 90, Executive, 100, 1700 >
# 我们没有手工运行不论什么Load和Refresh操作。而这条数据却同步到了TimesTen。这个结果是因为5秒一次的autorefresh导致的。
# 由此可见。Explicitly与Dynamic Load的缓存组，refresh的行为是不一样的，前者的refresh等于unload all + load all。而后者相当于update。
# 我们能够先禁止掉autorefresh后。然后看refresh的行为
$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> select * from tthr.departments;
< 10, Administration, 200, 1700 >
< 20, Marketing, 201, 1800 >
< 25, SC, <NULL>, 1700 >
< 30, Purchasing, 114, 1700 >
< 40, Human Resources, 203, 2400 >
< 50, Shipping, 121, 1500 >
< 60, IT, 103, 1400 >
< 70, Public Relations, 204, 2700 >
< 80, Sales, 145, 2500 >
< 90, Executive, 100, 1700 >
Command> alter cache group CACHEADM.RO set autorefresh state paused;
# 在Oracle中以tthr用户删除刚插入的记录
SQL> delete from departments where department_id = 25;
SQL> commit;
# 回到ttisql会话，因为autorefresh周期非常长，此时数据还在
Command> select * from tthr.departments where department_id = 25;
< 25, SC, <NULL>, 1700 >
# 手工refresh，这时数据同步了
Command> refresh cache group cacheadm.ro commit every 256 rows;
Command> select * from tthr.departments where department_id = 25;

For an explicitly loaded cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement on the cache group. In effect, all committed inserts, updates and deletes on the cached Oracle Database tables are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle Database tables have been updated or deleted. See “Unloading a cache group” for more information about the UNLOAD CACHE GROUP statement.

能够看到，对于Explicitly load缓存组，数据初始化时的load操作是必须的。但也仅仅须要运行一次，手工refresh操作则相当于unload+load。开销非常大，而自己主动refresh操作会基于日志将Oracle中发生变化的记录同步到TimesTen。

大部分时候，refresh和load这些手工操作都是不必要的， TimesTen会自己主动的运行autorefresh，dynamic load等操作。

本文的样例仅仅为说明原理

仅仅读缓存组，缓存部分行和列

缓存部分列是在定义缓存组时实现的，比如：

CREATE READONLY CACHE GROUP "RO" 
 AUTOREFRESH MODE INCREMENTAL INTERVAL 5 MINUTES
 STATE PAUSED
 FROM
  "TTHR"."DEPARTMENTS" (
    "DEPARTMENT_ID" NUMBER(4) NOT NULL,
    PRIMARY KEY("DEPARTMENT_ID")
  )

除了主键列必须缓存外，其他都能够不缓存

缓存部分行在定义缓存组时实现，比如

CREATE DYNAMIC READONLY CACHE GROUP "RO" 
 AUTOREFRESH MODE INCREMENTAL INTERVAL 5 MINUTES
 STATE PAUSED
 FROM
  "TTHR"."DEPARTMENTS" (
    "DEPARTMENT_ID"   NUMBER(4)         NOT NULL,
    "DEPARTMENT_NAME" VARCHAR2(30 BYTE) NOT NULL,
    "MANAGER_ID"      NUMBER(6)        ,
    "LOCATION_ID"     NUMBER(4)        ,
    PRIMARY KEY("DEPARTMENT_ID")
  ) AGING LRU OFF 
  WHERE (departments.department_id = 1)

Command> cachegroups ro

Cache Group CACHEADM.RO:

  Cache Group Type: Read Only (Dynamic)
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Minutes
  Autorefresh Status: ok
  Aging: LRU off

  Root Table: TTHR.DEPARTMENTS
  Where Clause: (departments.department_id = 1)
  Table Type: Read Only

root table和child table都能够加where条件：

CREATE READONLY CACHE GROUP "RO" 
 AUTOREFRESH MODE INCREMENTAL INTERVAL 5 MINUTES
 STATE PAUSED
 FROM
  "TTHR"."REGIONS" (
    "REGION_ID"   NUMBER            NOT NULL,
    "REGION_NAME" VARCHAR2(25 BYTE),
    PRIMARY KEY("REGION_ID")
  )
  WHERE (regions.region_id < 5),
  "TTHR"."COUNTRIES" (
    "COUNTRY_ID"   CHAR(2 BYTE)      NOT NULL,
    "COUNTRY_NAME" VARCHAR2(40 BYTE),
    "REGION_ID"    NUMBER            NOT NULL,
    PRIMARY KEY("COUNTRY_ID"),
    FOREIGN KEY("REGION_ID")
      REFERENCES "TTHR"."REGIONS"("REGION_ID")
  )
  WHERE (tthr.countries.region_id < 5),
  "TTHR"."LOCATIONS" (
    "LOCATION_ID"    NUMBER(4)         NOT NULL,
    "STREET_ADDRESS" VARCHAR2(40 BYTE),
    "POSTAL_CODE"    VARCHAR2(12 BYTE),
    "CITY"           VARCHAR2(30 BYTE) NOT NULL,
    "STATE_PROVINCE" VARCHAR2(25 BYTE),
    "COUNTRY_ID"     CHAR(2 BYTE)      NOT NULL,
    PRIMARY KEY("LOCATION_ID"),
    FOREIGN KEY("COUNTRY_ID")
      REFERENCES "TTHR"."COUNTRIES"("COUNTRY_ID")
  )
  WHERE (locations.country_id > 0)

缓存部分行是在Load时实现的，比如

load cache group "CACHEADM"."RO"
 where department_id = 1
 commit every 256 rows

可是仅仅对dynamic read only 才有效

仅仅读缓存的限制

參考cache user guide, chapter 4.

The cache tables on TimesTen cannot be updated directly.

这不用解释了
Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

A FLUSH CACHE GROUP statement cannot be issued on the cache group.

flush是从TimesTen到Oracle的手工动作，当然也不同意了

A TRUNCATE TABLE statement issued on a cached Oracle Database table is not automatically refreshed to the TimesTen cache table.

不会自己主动的更新。但能够通过refresh，或unload+load更新
A LOAD CACHE GROUP statement can only be issued on the cache group if the cache tables are empty, unless the cache group is dynamic.

对于explicitly load缓存组，load时，cache table必须为空
The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic, in which case the autorefresh state must be PAUSED or ON.

The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic, in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.
对于explicitly load缓存组，load时不能带where条件, 结合上一个条件。这时load仅仅能用于数据初始化

The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP
statement on the cache group. The REFRESH CACHE GROUP statement cannot contain
a WHERE clause

All tables and columns referenced in WHERE clauses when creating, loading or
unloading the cache group must be fully qualified.

清理cache group

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> call ttGridDetach;
 3363: Node is not attached to a grid <-说明是cache group而非cache grid
Command> drop cache group CACHEADM.D_RO;
Command> drop cache group CACHEADM.RO;
Command> call ttcachestop;
Command> call ttGridDestroy('samplegrid');
Command> call ttGridNameGet;
< <NULL> >

參考

Cache User Guide, Chapter 2 - Getting Started，Performing operations on the read-only cache group
Cache User Guide, Chapter 4 - Defining Cache Groups，Creating a cache group
Cache User Guide, Chapter 5 - Cache Group Operations，Loading and refreshing a cache group

weixin_33697898

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
TimesTen 应用层数据库缓存学习：4. 仅仅读缓存

在运行本文样例前。首先先运行TimesTen 应用层数据库缓存学习：2. 环境准备中的操作。Read-only Cache Group的概念仅仅读缓存组例如以下图：仅仅读缓存组(Read-Only Cache Group)的cache table不能直接改动。在Oracle数据库中的数据更新后自己主动同步到Timesten。仅仅读缓存的原理是对Ora...
复制链接

扫一扫