TimesTen 应用层数据库缓存学习:8. 配置Sliding Window(滑动窗口)

本文主要是介绍TimesTen 应用层数据库缓存学习:8. 配置Sliding Window(滑动窗口),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

Sliding Window(滑动窗口)是Cache Group常用的一种场景。
本文介绍滑动窗口的基本概念以及如何配置滑动窗口。

滑动窗口的描述如下:

In a sliding window configuration, new rows are inserted into and old rows are deleted from the cache tables on a regular schedule so that the tables contain only the data that satisfies a specific time interval.

如何实现:

You can configure a sliding window for a cache group by using incremental autorefresh mode and defining a time-based aging policy. The autorefresh operation checks the timestamp of the rows in the cached Oracle Database tables to determine whether new data should be refreshed into the TimesTen cache tables. The system time and the time zone must be identical on the Oracle Database and TimesTen systems.

有两点必须注意,一个是时区和时间必须一致(在我们的实验环境中没有问题,TimesTen和Oracle都在一台主机上),一个是使用基于时间的Aging和增量autorefresh。

滑动窗口最常用的配置是Explicitly load Read-only Cache Group

Explicitly load Read only Sliding Window

只读滑动窗口是最常用的,可以使用incremental auto refresh。

例如,下面的缓存组缓存近一个月的订单,然后每天将新的订单加入缓存组:

CREATE READONLY CACHE GROUP recent_shipped_orders
AUTOREFRESH MODE INCREMENTAL INTERVAL 1440 MINUTES STATE ON
FROM oratt.orders(ord_num      NUMBER(10) NOT NULL,cust_num     NUMBER(6) NOT NULL,when_placed  DATE NOT NULL,when_shipped DATE NOT NULL,PRIMARY KEY(ord_num))
AGING USE when_shipped LIFETIME 30 DAYS CYCLE 24 HOURS ON;

我们可以模拟一下这个场景,但时间可以短些。

# 先在Oracle中建立订单表
$ sqlplus tthr/oracle@ttorcl
create table orders(ord_num int primary key, ship_time timestamp not null);
insert into orders values(1, sysdate);
insert into orders values(2, sysdate);
commit;SQL> set linesize 100
SQL> select * from orders;ORD_NUM SHIP_TIME
---------- ---------------------------------------------------------------------------1 14-APR-16 07.10.03.000000 AM2 14-APR-16 07.10.04.000000 AMSQL> grant select on orders to cacheadm;Grant succeeded.ttisql -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle" -e "set prompt 'cacheadm>'"
CREATE READONLY CACHE GROUP "RO_SW" AUTOREFRESH MODE INCREMENTAL INTERVAL 10 SECONDSSTATE ONFROM"TTHR"."ORDERS" ("ORD_NUM"   NUMBER(38)   NOT NULL,"SHIP_TIME" TIMESTAMP(6) NOT NULL,PRIMARY KEY("ORD_NUM"))AGING USE SHIP_TIME LIFETIME 300 seconds CYCLE 10 seconds ONcacheadm>cachegroups;Cache Group CACHEADM.RO_SW:Cache Group Type: Read OnlyAutorefresh: YesAutorefresh Mode: IncrementalAutorefresh State: OnAutorefresh Interval: 10 SecondsAutorefresh Status: okAging: Timestamp based uses column SHIP_TIME lifetime 300 seconds cycle 10 seconds onRoot Table: TTHR.ORDERSTable Type: Read Only1 cache group found.tthr>select * from orders;
< 1, 2016-04-14 07:10:03.000000 >
< 2, 2016-04-14 07:10:04.000000 >
2 rows found.# Oracle数据库中
# 用户sys执行 grant execute on dbms_lock to public;
# 用户tthr执行以下过程,产生订单:
declare ord_num number;      
begin
for i in 1..1000 loop
select max(ord_num) into ord_num from orders;
insert into orders values(ord_num+1, sysdate);
commit;
dbms_lock.sleep( 10 );
end loop;
end;
/$ ttisql  -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten;oraclepwd=oracle" -e "set prompt 'tthr>'"tthr>select ord_num, TIMESTAMPDIFF(SQL_TSI_SECOND,ship_time, sysdate) from orders;
< 12, 299 >
< 13, 289 >
< 14, 279 >
< 15, 269 >
< 16, 259 >
< 17, 249 >
< 18, 239 >
< 19, 229 >
< 20, 219 >
< 21, 209 >
< 22, 199 >
< 23, 189 >
< 24, 179 >
< 25, 169 >
< 26, 159 >
< 27, 149 >
< 28, 139 >
< 29, 129 >
< 30, 119 >
< 31, 109 >
< 32, 99 >
< 33, 89 >
< 34, 79 >
< 35, 69 >
< 36, 59 >
< 37, 49 >
< 38, 39 >
< 39, 29 >
< 40, 19 >
< 41, 9 >
30 rows found.
tthr>
过一小会
tthr>select ord_num, TIMESTAMPDIFF(SQL_TSI_SECOND,ship_time, sysdate) from orders;
< 18, 291 >
< 19, 281 >
< 20, 271 >
< 21, 261 >
< 22, 251 >
< 23, 241 >
< 24, 231 >
< 25, 221 >
< 26, 211 >
< 27, 201 >
< 28, 191 >
< 29, 181 >
< 30, 171 >
< 31, 161 >
< 32, 151 >
< 33, 141 >
< 34, 131 >
< 35, 121 >
< 36, 111 >
< 37, 101 >
< 38, 91 >
< 39, 81 >
< 40, 71 >
< 41, 61 >
< 42, 51 >
< 43, 41 >
< 44, 31 >
< 45, 21 >
< 46, 11 >
29 rows found.
tthr># 可以看到,时间差总是在300秒以内,订单从41滑动到46,老的订单12-17被淘汰

