这时我想到了用 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 ;

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

在之前所创建的索引下 ,

套用到 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 发送字面值 + 正确稳定的统计信息 + 合理的索引 ≈ 最优的执行计划 ≈ 最优的速度

这时候想办法让 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.

    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.

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

这次的调优的总体难度对比以往在 MIS MES 上的 Oracle,SQLServer 来说并不算很难 ( 单纯考虑数据库层面的对比 ),

但是对 SAP 的一些内部机制不够了解 , 对 DB2 的陌生 …… 导致消耗了大量的时间在反复测试和阅读文档上 ,

途中找瓶颈点时还折腾了 AIX, 怀疑操作系统配置 , 改动了 SAP 参数 , 数据库参数 , 怀疑存储性能 , 把所有可能的环节全都怀疑了一遍 , 把熬工 , 刘工等人也折腾得不行了 ,ABAP 开发人员也配合一起做了大量的更改 + 测试 ..

总体来讲 , 调优还是需要对整体应用系统涵盖数据库 , 后台操作系统 , 存储等等 .. 有全面的了解

只有全方位的了解应用系统 , 以及数据库 才能快速的 定位出问题根源 ,

否则只能是铺天盖地的寻找瓶颈点 头痛医头 脚痛医脚 , 甚至像我一样到最后依靠瞎猫碰死老鼠的招式 , 不过能解决问题仍然是很高兴的 ,

与大家分享之 : )

DB2 最佳实践 : 编写并调优查询语句以优化性能最佳实践