SAP+DB2 糟糕的报表查询『ZCOR0015』 优化全程记录

archer05

ZCOR0015的优化全过程记录文档







    2015年3月，今天无意翻到这篇写于2010年7月的文档，回想那时的工作，毕业3年初出茅庐的我面对接触不多的SAP+DB2竟敢操刀动斧，自信满满。
虽然这过程一路坎坷，数次判断几乎全都被打脸验证，看着如小强般坚毅的我，哈哈~ 文档里带着情绪的措词十分逗笑，可以洞悉那时的心情。
——如今5年过去，却久未闻硝烟，怀念ing~
　　


目录:
ZCOR0015的优化全过程记录文档... 1
内容说明:1
1.寻找问题点,切入分析... 2
2.再次碰到问题,转折点.. 6
3.反复尝试,直至最后解决... 9
4.结果... 12
5.总结... 14
6.参考文档... 15


内容说明:

红色字体为突出个别文字的内容
黑色字体为普通过程阐述
蓝色字体为图解说明
黑色背景的字体为一些关键步骤点

优化目的:让ZCOR0015能够迅速取出数据,并且不通过MKPFMSEG中间表的处理.
业务的接受度: 跑1个月跨度的报表,要在5分钟内返回结果,跑半年的报表,要在1小时内返回结果.

结果: ZCOR0015 统计一个月的数据20090101~20090131




X轴为消耗时间,单位(秒)
上图为优化后的时间消耗,下图为优化前的时间消耗.
红色为数据库层时间消耗,蓝色为ABAP层时间消耗.

1.寻找问题根源,切入分析

先在se38里找出该程序,根据开发人员的测试,该程序的大部分的时间消耗在数据库层面,过后开发人员做了一个定时job,定期把视图WB2_V_MKPF_MSEG2的数据导入MKPFMSEG表里, 写程序的时候就直接从MKPFMSEG里取数据即可,这样暂时可以解决这个程序的缓慢问题,但是却把性能的隐患转移至后台job程序上了, 当前这个job跑起来非常消耗资源,并且也存在数据实时性的问题,
这次的调优目的就是彻底的解决这个问题.
我一开始的假想,认为这性能的问题,无非就是SQL语句所走的执行计划匹配上不合理的索引,导致不合理的资源消耗,当然这个早早下的结论也没错,只是太宽泛了,起初我认为折腾一天就可以搞定,甚至是一下午,但是最终却消耗了10天的时间…
没有全盘了解系统的细节结构、应用层面的逻辑 对SAP经验不足, 却又自信满满的姿态去看这次的问题 ,其实还蛮可怕的 ,这次的事情 所覆盖的知识面完全超出了我的想象。
把这段存在性能问题的sql从abap程序里抓出来,基本如下:



SAP端:
select a~matnr_i
a~mblnr_i
a~bwart_i
* b~aufnr
a~budat
a~WERKS_I
a~bukrs_i
a~menge_i
a~mjahr_i
a~aufnr_i
a~SHKZG_I
INTO CORRESPONDING FIELDS OF TABLE g_t_result
from WB2_V_MKPF_MSEG2 as a "inner join aufm as b on a~mblnr_i = b~mblnr and a~mjahr_i = b~mjahr and a~matnr_i = b~matnr
where
a~budat BETWEEN s_budat1 AND s_budat2 and
a~bukrs_i = s_bukrs and
a~werks_i = s_werks and
a~mjahr_i = s_mjahr and
a~bwart_i in ('101','102','122','123','161','162','Z09','Z10','Z11','Z12','Z21','Z22','Z01','Z02','601','602','653','654','261','262','531','532','543','544','961','962') AND
a~matnr_i BETWEEN '000000000010000000' and '000000000019999999'.
　　
改成DB2里的SQL: (先暂时改成count(*) 计数,优化完了再改为实际取的字段名,大体上执行计划不会有差异.)



    select count(*)
