TimesTen 应用层数据库缓存学习：11. AWT性能监控

最新推荐文章于 2018-04-28 16:53:38 发布

dingdingfish

最新推荐文章于 2018-04-28 16:53:38 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： TimesTen缓存组 In-Memory Computing Technology 文章标签： timesten cachegroup 缓存组 AWT

本文链接：https://blog.csdn.net/stevensxiao/article/details/51217474

In-Memory Computing Technology 同时被 2 个专栏收录

189 篇文章

订阅专栏

TimesTen缓存组

44 篇文章

订阅专栏

本文介绍了一种通过建立AWT缓存组实现Oracle与TimesTen间数据同步的方法，并展示了如何配置缓存组、开启性能监控及利用系统表进行监控。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

演示环境准备

为了运行以下的例子，我们建立了一个AWT缓存组
Oracle Schema用户:

$ sqlplus tthr/oracle@ttorcl
create table orders(ord_num int primary key, ship_time timestamp not null);
grant select, insert, update, delete on orders to cacheadm;

TimesTen Cache管理用户:

cacheadm>
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "AWT" 
 FROM
  "TTHR"."ORDERS" (
    "ORD_NUM"   NUMBER(38)   NOT NULL,
    "SHIP_TIME" TIMESTAMP(6) NOT NULL,
    PRIMARY KEY("ORD_NUM")
  )
cacheadm> call ttrepstart;
cacheadm> cachegroups;

Cache Group CACHEADM.AWT:

  Cache Group Type: Asynchronous Writethrough
  Autorefresh: No
  Aging: No aging defined

  Root Table: TTHR.ORDERS
  Table Type: Propagate

1 cache group found.

cacheadm>repschemes;

Replication Scheme TTREP._AWTREPSCHEME:

  Element: _1798096                       
  Type: Table TTHR.ORDERS
  Master Store: CACHEDB1_1122 on TIMESTEN-HOL Transmit Durable
  Subscriber Store: _ORACLE from TIMESTEN-HOL 

  Store: CACHEDB1_1122 on TIMESTEN-HOL
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

  Store: _ORACLE from TIMESTEN-HOL
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

1 replication scheme found.

OS用户,下面的输出类似于repschems

$ ttrepadmin -showconfig cachedb1_1122

Self host "TIMESTEN-HOL", port auto, name "CACHEDB1_1122", LSN 19/2685192, timeout 120, threshold 0


List of subscribers
-------------------

Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
_ORACLE           TIMESTEN-HOL              Auto   Start      38     0

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS     
------------- ------------- ------- ------- ---------     
00:00:05      -               -1.00      -1        -1 




List of objects and subscriptions
---------------------------------


Table details
-------------
Table : TTHR.ORDERS   Timestamp updates : -  

Master Name               Subscriber name         
-----------               ---------------         
CACHEDB1_1122             _ORACLE

TimesTen Schema用户:

tthr>
insert into orders values(1, sysdate);
ALTER SESSION SET PLSQL_TIMEOUT = 0;
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( 1 );
end loop;
end;
/

打开 AWT 性能监控

使用ttCacheAWTMonitorConfig打开监控，然后可以用ttRepAdmin查看监控信息。
其中On表示打开，数字16表示16次采样一次，是推荐值，但在我们的例子中，我们采用1，即每次都采样

cacheadm>CALL ttCacheAWTMonitorConfig;
< OFF, 0 >
1 row found.
cacheadm>CALL ttCacheAWTMonitorConfig('off');
< OFF, 0 >
1 row found.
cacheadm>CALL ttCacheAWTMonitorConfig('on');
< ON, 16 >
1 row found.
cacheadm>CALL ttCacheAWTMonitorConfig('on', 1);
< ON, 1 >
1 row found.
cacheadm>CALL ttCacheAWTMonitorConfig;
< ON, 1 >
1 row found.

Display AWT performance results with the ttRepAdmin utility

使用ttRepAdmin的-awtmoninfo 和 -showstatus 选项，例如

$ ttRepAdmin -showstatus -awtmoninfo cachedb1_1122

Replication Agent Status as of: 2016-04-21 18:04:39

DSN                         : cachedb1_1122
Process ID                  : 8089 (Started)
Replication Agent Policy    : manual
Host                        : TIMESTEN-HOL
RepListener Port            : 42901 (AUTO)
Last write LSN              : 18.65272072
Last LSN forced to disk     : 18.65271808
Replication hold LSN        : 18.65206536

Replication Peers:
   Name                     : _ORACLE
   Host                     : TIMESTEN-HOL
   Port                     : 42901 (AUTO) (Connected)
   Replication State        : STARTED
   Communication Protocol   : 38

   Name                     : CACHEDB1_1122
   Host                     : TIMESTEN-HOL
   Port                     : 0 (AUTO)
   Replication State        : STARTED
   Communication Protocol   : 38

