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

本文主要是介绍TimesTen 应用层数据库缓存学习：11. AWT性能监控，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

演示环境准备

为了运行以下的例子，我们建立了一个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 WritethroughAutorefresh: NoAging: No aging definedRoot 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.

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

$ ttrepadmin -showconfig cachedb1_1122Self 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     0Last 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_1122Replication Agent Status as of: 2016-04-21 18:04:39DSN                         : 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.65206536Replication Peers:Name                     : _ORACLEHost                     : TIMESTEN-HOLPort                     : 42901 (AUTO) (Connected)Replication State        : STARTEDCommunication Protocol   : 38Name                     : CACHEDB1_1122Host                     : TIMESTEN-HOLPort                     : 0 (AUTO)Replication State        : STARTEDCommunication Protocol   : 38TRANSMITTER thread(s):For                     : _ORACLE (track 0)Start/Restart count   : 1Send LSN              : 18.65245448Transactions sent     : 467Total packets sent    : 856Tick packets sent     : 384MIN sent packet size  : 64MAX sent packet size  : 893AVG sent packet size  : 291Last packet sent at   : 18:04:39Total Packets received: 854MIN rcvd packet size  : 64MAX rcvd packet size  : 120AVG rcvd packet size  : 119Last packet rcvd'd at : 18:04:39TXNs Allocated        : 11TXNs In Use           : 5ACTs Allocated        : 11ACTs In Use           : 5ACTs Data Allocated   : 880Timeout               : 120Adapted Timeout Max   : 120Adapted Timeout Time  : 1461285423current txn           : 0.0Longest batch runtime : 0Longest batch 1st txn : 0.0Longest batch lst txn : 0.0Largest txn (ops)     : 1461231493.12371Largest txn (#ops)    : 1Longest txn (time)    : 0.0Longest txn (secs)    : 0Most recent errors (max 5):TT16025 in repagent.c (line 1227) at 17:34:49 on 04-21-2016TT16285 in transmitter.c (line 1109) at 17:34:49 on 04-21-2016TT16999 in transmitter.c (line 1447) at 17:34:49 on 04-21-2016RECEIVER thread(s):For                     : CACHEDB1_1122 (track 0)Start/Restart count   : 1Transactions received : 467Total packets sent    : 855Tick packets sent     : 0MIN sent packet size  : 64MAX sent packet size  : 120AVG sent packet size  : 119Last packet sent at   : 18:04:39Total Packets received: 2259MIN rcvd packet size  : 64MAX rcvd packet size  : 298AVG rcvd packet size  : 110Last packet rcvd'd at : 18:04:39rxWaitCTN             : 0.0prevCTN               : 0.0current txn           : 0.0serial CTN            : 0.0STA Blk Data Allocated: 32STA Data Allocated    : 4096Longest batch runtime : 0Longest batch 1st txn : 0.0Longest batch lst txn : 0.0Largest txn (ops)     : 1461231493.12371Largest txn (#ops)    : 5Longest txn (time)    : 0.0Longest txn (secs)    : 0AWT 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 millisecsCacheAwtMethod mode : 1Cache-connect Operational Stats :SQL Operations sent to Oracle : 0Number of update operations : 0Number of update batches    : 0Number of insert operations : 0Number of insert batches    : 0Number of delete operations : 0Number of delete batches    : 0Total number of batches sent: 0Number of bytes sent : 0PL/SQL Operations sent to Oracle : 0Number of update operations : 0Number of insert operations : 0Number of delete operations : 0Number of batches sent : 0Number of bytes sent : 0Number of TimesTen Transactions sent to Oracle (includes retries) : 376Number of retries on TimesTen due to errors on Oracle : 0Number of round trips to Oracle (includes executes, commits and rollbacks) : 0Number of commits on Oracle : 0Number of rollbacks on Oracle : 0Number of rxbatches: 376Number of rxskips: 0Most 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     0Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
00:00:04      -               -1.00      -1        -1    1