from sapprd."MKPF" T0001, sapprd."MSEG" T0002
where
T0002."MANDT"= T0001."MANDT"
AND T0002."MBLNR"= T0001."MBLNR"
AND T0002."MJAHR"= T0001."MJAHR"
AND T0001."BUDAT" BETWEEN '20090101' AND '20090131'and
T0002."BUKRS"='1000'and
T0002."WERKS"='1000'and
T0001."MJAHR"='2009'and
T0002."BWART"in('101','102','122','123','161','162','Z09','Z10','Z11','Z12','Z21','Z22','Z01','Z02',
'601','602','653','654','261','262','531','532','543','544','961','962') AND
T0002."MATNR" BETWEEN '000000000010000000'and'000000000019999999';
执行计划如下,MKPF上的索引是非常不合理的,光是扫描一个索引就消耗那么多的资源,从这里就判定出索引的不合理性.

下面为这MSEG,MKPF这2张表在SDX里的状况,行数,统计信息更新时间,页数等等.与生产机的数据量基本同属于一个数量级.
第二段是MKPF的两个索引状况,叶子数,索引字段的基数,统计信息时间.(还有levels 一般不超过3)

以下为这个执行计划消耗的I/O ,CPU,BUFFERS等等资源的状况. (这里为估算值,并非真正的实际值)

接着开始在DB2数据库层面优化SQL.
单纯在数据库层面其实不复杂,就那么几个招式:
1.改SQL.
2.建立合适的索引(根据业务逻辑+需要把多少工作交给ABAP层面来做,减少SQL的谓词来取一个平衡点)
3.收集统计信息,让数据库有稳定的执行计划.

把SQL改写了一下

• T0002."BUKRS"='1000'and
  
• T0002."WERKS"='1000'and
  
• T0001."MJAHR"='2009'and
  
• T0002."BWART"in('101','102','122','123','161','162','Z09','Z10','Z11','Z12','Z21','Z22','Z01','Z02',
  
• '601','602','653','654','261','262','531','532','543','544','961','962')
  

把上面这部分繁琐的业务逻辑去掉,如果把这部分内容纳入SQL里的话,会明显的加重取回数据的负担,将会扫描更多的索引页,或是建立更多的索引,本身SAP的内表就很强大,把这部分逻辑交给ABAP层面做即可,先取回数据到内表里,再LOOP循环一次,把结果筛选一遍,这样的思路应该是最优的.
然后再分别建两个索引.
附上建立的ddl:



    CREATE INDEX "SAPPRD"."IX_MKPF_BUDATMANDT"
ON "SAPPRD"."MKPF"
("BUDAT" ASC,
"MANDT" ASC
)
PCTFREE 10
DISALLOW REVERSE SCANS
COLLECT DETAILED STATISTICS;
GRANT CONTROL ON INDEX "SAPPRD"."IX_MKPF_BUDATMANDT" TO USER "SAPPRD";
CREATE INDEX "SAPPRD"."MSEG~GM"
ON "SAPPRD"."MSEG"
("MANDT" ASC,
"MBLNR" ASC,
"MJAHR" ASC,
"MATNR" ASC
)
PCTFREE 10
DISALLOW REVERSE SCANS
COLLECT DETAILED STATISTICS;
GRANT CONTROL ON INDEX "SAPPRD"." MSEG~GM " TO USER "SAPPRD";
　　

大致上解释下这几个参数,
PCTFREE:为填充因子,10的含义是 遗留下10%的页空间,以供insert or update时不至于那么快导致页分裂,如果值太大的话,会导致扫描更多的索引页,增加成本.
DISALLOW REVERSE SCANS: 是否使用反转索引,其实在这是物理上的概念,并非索引就倒序了,只是避免热点块的发生,我们的SAP不存在那么高的并发,所以DISALLOW即可.
COLLECT DETAILED STATISTICS: 收集详细的统计信息.
接着在DB2里执行一下这个SQL:



    select
T0002.matnr,
T0001.mblnr,
T0002.bwart,
T0001.budat,
T0002.WERKS,
T0002.bukrs,
T0002.menge,
T0001.mjahr,
T0002.aufnr,
T0002.SHKZG
from sapprd."MKPF" T0001 inner join sapprd."MSEG" T0002 on
T0002."MANDT"= T0001."MANDT"
AND T0002."MBLNR"= T0001."MBLNR"
AND T0002."MJAHR"= T0001."MJAHR"
where T0001."BUDAT" BETWEEN '20090101' AND '20090201'and
T0002."MATNR" BETWEEN '000000000010000000'and'000000000019999999';
　　


