今天在发现一个程序，一次要取100W多行的数据进行处理，原来取数里也除了FOR ALL ENTRIES 来出这100W多的数据来的，但现发现非常慢，主要是表中的数据也帮大，好几亿了，所以不得不进行了一些数据优化，最后是在SELECT 的最后加入：
|
1 2 3 |
select * from mseg . |
%_HINTS ORACLE
‘&max_blocking_factor 100& &max_in_blocking_factor 1000&’.
1.完整的SELECT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT ZFI_LMYJ~VBELN ZFI_LMYJ~ZVBELP ZFI_LMYJ~GMYHQ ZFI_LMYJ~SJCDL ZFI_LMYJ~SJCDJ ZFI_LMYJ~SJYHQ ZFI_LMYJ~ZYJBL ZFI_LMYJ~ZYJJE ZFI_LMYJ~LIFNR ZFI_LMYJ~ZDPBH ZFI_LMYJ~ZHTBH ZFI_LMYJ~ZJSLX INTO CORRESPONDING FIELDS OF TABLE LT_MAIN_ITEM2 FROM ZFI_LMYJ FOR ALL ENTRIES IN IT_JSRQ WHERE ZFI_LMYJ~ZDPBH = IT_JSRQ-ZDPBH AND ZFI_LMYJ~LIFNR IN S_LIFNR AND ZFI_LMYJ~ZTJSH = '' "结算单号（BBC） AND ZFI_LMYJ~VBELN IN S_VBELN %_HINTS ORACLE '&max_blocking_factor 100& &max_in_blocking_factor 1000&'. |
如不加入最后的 %_HINTS ORACLE (如是HANA数据库请换成：%_HINTS HDB) ，用ST05跟踪，你会发现生成的SQL语句，会把 IT_JSRQ-ZDPBH 中的每5个生成一个新的WHERE语句执行，而加入后，你会发现，使用的是不是1000个 ZDPBH 生成一个WHERE 语句，这样子请求SELECT的交数减少，应该能提示查询效率吧，具体的，得明天搞到生产机用大数据最进行比较一下才能知道。
ST05跟踪结果如下：
SELECT
“VBELN”,”ZVBELP”,”GMYHQ”,”SJCDL”,”SJCDJ”,”SJYHQ”,”ZYJBL”,”ZYJJE”,”LIFNR”,
“ZDPBH”,”ZHTBH”,”ZJSLX”
FROM
“ZFI_LMYJ”
WHERE
“MANDT”=:A0 AND “ZDPBH” IN
(:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14,:A15,:A16,:A17,:
A18,:A19,:A20,:A21,:A22,:A23,:A24,:A25,:A26,:A27,:A28,:A29,:A30,:A31,:A32,:A33
,:A34,:A35,:A36,:A37,:A38,:A39,:A40,:A41,:A42,:A43,:A44,:A45,:A46,:A47,:A48,:A
49,:A50,:A51,:A52,:A53,:A54,:A55,:A56,:A57,:A58,:A59,:A60,:A61,:A62,:A63,:A64,
:A65,:A66,:A67,:A68,:A69,:A70,:A71,:A72,:A73,:A74,:A75,:A76,:A77,:A78,:A79,:A8
0,:A81,:A82,:A83,:A84,:A85,:A86,:A87,:A88,:A89,:A90,:A91,:A92,:A93,:A94,:A95,:
A96,:A97,:A98,:A99,:A100,:A101,:A102,:A103,:A104,:A105,:A106,:A107,:A108,:A109
,:A110,:A111,:A112,:A113,:A114,:A115,:A116,:A117,:A118,:A119,:A120,:A121,:A122
,:A123,:A124,:A125,:A126,:A127,:A128,:A129,:A130,:A131,:A132,:A133,:A134,:A135
,:A136,:A137,:A138,:A139,:A140,:A141,:A142,:A143,:A144,:A145,:A146,:A147,:A148
,:A149,:A150,:A151,:A152,:A153,:A154,:A155,:A156,:A157,:A158,:A159,:A160,:A161
,:A162,:A163,:A164,:A165,:A166,:A167,:A168,:A169,:A170,:A171,:A172,:A173,:A174
,:A175,:A176,:A177,:A178,:A179,:A180,:A181,:A182,:A183,:A184,:A185,:A186,:A187
后面一直A1000.生成了IN 1000个。
2.相关的文档资料
可以细节的自己研究。
该语法在生成 Native SQL 时会根据内表数据将 Open SQL 拆分成多条在数据库中执行,在内表数据量较大的时候会发生性能问题.
优化 FOR ALL ENTRIES 可以在全局配置参数文件;
针对单独的程序可以使用 HINT 语句 覆盖参数文件的值。
使用 HINT 可以强行指定 FOR ALL ENTRIES 解析使用的参数：
|
1 2 3 4 5 6 |
SELECT * FROM [..] FOR ALL ENTRIES IN [..] WHERE [..] %_HINTS ORACLE '&prefer_in_itab_opt 1&&prefer_fix_blocking -1&' |
通过配置参数文件可以调整 FOR ALL ENTRIES 的性能（使用 TCODE: RZ10 RZ11 可以查看和调整SAP参数）
2.1.1). rsdb/prefer_join
使用连接(JOIN)的方式实现 FOR ALL ENTRIES, 0 – 禁用 1 – 启用
NETWEAVER 7.0 版本 DB2 和 MS SQL Sever实现了该参数,
NETWEAVER 7.1 版本加入了对 Oracle的实现
2.2.2) rsdb/prefer_union_all
( rsdb/prefer_join = 1 时,该参数无效 )
rsdb/prefer_union_all = 0 时, 条件之间用 OR 连接
rsdb/prefer_union_all = 1 时, 条件之间用 UNION ALL 连接
rsdb/prefer_union_all = 0 =>
|
1 2 3 4 5 6 7 |
SELECT ... WHERE f = itab[1]-f OR f = itab[2]-f ... OR f = itab[N]-f . |
rsdb/prefer_union_all = 1 =>
|
1 2 3 4 5 6 7 |
SELECT ... WHERE f = itab[1]-f UNION ALL SELECT ... WHERE f = itab[2]-f .... UNION ALL SELECT ... WHERE f = itab[N]-f . |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * UP TO 30 ROWS INTO TABLE lt_bkpf FROM bkpf. SELECT * INTO TABLE lt_bseg FROM bsas FOR ALL ENTRIES IN lt_bkpf WHERE bukrs = lt_bkpf-bukrs AND belnr = lt_bkpf-belnr AND gjahr = lt_bkpf-gjahr %_HINTS ORACLE '&prefer_union_all 1&&max_blocking_factor 7&' . |
2.3.3）rsdb/prefer_in_itab_opt
rsdb/prefer_in_itab_opt 该参数被设置为 1，
如果 where 条件中仅有一个字段被内表限制,则将内表的字段放到 IN 条件中。
SQL IN 条件比 OR 效率高, 启用该参数可以提高效率
|
1 2 3 4 5 |
SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f. |
会生成下面的 SQL 语句：
|
1 2 3 4 5 |
SELECT ... WHERE f IN (itab[1]-f, itab[2]-f, ..., itab[N]-f) |
Example1 – 启用 prefer_in_itab_opt:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DATA: lt_bkpf TYPE TABLE OF bkpf, lt_bseg TYPE TABLE OF bsas. DATA: lv_bkpf TYPE i, lv_bseg TYPE i. SELECT * UP TO 30 ROWS INTO TABLE lt_bkpf FROM bkpf. SELECT * INTO TABLE lt_bseg FROM bsas FOR ALL ENTRIES IN lt_bkpf WHERE belnr = lt_bkpf-belnr %_HINTS ORACLE '&prefer_in_itab_opt 1&&max_blocking_factor 11&' |
Example2 – 禁用prefer_in_itab_opt
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DATA: lt_bkpf TYPE TABLE OF bkpf, lt_bseg TYPE TABLE OF bsas. DATA: lv_bkpf TYPE i, lv_bseg TYPE i. SELECT * UP TO 30 ROWS INTO TABLE lt_bkpf FROM bkpf. SELECT * INTO TABLE lt_bseg FROM bsas FOR ALL ENTRIES IN lt_bkpf WHERE belnr = lt_bkpf-belnr %_HINTS ORACLE '&prefer_in_itab_opt 0&&max_blocking_factor 11&' . |
2.4.4) rsdb/max_blocking_factor
该参数设定每个SQL Statement 处理的内表条件的个数。
如果内表数据为 50条, rsdb/max_blocking_factor = 5 则实际生成10条 SQL Statement,每个 SQL Statement 包含5组条件。
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DATA: lt_bkpf TYPE TABLE OF bkpf, lt_bseg TYPE TABLE OF bsas. DATA: lv_bkpf TYPE i, lv_bseg TYPE i. SELECT * UP TO 30 ROWS INTO TABLE lt_bkpf FROM bkpf. SELECT * INTO TABLE lt_bseg FROM bsas FOR ALL ENTRIES IN lt_bkpf WHERE bukrs = lt_bkpf-bukrs AND belnr = lt_bkpf-belnr AND gjahr = lt_bkpf-gjahr %_HINTS ORACLE '&max_blocking_factor 7&' . |
2.5.5). rsdb/max_in_blocking_factor
同 rsdb/max_blocking_factor 类似, 该参数针对 rsdb/prefer_in_itab_opt ,即 WHERE 条件为 IN 的情况。
2.6.6). rsdb/min_blocking_factor
rsdb/prefer_fix_blocking = 1 时, 该参数生效。
2.7.7). rsdb/min_in_blocking_factor
同 rsdb/min_blocking_factor 类似, 该参数针对 rsdb/prefer_in_itab_opt,即 WHERE 条件为 IN 的情况,
2.8.8). rsdb/prefer_fix_blocking
该参数配合 rsdb/min_blocking_factor 使用, 内表中数据的条数不能被 rsdb/max_blocking_factor 整除时，
剩余数据是否均匀的生成 SQL Statment 由该参数控制。
假设内表数据为20条, rsdb/max_blocking_factor=11 , rsdb/min_blocking_factor=5
当 rsdb/prefer_fix_blocking = 0 时, 会生成 2 条SQL语句，第一条条件为10个，第二条为9个.
当 rsdb/prefer_fix_blocking = 1 时, 会生成 3 条SQL语句，第一条条件为10个，第二条和第三条为5个
Oracle在解析 SQL 时可以使用替换变量,相同的 SQL 在执行时,不需要重新解析,可以提高性能,设置该参数可以保证 SQL Statement 相同。
rsdb/prefer_fix_blocking 和 rsdb/min_in_blocking_factor 针对少量数据；
合理设置可有效利用 Oracle 的替代变量,在使用 HINT 优化大量数据时指定这两个参数的意义不大。
rsdb/max_blocking_factor 设置的过大可能会使 SQL 超长,造成程序 DUMP,故参数文件中应谨慎设置；
在 HINT 中 rsdb/max_blocking_factor 可以设置的大些,但要注意需测试出合理的值,提高性能的同时避免程序DUMP,
实际使用中 ECC5 SQL Statement中最多包含320组条件,即使 max_blocking_factor 设置为1000，
实际仍按320处簇表无法利用 max_blocking_factor参数提高性能,在 ECC5测试,取BSEG每次仅处理一条数据
