eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 参数_smon_internal_errlimit与数据库恢复 | Blog首页 | ORA-27069 文件数据块损坏 数据库恢复一则 »

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,在某些采样时段,获得的信息是这样的:
OwnerTablespace NameObject NameSubobject NameObj. TypeLogical 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 NameStatement TotalPer 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的视角去学习和研究。




历史上的今天...
    >> 2012-03-09文章:
    >> 2010-03-09文章:
    >> 2009-03-09文章:
           Eygle.Com 网站历史
    >> 2008-03-09文章:
    >> 2005-03-09文章:

By eygle on 2011-03-09 07:53 | Comments (0) | Case | 2750 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com