速度不错,两次索引扫描,然后fetch 出数据,再做Nested loop join,
并且先loop的小表MKPF,成本都很低,这已经是最佳的执行计划以及索引选择了,这个时候我笑了,我认为问题已经完美解决,并且返回数据的速度都在个位数的秒级.

2.再次碰到问题,转折点.

这时我把程序交给开发人员,我把思路说明了一遍,我说这个程序现在1个月数据量在10秒内即可出来,半年的数据量顶天也就1分钟,
等开发人员把程序写入ABAP报表里了,跑了一下,我傻眼了....一个月的数据量, 竟然需要10+分钟...和我预期的完全不一样.
脑袋里开始空白了,这是过于自信所带来的空白..

开始扫荡式寻找问题点,数据库性能,数据库参数,AIX性能,磁盘
存储 网络,ABAP代码..全都翻了一遍,
起初发现AIX里的TOPAS显示

hdisk4
的busy%程度非常高,高达90+,但是CPU显示的user不高,大部分CPU时间都是在wait,IDLE基本持平90%左右 (这时SDX的环境只有我一个人在用)
(tips:后来事情结束后才恍然大悟,其实大量表扫描+hash join的特征.)

虽然我脑子里很清晰的知道,他们出问题的可能性不大,
其实这时我心里很清楚，由于我自身的瓶颈（SAP+DB2的经验不足）其间的逻辑交互机制不清晰，单方面从数据库层面来判断问题，显然是捉襟见肘的。
但是近乎自负的那种自信却抵触了我否定自己的念头。

接着还是一味的在AIX操作系统层面寻找问题…… 并且还消耗了蛮多时间，索性到最后，还是回到数据库，啃了些文档，打算在数据库层面寻找问题点。

• select*from sysibmadm.TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 5 rows only;
  

上面的语句可以找出在共享池里的SQL,按执行时间排序.(这个手段我在Oracle和SQLServer里倒是经常用,但是DB2比较陌生..)
发现了从ABAP层送出的SQL语句和我所认知的完全不一样,这个时候开始有突破口了.
下面的代码为ABAP层面送给DB2的原句
SELECT T_00."MATNR",T_01."MBLNR",T_00."BWART",T_01."BUDAT",
                                 T_00."WERKS",T_00."BUKRS",T_00."MENGE",T_01."MJAHR",
                                                T_00."AUFNR",T_00."SHKZG"
FROM SAPPRD."MSEG"T_00 INNER JOIN SAPPRD."MKPF"T_01 ON
                            T_01."MANDT"=?
                            AND T_01."MANDT"=T_00."MANDT"
                            AND T_01."MBLNR"=T_00."MBLNR"
                            AND T_01."MJAHR"=T_00."MJAHR"
WHERE
T_00."MANDT"=? AND
T_01."BUDAT" BETWEEN ? AND ? AND
T_00."MATNR" BETWEEN ? AND ?  
WITH UR -- OPTLEVEL( 10 ) -- QUERY_DEGREE( 1 )
-- LOCATION( ZCOR0015_1 , 125 ) -- SYSTEM( SDX , SAPPRD )'


看了下执行计划,终于找到问题了,
ABAP层面送出的SQL语句,所导致的执行计划是非常烂的,又走回之前的2个索引,一个MKPF~BUD,一个是MSEG~M,
并且根据这个索引计算出来的连接方式为HASH JOIN. 使用不合理的索引,带来的成本是非常高的.

这段程序和我所想传送给数据库的语句有2个不同的地方
首先是T_01."MANDT"=?

其次是WITH UR --
OPTLEVEL( 10 ) -- QUERY_DEGREE( 1 ) -- LOCATION( ZCOR0015_1 , 125 ) -- SYSTEM(
SDX , SAPPRD )'

琢磨了下 MANDT这个是cline代码,用来区分800,900的数据,SAP自动给加上的,可以理解
再者是这个WITH UR 是锁级别,
OPTLEVEL(10)是优化级别,QUERY_DEGREE是并行的参数.
为什么带这些东西后,这段程序的执行计划差异会那么大呢??

我把『?』问号 都替换成了实际值,然后套入SQL里,执行下面的语句.
而且还在我后来建立的MKPF索引里添加了MANDT字段,
SELECT T_00."MATNR",T_01."MBLNR",T_00."BWART",T_01."BUDAT",
                                 T_00."WERKS",T_00."BUKRS",T_00."MENGE",T_01."MJAHR",
                                                T_00."AUFNR",T_00."SHKZG"
