SAP的 XI_AF_MSG 消息表优化及清理

XI_AF_MSG表在SAP XI系统中，存储的是Java Adapter Engine的输入输出信息（stores incoming and outgoing messages）。

根据设定的规则，这个表的数据量通常应该很低，但是在一些异常情况下（有时候是Bug），XI_AF_MSG表的数据量会急剧增加，导致运算缓慢，在客户的系统中，这个表的记录数达到了140万，其LOB对象的存储空间占用了10G，这是相当大的一个容量，几乎不能容忍：

SQL> select count(*) from "SAPSR3DB"."XI_AF_MSG" ;

  COUNT(*)
----------
   1389443

SQL> col segment_name for a30
SQL> select segment_name ,bytes/1024/1024/1024 from dba_segments where segment_name='XI_AF_MSG';

SEGMENT_NAME                   BYTES/1024/1024/1024
------------------------------ --------------------
XI_AF_MSG                                1.81640625

SQL> col segment_name for a40
SQL> select segment_name ,bytes/1024/1024/1024 GB from dba_segments
  2  where segment_name in (select segment_name from dba_lobs where table_name='XI_AF_MSG');

SEGMENT_NAME                                     GB
---------------------------------------- ----------
SYS_LOB0000053939C00038$$                .000061035
SYS_LOB0000053939C00032$$                .000061035
SYS_LOB0000053939C00020$$                10.8710938

如果调度任务不能清理掉这些数据，那么可以根据状态的不同，将其中的过期数据，手工清除（根据PERSIST_UNTIL判断来Delete删除），这其中的某些数据可能来自某些失败的任务调度，对于DBA来说，直接从后台Delete数据具有一定的风险，必须清楚的了解这些表之间的关系：

SQL> select status, count(1) as current_number from "SAPSR3DB"."XI_AF_MSG" group by status;

STATUS   CURRENT_NUMBER
-------- --------------
DLVD              40976
WAIT                 28
NDLV            1348447
HOLD                  8

还有意想不到的发现，XI系统中最频繁访问的数据表居然就是XI_AF_MSG，在某些采样时段，获得的信息是这样的：

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
SAPSR3DB	PSAPSR3DB	XI_AF_MSG		TABLE	7,010,304	75.70
SAPSR3	PSAPSR3	SXMSPMAST2		TABLE	569,808	6.15
SAPSR3	PSAPSR3	TRFCQIN~4		INDEX	104,800	1.13
SAPSR3	PSAPSR3700	REPOSRC		TABLE	97,568	1.05
SAPSR3	PSAPSR3700	REPOSRC~0		INDEX	95,696	1.03

类似这样的查询，都执行了全表扫描，每次执行大约需要34秒时间：
SELECT "MSG_ID" FROM "XI_AF_MSG" WHERE "MSG_ID" = :1 AND "DIRECTION" = :2

Plan Statistics

• % Total DB Time is the Elapsed Time of the SQL statement divided
  into the Total Database Time multiplied by 100
  

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	2,302,315	34,362.92	29.34
CPU Time (ms)	79,442	1,185.70	23.65
Executions	67
Buffer Gets	6,319,143	94,315.57	35.66
Disk Reads	2,116,069	31,583.12	20.53
Parse Calls	6	0.09	0.02
Rows	0	0.00
User I/O Wait Time (ms)	2,217,458
Cluster Wait Time (ms)	0
Application Wait Time (ms)	0
Concurrency Wait Time (ms)	4
Invalidations	0
Version Count	1
Sharable Mem(KB)	16

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
0	SELECT STATEMENT				23885 (100)
1	TABLE ACCESS FULL	XI_AF_MSG	1	46	23885 (1)	00:04:47

这显然是索引出了问题，通过重建MSG_ID上的相关索引，使得这个SQL的逻辑读降低到3：

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    46 |     2   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00142245 |     1 |    46 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        443  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SAP的系统也是相当庞大，极其复杂，慢慢学习，用Oracle的视角去学习和研究。