TRANSMITTER thread(s):
 For                     : _ORACLE (track 0)
   Start/Restart count   : 1
   Send LSN              : 18.65245448
   Transactions sent     : 467
   Total packets sent    : 856
   Tick packets sent     : 384
   MIN sent packet size  : 64
   MAX sent packet size  : 893
   AVG sent packet size  : 291
   Last packet sent at   : 18:04:39
   Total Packets received: 854
   MIN rcvd packet size  : 64
   MAX rcvd packet size  : 120
   AVG rcvd packet size  : 119
   Last packet rcvd'd at : 18:04:39
   TXNs Allocated        : 11
   TXNs In Use           : 5
   ACTs Allocated        : 11
   ACTs In Use           : 5
   ACTs Data Allocated   : 880
   Timeout               : 120
   Adapted Timeout Max   : 120
   Adapted Timeout Time  : 1461285423
   current txn           : 0.0
   Longest batch runtime : 0
   Longest batch 1st txn : 0.0
   Longest batch lst txn : 0.0
   Largest txn (ops)     : 1461231493.12371
   Largest txn (#ops)    : 1
   Longest txn (time)    : 0.0
   Longest txn (secs)    : 0
   Most recent errors (max 5):
     TT16025 in repagent.c (line 1227) at 17:34:49 on 04-21-2016
     TT16285 in transmitter.c (line 1109) at 17:34:49 on 04-21-2016
     TT16999 in transmitter.c (line 1447) at 17:34:49 on 04-21-2016

RECEIVER thread(s):
 For                     : CACHEDB1_1122 (track 0)
   Start/Restart count   : 1
   Transactions received : 467
   Total packets sent    : 855
   Tick packets sent     : 0
   MIN sent packet size  : 64
   MAX sent packet size  : 120
   AVG sent packet size  : 119
   Last packet sent at   : 18:04:39
   Total Packets received: 2259
   MIN rcvd packet size  : 64
   MAX rcvd packet size  : 298
   AVG rcvd packet size  : 110
   Last packet rcvd'd at : 18:04:39
   rxWaitCTN             : 0.0
   prevCTN               : 0.0
   current txn           : 0.0
   serial CTN            : 0.0
   STA Blk Data Allocated: 32
   STA Data Allocated    : 4096
   Longest batch runtime : 0
   Longest batch 1st txn : 0.0
   Longest batch lst txn : 0.0
   Largest txn (ops)     : 1461231493.12371
   Largest txn (#ops)    : 5
   Longest txn (time)    : 0.0
   Longest txn (secs)    : 0

   AWT Monitoring statistics
   -------------------------
   TimesTen processing time : 138.387000 millisecs (100 %)
   Oracle execute  (SQL execution) time : 0.000000 millisecs (0 %)
   Oracle execute (PL/SQL execution) time : 0.000000 millisecs (0 %)
   Time since monitoring was started: 375830.816000 millisecs
   CacheAwtMethod mode : 1
   Cache-connect Operational Stats :
      SQL Operations sent to Oracle : 0
           Number of update operations : 0
           Number of update batches    : 0
           Number of insert operations : 0
           Number of insert batches    : 0
           Number of delete operations : 0
           Number of delete batches    : 0
           Total number of batches sent: 0
           Number of bytes sent : 0
      PL/SQL Operations sent to Oracle : 0
           Number of update operations : 0
           Number of insert operations : 0
           Number of delete operations : 0
           Number of batches sent : 0
           Number of bytes sent : 0
      Number of TimesTen Transactions sent to Oracle (includes retries) : 376
      Number of retries on TimesTen due to errors on Oracle : 0
      Number of round trips to Oracle (includes executes, commits and rollbacks) : 0
      Number of commits on Oracle : 0
      Number of rollbacks on Oracle : 0
      Number of rxbatches: 376
      Number of rxskips: 0
   Most recent errors (max 5):
     TT16025 in repagent.c (line 1227) at 17:34:49 on 04-21-2016

注意AWT Monitoring statistics部分。

Using system tables to monitor AWT operations

查询SYS.MONITOR的LAST_LOG_FILE, REPHOLD_LOG_FILE 和 REPHOLD_LOG_OFF列。
它们的含义为:
- LAST_LOG_FILE: Most recent log file present
- REPHOLD_LOG_FILE: Number of last log file held by replication
- REPHOLD_LOG_OFF: Offset in last log file held by replication

SYS.MONITOR只有一行记录, 输出中的18表示log文件是cachedb1_1122.log18

cacheadm>select LAST_LOG_FILE, REPHOLD_LOG_FILE, REPHOLD_LOG_OFF from SYS.MONITOR;
< 18, 18, 63803656 >
1 row found.
cacheadm>select LAST_LOG_FILE, REPHOLD_LOG_FILE, REPHOLD_LOG_OFF from SYS.MONITOR;
< 18, 18, 63854856 >
1 row found.
cacheadm>select LAST_LOG_FILE, REPHOLD_LOG_FILE, REPHOLD_LOG_OFF from SYS.MONITOR;
< 18, 18, 63854856 >
1 row found.

依据下面的原则来判断是非有问题，即如果复制hold的日志文件和产生的最新日志文件差距不断拉大，则表示复制数据的速度赶不上产生数据的速度

If the difference between the value in the LAST_LOG_FILE column and the value in the REPHOLD_LOG_FILE column is increasing over time, and the value in the REPHOLD_LOG_OFF column is increasing slowly or not changing, then the tables are being updated at a faster rate than the updates are being replicated.

如果有问题，可以结合以下的命令再判断

$ ttRepAdmin -receiver -list cachedb1_1122
Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
_ORACLE           TIMESTEN-HOL              Auto   Start      38     0

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
00:00:04      -               -1.00      -1        -1    1