FROM SAPPRD."MSEG"T_00 INNER JOIN SAPPRD."MKPF"T_01 ON
                            T_01."MANDT"='900'
                            AND T_01."MANDT"=T_00."MANDT"
                            AND T_01."MBLNR"=T_00."MBLNR"
                            AND T_01."MJAHR"=T_00."MJAHR"
WHERE
T_00."MANDT"='900' AND
T_01."BUDAT" BETWEEN '20090101' AND '20090201' AND
T_00."MATNR" BETWEEN '000000000010000000' AND '000000000019999999'
WITH UR -- OPTLEVEL( 10 ) -- QUERY_DEGREE( 1 )
-- LOCATION( ZCOR0015_1 , 125 ) -- SYSTEM( SDX , SAPPRD )'

这样子速度非常快,并且执行计划也是正确的,我就纳闷了.为什么同样的程序,我提交给DB2的 与ABAP提交给DB2的相差那么大?
脑子再次迷糊.

3.反复尝试,直至最后解决

这时我想到了用HINT来解决问题,在ABAP里写HINT 强制走NESTED LOOP,指定索引

尝试了一大堆%_HINT未果,仍然找不到匹配DB2使用的HINT
就连网上比较普遍的一个HINT也无果,没有实际作用,
%_HINTS
    ADABAS   'ORDERED'
    INFORMIX 'ORDERED'
    MSSQLNT  'OPTION FORCE ORDER'                        
    DB6 '<NLJOIN><IXSCAN TABLE=''MSEG'' SAP_INDEX=''M''/>'
    DB6 '<IXSCAN TABLE=''MKPF'' SAP_INDEX=''0''/></NLJOIN>'.

我猜测或许SAP针对DB2这一块写的HINT程序,只是匹配的早前的DB2版本,在后续版本中已经废弃掉了,

(我之所以会这么猜测,是因为我跟踪了MB51的标准程序,有一个选择用过账日期开始来访问数据库,但是这个标准程序根本就不会走到那段HINT代码里,说明这个选择是不会生效的)
忽然有种被忽悠的感觉.

如果废弃了,应该有所说明,并且把新的语法公告之,但是我没有翻到相关的ABAP基于DB2 HINT的说明文档,这种undocument的方式让人十分不爽啊.
反倒是Oracle的HINT说明非常多,而且很容易使用,我在这里非常的BS IBM DB2,它让我翻看了一晚的程序,翻文档
翻到有一种想吐的感觉..
Google搜索”ABAP DB2 HINT” 这三个关键字,能搜索到的内容我全都尝试了一遍.
我已经快2年没有这样的感觉了,真恶心啊。

下面这个也是模仿着Oracle写的,没法使用,在ABAP里写Oracle的HINT就很容易,因为语法和Oracle里的基本一样,对比之下Oracle的文档就丰富许多.（黝黑18摸）
*%_HINTS DB2 'USE VALUES FOR OPTIMIZATION'
*DB2 'INDEX("MKPF" "MKPF~BUD")'.

SAP的F1 文档很烂,在这一块基本没什么说明,如此大的几家厂商,一起融洽的合作就那么困难吗??稍微麻烦的点的东西就undocument
,太让人气愤..这个时候我甚至想到把问题发msg给SAP.
多次测试之后,以瞎猫碰上死老鼠的方式…
发现USE_OPLEVEL这个参数起到了效果,原来是
WITH UR -- OPTLEVEL( 10 )
-- QUERY_DEGREE( 1 ) -- LOCATION( ZCOR0015_1 , 125 ) -- SYSTEM( SDX , SAPPRD )'
这个10级的OPTLEVEL,起到了影响.

这个参数是可以对应到DB2里的.不明白为什么SAP默认要强制送给DB2这个参数??而且SAP默认安装的时候DB2数据库的这个参数值为5 ,也不知道从哪里可以改变SAP的这个默认级别.
无论你在数据库层面更改这个值, ABAP程序送出SQL时总会是带上--
OPTLEVEL( 10 )的 ,有点类似于 基于session更改参数的概念. 不影响整体参数.

这时快要水落石出了,解决它有2种途径：