对于dynamic read-only以及AWT是否可以配置滑动窗口呢,答案是肯定的,只不过不是很常用。

If the cache group does not use incremental autorefresh mode, you can configure a sliding window by using a LOAD CACHE GROUP, REFRESH CACHE GROUP, or INSERT statement, or a dynamic load operation to bring new data into the cache tables.

Dynamic Load Read only Sliding Window

CREATE DYNAMIC READONLY CACHE GROUP "DRO_SW" AUTOREFRESH MODE INCREMENTAL INTERVAL 10 SECONDSSTATE PAUSEDFROM"TTHR"."ORDERS" ("ORD_NUM"   NUMBER(38)   NOT NULL,"SHIP_TIME" TIMESTAMP(6) NOT NULL,PRIMARY KEY("ORD_NUM"))AGING USE SHIP_TIME LIFETIME 300 seconds CYCLE 10 seconds ON
cacheadm>cachegroups;Cache Group CACHEADM.DRO_SW:Cache Group Type: Read Only (Dynamic)Autorefresh: YesAutorefresh Mode: IncrementalAutorefresh State: PausedAutorefresh Interval: 10 SecondsAutorefresh Status: okAging: Timestamp based uses column SHIP_TIME lifetime 300 seconds cycle 10 seconds onRoot Table: TTHR.ORDERSTable Type: Read Only1 cache group found.tthr>select count(*) from orders;
< 0 >
1 row found.
# 虽然可以通过dynamic load导入数据,不过批量导入还是得靠load。
# Load的作用是将新的数据导入缓存组,而autofresh同时也在工作,将已在缓存组的数据进行更新。
# 例如如果在TimesTen中的订单79,在Oracle端删除了,那么autorefresh也会将其删除# 用户tthr执行以下过程,产生订单:
declare ord_num number;      
begin
for i in 1..1000 loop
select max(ord_num) into ord_num from orders;
insert into orders values(ord_num+1, sysdate);
commit;
dbms_lock.sleep( 10 );
end loop;
end;
/
cacheadm>load cache group dro_sw commit every 256 rows;
3 cache instances affected.tthr>select count(*) from orders;
< 3 >
1 row found.cacheadm>load cache group dro_sw commit every 256 rows;
2 cache instances affected.tthr>select count(*) from orders;
< 5 >
1 row found.tthr>select ord_num, TIMESTAMPDIFF(SQL_TSI_SECOND,ship_time, sysdate) from orders;
< 78, 299 >
< 79, 289 >
< 81, 269 >
< 82, 259 >
< 83, 249 >
< 84, 239 >
< 85, 229 >
< 86, 219 >
< 87, 209 >
< 88, 199 >
< 89, 189 >
< 90, 179 >
< 91, 169 >
< 92, 159 >
< 93, 149 >
< 94, 139 >
< 95, 129 >
< 96, 119 >
< 97, 109 >
< 98, 99 >
< 99, 89 >
< 100, 79 >
< 101, 69 >
< 102, 59 >
< 103, 49 >
< 104, 39 >
< 105, 29 >
< 106, 19 >
28 rows found.
时间差总在300秒内

