declare a number := 1;
begin
select max(a) into :a from lru_tab;
a := a+1;
insert into lru_tab values(a, sysdate);
end;
/
Aging基本概念
Aging就是缓存的老化策略，毕竟缓存不能容纳所有的数据库数据，因此根据Aging设置，一些数据在某些时刻会退出缓存
TimesTen中, Aging既可以用于普通的表，也可以用于cache group。对于cache group，Aging在root table定义，影响整个cache instance。
为了简便，以下的例子都使用普通表来做示例。
TimesTen Aging支持基于LRU(least recently used）基于时间两种方式.
Aging 常用命令
time based aging:
create table tbaging (a int, ts timestamp not null)
AGING USE ts LIFETIME 1 MINUTES CYCLE 1 MINUTES ON;LRU aging:
create table lru_tab(a int primary key not null, b date not null) aging lru on;LRU aging策略设置
call ttAgingLRUConfig;
call ttAgingLRUConfig(.2, .3, 1);强制启动Aging（无论定义了Aging的表设置Aging为on还是off）
call ttAgingScheduleNow(); <-所有定义了aging的表
call ttAgingScheduleNow('tablename')Aging的开通，开启，关闭
alter table tablename add aging lru on
alter table tablename drop aging
alter table tablename set aging on|off
alter table tablename
add aging use columnname lifetime 3600 seconds
cycle 5 seconds
ON
alter table tablename
set aging lifetime 2 days;
alter table tablename
set aging cycle 5 secondsAging监控
tttracemon DSN
Trace> level aging 3
Trace> flush
Trace> dump
...
Trace> level aging 0
Trace> quitLRU Aging
LRU Aging删除最老的记录，以保持内存维持在一个水平。
可以用ttAgingLRUConfig([LowUsageThreshHold], [LowUsageThreshHold], [AgingCycle])设置LRU Aging属性。
其中低于LowUsageThreshHold就关闭Aging，高于LowUsageThreshHold就开始Aging，AgingCycle为检查频率，整数，至少为1分钟。
LRU aging can be defined for all cache group types except explicitly loaded autorefresh cache groups. LRU aging is defined by default on dynamic cache groups.
Command> create table lru_tab(a int primary key not null, b date not null) aging lru on;
Command> desc lru_tab;
Table ORACLE.LRU_TAB:
Columns:
*A NUMBER (38) NOT NULL
B DATE NOT NULL
Aging: LRU on
1 table found.
(primary key columns are indicated with *)
Command> dssize;
PERM_ALLOCATED_SIZE: 40960
PERM_IN_USE_SIZE: 9545
PERM_IN_USE_HIGH_WATER: 31652
TEMP_ALLOCATED_SIZE: 131072
TEMP_IN_USE_SIZE: 94674
TEMP_IN_USE_HIGH_WATER: 95197
Command> call ttAgingLRUConfig;
< .8000000, .9000000, 1 > <- 缺省值
1 row found.
Command> select 9545/40960 from dual;
< .2330322265625 >
1 row found.
Command> call ttAgingLRUConfig(.2, .3, 1);
< .2000000, .3000000, 1 >
1 row found.
Command> insert into lru_tab values(1, sysdate);
1 row inserted.
Command> select * from lru_tab;
< 1, 2016-04-13 22:31:38 >
1 row found.
declare a number := 1;
begin
select max(a) into a from lru_tab;
for i in 1..100000 loop
a := a+1;
insert into lru_tab values(a, sysdate);
end loop;
end;
/
PL/SQL procedure successfully completed.
Command> select count(*) from lru_tab;
< 100001 >
1 row found.
Command> dssize;
PERM_ALLOCATED_SIZE: 40960
PERM_IN_USE_SIZE: 15032
PERM_IN_USE_HIGH_WATER: 31652
TEMP_ALLOCATED_SIZE: 131072
TEMP_IN_USE_SIZE: 94611
TEMP_IN_USE_HIGH_WATER: 95197
declare a number := 1;
begin
select max(a) into a from lru_tab;
for i in 1..100000 loop
a := a+1;
insert into lru_tab values(a, sysdate);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
Command> dssize;
PERM_ALLOCATED_SIZE: 40960
PERM_IN_USE_SIZE: 20582
PERM_IN_USE_HIGH_WATER: 31652
TEMP_ALLOCATED_SIZE: 131072
TEMP_IN_USE_SIZE: 94548
TEMP_IN_USE_HIGH_WATER: 95197
Command> select count(*) from lru_tab; < 200001 >
1 row found.
Command> declare a number := 1; begin select max(a) into a from lru_tab; for i in 1..100000 loop a := a+1; insert into lru_tab values(a, sysdate); commit; end loop; end; /
PL/SQL procedure successfully completed.
Command> select count(*) from lru_tab; < 300001 >
1 row found.
Command> dssize;
PERM_ALLOCATED_SIZE: 40960
PERM_IN_USE_SIZE: 26054
PERM_IN_USE_HIGH_WATER: 31652
TEMP_ALLOCATED_SIZE: 131072
TEMP_IN_USE_SIZE: 94548
TEMP_IN_USE_HIGH_WATER: 95197
$ tttracemon cachedb1_1122
Trace> level aging 3
Trace> flush
Trace> dump
0 records dumped
Trace> dump
23:28:28.939 31 AGING 1L 135C 2613P Entering sbAgingLRU(): curTime=1085, minAccessCounter=1039
23:28:28.939 32 AGING 1L 135C 2613P Exiting sbAgingLRU(): curTime=1085, minAccessCounter=1039, permRatio=0.236157, highMemThreshold=0.300000
23:29:28.979 33 AGING 1L 135C 2613P Entering sbAgingLRU(): curTime=1086, minAccessCounter=1039
23:29:28.979 34 AGING 2L 135C 2613P Entering sbAgingOneTable(): curTime=1086, ltblid= 1798064
23:29:28.984 35 AGING 2L 135C 2613P Exiting sbAgingOneTable(): curTime=1086, reason = 'no more qualified rows found', 0 deleted, 104800 remaining, tbl = ORACLE.LRU_TAB
23:29:28.994 36 AGING 1L 135C 2613P sbAgingLRU()(again): curTime=1086, minAccessCounter=1048
23:29:28.994 37 AGING 2L 135C 2613P sbAgingLRU(): skip table 1798064, table's min access=1085, curTime=1086, minAccessCounter=1048
23:29:29.004 38 AGING 1L 135C 2613P sbAgingLRU()(again): curTime=1086, minAccessCounter=1054
23:29:29.004 39 AGING 2L 135C 2613P sbAgingLRU(): skip table 1798064, table's min access=1085, curTime=1086, minAccessCounter=1054
23:29:29.014 40 AGING 1L 135C 2613P sbAgingLRU()(again): curTime=1086, minAccessCounter=1062
23:29:29.014 41 AGING 2L 135C 2613P sbAgingLRU(): skip table 1798064, table's min access=1085, curTime=1086, minAccessCounter=1062
23:29:29.025 42 AGING 1L 135C 2613P sbAgingLRU()(again): curTime=1086, minAccessCounter=1074
23:29:29.025 43 AGING 2L 135C 2613P sbAgingLRU(): skip table 1798064, table's min access=1085, curTime=1086, minAccessCounter=1074
23:29:29.025 44 AGING 1L 135C 2613P Exiting sbAgingLRU(): curTime=1086, minAccessCounter=1074
14 records dumped <- 最开始，说没有满足条件的数据可删，即使空间已经超过限制了
Trace> dump
23:37:40.751 4071 AGING 3L 135C 2613P curTime=1094, 0 deleted, 862 remaining, tbl = ORACLE.LRU_TAB
23:37:40.755 4072 AGING 3L 135C 2613P curTime=1094, 0 deleted, 762 remaining, tbl = ORACLE.LRU_TAB
23:37:40.762 4073 AGING 3L 135C 2613P curTime=1094, 0 deleted, 662 remaining, tbl = ORACLE.LRU_TAB
23:37:40.763 4074 AGING 3L 135C 2613P curTime=1094, 0 deleted, 562 remaining, tbl = ORACLE.LRU_TAB
23:37:40.769 4075 AGING 3L 135C 2613P curTime=1094, 0 deleted, 462 remaining, tbl = ORACLE.LRU_TAB
23:37:40.771 4076 AGING 3L 135C 2613P curTime=1094, 0 deleted, 362 remaining, tbl = ORACLE.LRU_TAB
23:37:40.772 4077 AGING 3L 135C 2613P curTime=1094, 0 deleted, 262 remaining, tbl = ORACLE.LRU_TAB
23:37:40.774 4078 AGING 3L 135C 2613P curTime=1094, 0 deleted, 162 remaining, tbl = ORACLE.LRU_TAB
23:37:40.776 4079 AGING 3L 135C 2613P curTime=1094, 0 deleted, 62 remaining, tbl = ORACLE.LRU_TAB
23:37:40.778 4080 AGING 3L 135C 2613P curTime=1094, 0 deleted, 0 remaining, tbl = ORACLE.LRU_TAB
23:37:40.778 4081 AGING 2L 135C 2613P Exiting sbAgingOneTable(): curTime=1094, reason = 'no more rows', 0 deleted, 0 remaining, tbl = ORACLE.LRU_TAB
23:37:40.778 4082 AGING 1L 135C 2613P Exiting sbAgingLRU(): curTime=1094, minAccessCounter=1089
509 records dumped <- 过一会一看，数据都删完了，就是Aging引起的
Trace> level aging 0
Command> dssize;
PERM_ALLOCATED_SIZE: 40960
PERM_IN_USE_SIZE: 9673
PERM_IN_USE_HIGH_WATER: 39766
TEMP_ALLOCATED_SIZE: 131072
TEMP_IN_USE_SIZE: 93092
TEMP_IN_USE_HIGH_WATER: 95197
Time-based aging
基于时间的Aging基于表中一个日期列，与当前时间比较。超过范围则删除。
所有的缓存组都可以定义基于时间的Aging
Command> create table tbaging (a int, ts timestamp)
> AGING USE ts LIFETIME 1 MINUTES CYCLE 1 MINUTES ON;
2993: Aging column cannot be nullable
The command failed.
Command> create table tbaging (a int, ts timestamp not null)
> AGING USE ts LIFETIME 1 MINUTES CYCLE 1 MINUTES ON;
Command> desc tbaging;
Table ORACLE.TBAGING:
Columns:
A NUMBER (38)
TS TIMESTAMP (6) NOT NULL
Aging: Timestamp based uses column TS lifetime 1 minute cycle 1 minute on
1 table found.
(primary key columns are indicated with *)
Command> INSERT INTO tbaging VALUES (1, SYSDATE);
1 row inserted.
Command> INSERT INTO tbaging VALUES (2, SYSDATE);
1 row inserted.
Command> select * from tbaging;
< 1, 2016-04-13 23:50:51.000000 >
< 2, 2016-04-13 23:50:54.000000 >
2 rows found.
Command> sleep 120
Command> select * from tbaging;
0 rows found.
参考
- Cache User Guide: Chapter 4, Implementing aging in a cache group
4431
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