1、更改&SUBSTITUTE VALUES&：对于 SAP 内核版本
4.6，这个参数需要把 SAP 配置参数 dbs/db6/dbsl_substitute_literals
设置为 1 。这个参数使在 ABAP 语句中的所有输入值都被作为 SQL 语句文本的文字实现。就像 &SUBSTITUTE LITERALS&，DB2 优化器可以使用关于涉及表中的分布信息。这个基本同等于Oracle理解的绑定变量,如果设置了这个值的话,SAP送给DB2的SQL就是完整的,带有”字面值”的,那么我相信执行计划也一定会是正确的,不过修改这个SAP配置参数的评估已不在我目前的工作范围内了.

2、在ABAP程序里加上提示DB6 'USE_OPTLEVEL 0'.

我现在在DB2里测试下这个级别0,与10的区别

在数据库里对应这个SAP的参数是DFT_QUERYOPT

(在DB2里为0,1,2,3,5,7,9,但是不明白为什么SAP里有10级…咳咳,不知道这是怎么对应上的,反正只能靠猜测了)：
在session端更改DFT_QUERYOPT(db cfg级别), 默认为5
譬如SQL:set current query optimization = 5

数据库里最大值为9,SAP里最大值为10.最小值都为0

    Set current query optimization =9;
SELECT T_00."MATNR", T_01."MBLNR", T_00."BWART", T_01."BUDAT",
T_00."WERKS", T_00."BUKRS", T_00."MENGE", T_01."MJAHR",
T_00."AUFNR", T_00."SHKZG"
FROM SAPPRD."MSEG" T_00 INNER JOIN SAPPRD."MKPF" T_01 ON
T_01."MANDT"=?
AND T_01."MANDT"= T_00."MANDT"
AND T_01."MBLNR"= T_00."MBLNR"
AND T_01."MJAHR"= T_00."MJAHR"
WHERE
T_00."MANDT"=? AND
T_01."BUDAT" BETWEEN ? AND ? AND
T_00."MATNR" BETWEEN ? AND ?
WITH UR;
　　


以下为执行计划
烂的一塌糊涂,还是hash join, 而且也走的错误的索引,不是我想要的执行计划.
难怪ABAP送出的SQL与我相同,但是执行计划不同,导致性能并且很慢的原因就在这里.

改为0级别,再测试一次.
set current query optimization = 0;

这次的执行计划正确了！！


4.结果

在之前所创建的索引下,
套用到ABAP程序底部,加个HINT





    select
MSEG~matnr
MKPF~mblnr
MSEG~bwart
* b~aufnr
MKPF~budat
MSEG~WERKS
MSEG~bukrs
MSEG~menge
MKPF~mjahr
MSEG~aufnr
MSEG~SHKZG
INTO CORRESPONDING FIELDS OF TABLE g_t_result
from MSEG AS MSEG INNER JOIN MKPF as MKPF
on MKPF~MANDT = MSEG~MANDT
AND MKPF~MBLNR = MSEG~MBLNR
AND MKPF~MJAHR = MSEG~MJAHR
WHERE
* a~budat in s_budat AND
MKPF~BUDAT BETWEEN '20090101' AND '20090201'and
MSEG~MATNR BETWEEN '000000000010000000'and'000000000019999999'
%_HINTS DB6 'CONVERT_FAE_TO_CTE'
DB6 'USE_OPTLEVEL 0'.
　　

其实让DB2查询优化器,自己分析出来的执行计划我还是很放心的,但是在SAP里解析后的程序,添油加醋后 再给DB2, 这是完全不同的东西,很让我失望
MKPF~BUDAT BETWEEN '20090101' AND '20090105'  and

       MSEG~MATNR BETWEEN '000000000010000000' and '000000000019999999'
调整过后的速度非常快,与我预料的一样,类似上面这样的5天范围的数据查询,把数据返回到内表里,连1秒钟都不需要.
MKPF~BUDAT BETWEEN '20090101' AND '20090201'  and

       MSEG~MATNR BETWEEN '000000000010000000' and '000000000019999999'
一个月的数据返回到内表里,大致5~10秒.
MKPF~BUDAT BETWEEN '20090101' AND '20090701'  and

       MSEG~MATNR BETWEEN '000000000010000000' and '000000000019999999'