Explicitly Load AWT Sliding Window

前面两例为只读缓存,数据来源是Oracle。本例为AWT,数据来源在TimesTen。
使用场景为TimesTen作为数据收集的集中点,然后保存时间窗口的数据作为实时分析。
下面三个提示符分别是用以下三个命令登录的
* cacheadm - ttisql -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle" -e "set prompt 'cacheadm>'"

* tthr(schema user) - ttisql -connstr "dsn=cachedb1_1122;uid=tthr;pwd=timesten;oraclepwd=oracle" -e "set prompt 'tthr>'"

* Oracle DB - sqlplus tthr/oracle@ttorcl

SQL> grant select, insert, update, delete on orders to cacheadm;CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "AWT_SW" FROM"TTHR"."ORDERS" ("ORD_NUM"   NUMBER(38)   NOT NULL,"SHIP_TIME" TIMESTAMP(6) NOT NULL,PRIMARY KEY("ORD_NUM"))AGING USE SHIP_TIME LIFETIME 300 seconds CYCLE 10 seconds ONcacheadm>call ttrepstart;
tthr>select * from orders;
0 rows found.
cacheadm>cachegroups;Cache Group CACHEADM.AWT_SW:Cache Group Type: Asynchronous WritethroughAutorefresh: NoAging: Timestamp based uses column SHIP_TIME lifetime 300 seconds cycle 10 seconds onRoot Table: TTHR.ORDERSTable Type: Propagate1 cache group found.
cacheadm>repschemes;Replication Scheme TTREP._AWTREPSCHEME:Element: _1798096                       Type: Table TTHR.ORDERSMaster Store: CACHEDB1_1122 on TIMESTEN-HOL Transmit DurableSubscriber Store: _ORACLE from TIMESTEN-HOL Store: CACHEDB1_1122 on TIMESTEN-HOLPort: (auto)Log Fail Threshold: (none)Retry Timeout: 120 secondsCompress Traffic: DisabledStore: _ORACLE from TIMESTEN-HOLPort: (auto)Log Fail Threshold: (none)Retry Timeout: 120 secondsCompress Traffic: Disabled1 replication scheme found.
SQL> select count(*) from orders;COUNT(*)
----------134
# Oracle中有数据,但是一条也没load进来,原因是这些数据过老,不满足时间窗口的要求
cacheadm>load cache group awt_sw commit every 256 rows;
0 cache instances affected.
# 为了演示方便,我们删除掉Oracle中的数据
SQL> truncate table orders;Table truncated.
# 然后在Oracle中初始化一条数据
SQL> insert into orders values(1, sysdate);1 row created.SQL> commit;Commit complete.
cacheadm>load cache group awt_sw commit every 256 rows;
1 cache instance affected.
tthr>select * from orders;                                                                                                                                              < 1, 2016-04-14 22:28:35.000000 >
1 row found.# 然后在TimesTen端插入数据
tthr>ALTER SESSION SET PLSQL_TIMEOUT = 0; <- 缺省是30秒Session altered.
declare ord_num number;      
begin
for i in 1..1000 loop
select max(ord_num) into ord_num from orders;
insert into orders values(ord_num+1, sysdate);
commit;
dbms_lock.sleep( 10 );
end loop;
end;
/# 另起一个tthr会话
tthr>select * from orders;
< 2, 2016-04-14 22:29:59.000000 >
< 3, 2016-04-14 22:30:09.000000 >
< 4, 2016-04-14 22:30:19.000000 >
< 5, 2016-04-14 22:33:35.000000 >
< 6, 2016-04-14 22:33:45.000000 >
< 7, 2016-04-14 22:33:55.000000 >
6 rows found.
# 我们可以看到,TimesTen中的数据不断增加
# 而时间差在300秒以内
tthr>select ord_num, TIMESTAMPDIFF(SQL_TSI_SECOND,ship_time, sysdate) from orders;
< 16, 300 >
< 17, 290 >
< 18, 280 >
< 19, 270 >
< 20, 260 >
< 21, 250 >
< 22, 240 >
< 23, 230 >
< 24, 220 >
< 25, 210 >
< 26, 200 >
< 27, 190 >
< 28, 180 >
< 29, 170 >
< 30, 160 >
< 31, 150 >
< 32, 140 >
< 33, 130 >
< 34, 120 >
< 35, 110 >
< 36, 100 >
< 37, 90 >
< 38, 80 >
< 39, 70 >
< 40, 60 >
< 41, 50 >
< 42, 40 >
< 43, 30 >
< 44, 20 >
< 45, 10 >
30 rows found.# 而在Oracle中保留有所有的历史数据
SQL> select count(*) from orders;COUNT(*)
----------55

