« 参数_smon_internal_errlimit与数据库恢复 | Blog首页 | ORA-27069 文件数据块损坏 数据库恢复一则 »
SAP的 XI_AF_MSG 消息表优化及清理
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/03/sap_xi_af_msg.html
XI_AF_MSG表在SAP XI系统中,存储的是Java Adapter Engine的输入输出信息(stores incoming and outgoing messages)。链接:https://www.eygle.com/archives/2011/03/sap_xi_af_msg.html
根据设定的规则,这个表的数据量通常应该很低,但是在一些异常情况下(有时候是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:
-----------------------------------------------------------------------------------SAP的系统也是相当庞大,极其复杂,慢慢学习,用Oracle的视角去学习和研究。
| 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
历史上的今天...
>> 2012-03-09文章:
>> 2010-03-09文章:
>> 2009-03-09文章:
>> 2008-03-09文章:
>> 2005-03-09文章:
By eygle on 2011-03-09 07:53 | Comments (0) | Case | 2750 |