半年的数据返回到内表,不到1分钟
我本来以为数据库层面的问题已经完全解决了,但是这里又再出出了问题,其实在测试阶段出问题倒挺好的,做到全面的测试,把问题都扼杀在测试阶段..省得到时候部署到生产系统之后再出乱子.
2010-7-29,今天发现程序变得异常缓慢,与昨天的不一样,我调整了下优化级别为0,然后到DB2里看看执行计划.





    set current query optimization =0;
SELECT T_00."MATNR", T_01."MBLNR", T_00."BWART", T_01."BUDAT",
T_00."WERKS", T_00."BUKRS", T_00."MENGE", T_01."MJAHR",
T_00."AUFNR", T_00."SHKZG"
FROM SAPPRD."MSEG" T_00 INNER JOIN SAPPRD."MKPF" T_01 ON
T_01."MANDT"=?
AND T_01."MANDT"= T_00."MANDT"
AND T_01."MBLNR"= T_00."MBLNR"
AND T_01."MJAHR"= T_00."MJAHR"
WHERE
T_00."MANDT"=? AND
T_01."BUDAT" BETWEEN ? AND ? AND
T_00."MATNR" BETWEEN ? AND ?
WITH UR;
　　



真奇怪, 又用回MKPF~0索引了,真的有够烂,不稳定的执行计划是不可能放入生产环境使用的,看来还是要彻底的去解决这个问题.
假设 : 每次ABAP层面发送SQL语句给DB2的时候都是发送的字面值,而不是类似 “MANDT=?”这样的问号,那么这时数据库的CBO就会根据统计信息生成最优的执行计划。
(虽然每次发送字面值,都会让数据去解析执行计划,会造成执行计划不共享,带来一小部分CPU开销,但是我认为是值得的,因为是报表程序（OLAP应用）,正确的执行计划显得非常重要,并且这个CPU开销是十分小的,对比这个报表的消耗时间来说,基本可以忽略掉了)
如果把ABAP对SQL语句造成的影响降到最低,那么就是发送字面值了(字面值的意思就是类似 “MANDT=900”这样的程序).

以下的公式是成立的:
SQL发送字面值+正确稳定的统计信息+合理的索引 &asymp; 最优的执行计划 &asymp; 最优的速度

这时候想办法让ABAP发送字面值吧
1.   开始想到了改 SAP 配置参数 dbs/db6/dbsl_substitute_literals 设置为 1 ,这样SAP系统发送的全部SQL语句都是字面值..
可惜我没找到相关的参数,网上的资料说这个参数是4.6版本的,新版本的找不到了,但是却可以找到对应Oracle和MSSQL的参数.(再次晕倒..)

2.   在ABAP程序里增加 %_HINT来强制 程序发送字面值,
WHERE
      MKPF~BUDAT BETWEEN '20090101' AND '20090201'  and
      MSEG~MATNR BETWEEN '000000000010000000' and '000000000019999999'
    %_HINTS
      DB6 'CONVERT_FAE_TO_CTE'
      DB6 'USE_OPTLEVEL 0'
      DB2 '&SUBSTITUTE LITERALS&'
      DB6 '&SUBSTITUTE LITERALS&'
      DB2 '&SUBSTITUTE VALUES&'
      DB6 '&SUBSTITUTE VALUES&'.
经过测试,程序速度很快 10秒内数据就返回到内表了 : ) 估计发送了字面值,DB2的执行计划已经正确,但是为了严谨,我还是要验证一下.
select * from sysibmadm.TOP_DYNAMIC_SQL where stmt_text like '% FROM
"MKPF" T_00 INNER JOIN "MSEG" T_01 %' order by AVERAGE_EXECUTION_TIME_S;
经查询,字面值已经传入数据库.这样子,执行计划就是正确的了!