Dynamic Load AWT Sliding Window

就不讨论了,和上例差不多

这篇关于TimesTen 应用层数据库缓存学习:8. 配置Sliding Window(滑动窗口)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/677709

相关文章

SpringBoot实现数据库读写分离的3种方法小结

《SpringBoot实现数据库读写分离的3种方法小结》为了提高系统的读写性能和可用性,读写分离是一种经典的数据库架构模式,在SpringBoot应用中,有多种方式可以实现数据库读写分离,本文将介绍三... 目录一、数据库读写分离概述二、方案一:基于AbstractRoutingDataSource实现动态

SpringCloud动态配置注解@RefreshScope与@Component的深度解析

《SpringCloud动态配置注解@RefreshScope与@Component的深度解析》在现代微服务架构中,动态配置管理是一个关键需求,本文将为大家介绍SpringCloud中相关的注解@Re... 目录引言1. @RefreshScope 的作用与原理1.1 什么是 @RefreshScope1.

C# WinForms存储过程操作数据库的实例讲解

《C#WinForms存储过程操作数据库的实例讲解》:本文主要介绍C#WinForms存储过程操作数据库的实例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、存储过程基础二、C# 调用流程1. 数据库连接配置2. 执行存储过程(增删改)3. 查询数据三、事务处

SpringBoot日志配置SLF4J和Logback的方法实现

《SpringBoot日志配置SLF4J和Logback的方法实现》日志记录是不可或缺的一部分,本文主要介绍了SpringBoot日志配置SLF4J和Logback的方法实现,文中通过示例代码介绍的非... 目录一、前言二、案例一:初识日志三、案例二:使用Lombok输出日志四、案例三:配置Logback一

springboot security之前后端分离配置方式

《springbootsecurity之前后端分离配置方式》:本文主要介绍springbootsecurity之前后端分离配置方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的... 目录前言自定义配置认证失败自定义处理登录相关接口匿名访问前置文章总结前言spring boot secu

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

springboot简单集成Security配置的教程

《springboot简单集成Security配置的教程》:本文主要介绍springboot简单集成Security配置的教程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,... 目录集成Security安全框架引入依赖编写配置类WebSecurityConfig(自定义资源权限规则

SpringBoot中封装Cors自动配置方式

《SpringBoot中封装Cors自动配置方式》:本文主要介绍SpringBoot中封装Cors自动配置方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot封装Cors自动配置背景实现步骤1. 创建 GlobalCorsProperties

Spring Boot结成MyBatis-Plus最全配置指南

《SpringBoot结成MyBatis-Plus最全配置指南》本文主要介绍了SpringBoot结成MyBatis-Plus最全配置指南,包括依赖引入、配置数据源、Mapper扫描、基本CRUD操... 目录前言详细操作一.创建项目并引入相关依赖二.配置数据源信息三.编写相关代码查zsRArly询数据库数

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们