只要保证ABAP报表程序传入的是字面值,并且数据库端的索引是合理的,并且数据库的统计信息是正确的话, 就会拥有稳定的执行计划,以及最优的执行效率!
至此数据库层面的优化基本结束,这才是数据库真正的威力。
接着放到测试环境里测试,发现ABAP程序里还是消耗了10分钟(这时数据库的消耗只有5秒),
进行跟踪发现瓶颈在2万多数据量的内表嵌套LOOP部分.导致的性能低下。
这时与吴恒取得联系,让他给予一些关于嵌套LOOP的优化思路.然后我们这边的开发人员迅速做出了响应,不断的更改测试,更改测试,最后取得了非常好的效果!!!
以下为开发人员做的一些优化记录,经过这段时间的折腾,我们的开发人员能力也得到了提升,懂得如何撰写性能良好的程序 : )
SAP系统 ABAP 层面的性能优化。
在sap中进行abap编程时候会不可避免的遇到循环嵌套，在本着尽量少的访问数据库时，将数据多放在内存中处理，提高数据的处理速度。一般不建议使用loop嵌套循环，特别是针对数据量大时，体现的尤为明显。尽量在通过READ TABLE来读取第二张内表中的数据，再进行数据逻辑的处理，这样子在取数性能上可以大大提高效率。
下面我们就拿取一个月的数据时，所用到的两个逻辑取数耗时上的对比：
一、使用LOOP循环嵌套取数。

1、语法：





    LOOP AT g_t_out.
LOOP AT g_t_result WHERE matnr = g_t_out-matnr_i .
IF g_t_result-bwart ='101'.
g_t_out-DDCHS = g_t_out-DDCHS + g_t_result-menge.
ELSEIF g_t_result-bwart ='102'.
g_t_out-CXDDS = g_t_out-CXDDS + g_t_result-menge.
ELSEIF g_t_result-bwart ='122'.
g_t_out-GYSTH = g_t_out-GYSTH + g_t_result-menge.
……………
ENDLOOP.
modify g_t_out.
CLEAR g_t_out.
ENDLOOP.

　　

2、耗用时间（如下图）：

通过 LOOP循环嵌套取09年1月份的数据耗时为784秒
二、使用read table… with key 取数。
1、语法：






    sort g_t_out by matnr_i auart budat mblnr_i.
sort g_t_collect by matnr_i auart budat mblnr.
LOOP AT g_t_out.
read table g_t_collect With key matnr_i = g_t_out-matnr_i bwart_i ='101' BINARY SEARCH.
if sy-subrc =0.
g_t_out-DDCHS = g_t_out-DDCHS + g_t_collect-menge_i.
endif.
read table g_t_collect With key matnr_i = g_t_out-matnr_i bwart_i ='102' BINARY SEARCH.
if sy-subrc =0.
g_t_out-CXDDS = g_t_out-CXDDS + g_t_collect-menge_i.
endif.
read table g_t_collect With key matnr_i = g_t_out-matnr_i bwart_i ='122' BINARY SEARCH.
if sy-subrc =0.
g_t_out-GYSTH = g_t_out-GYSTH + g_t_collect-menge_i.
endif.
modify g_t_out.
CLEAR g_t_out.
ENDLOOP.
　　



2、耗用时间（如下图）：

使用READ TABLE取09年1月份的数据耗时为19秒
通过以上两种方法进行比较，第一种方法取同一月的数据要784秒，而第二种取同一月的数据只要19秒。显然采用第二种方法取数在效率上要远远优于第一种方法。



5.总结

这次的调优的总体难度对比以往在MIS MES上的Oracle,SQLServer来说并不算很难(单纯考虑数据库层面的对比),
但是对SAP的一些内部机制不够了解,对DB2的陌生……导致消耗了大量的时间在反复测试和阅读文档上,
途中找瓶颈点时还折腾了AIX,怀疑操作系统配置,改动了SAP参数,数据库参数,怀疑存储性能,把所有可能的环节全都怀疑了一遍, 把熬工,刘工等人也折腾得不行了,ABAP开发人员也配合一起做了大量的更改+测试..

总体来讲,调优还是需要对整体应用系统涵盖数据库,后台操作系统,存储等等..有全面的了解
只有全方位的了解应用系统,以及数据库才能快速的定位出问题根源,
否则只能是铺天盖地的寻找瓶颈点头痛医头脚痛医脚,甚至像我一样到最后依靠瞎猫碰死老鼠的招式,不过能解决问题仍然是很高兴的,
与大家分享之 : )

6.参考文档

DB2 最佳实践: 编写并调优查询语句以优化性能最佳实践
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0909querytuning/
SAP官方联机丛书
IBM DB2官方联机丛书

2010-7-28 @LDK xinyu