FOR ALL ENTRIES的效率问题
FOR ALL ENTRIES vs DB2 JOIN
手机的危害作文All abap programers and most of the dba's that support abap programmers are familiar with the abap clau "for all entries". Most of the web pages I visited recently, discuss 3 major drawbacks of the "for all entries" clau:
1. duplicate rows are automatically removed
2. if the itab ud in the clau is empty , all the rows in the source table will be lected .
3. performance degradation when using the clau on big tables.
In this post I'd like to shed some light on the third issue. Specifically i'll discuss the u of the "for all entries" clau as a means to join tables in the abap code instead of in db2.
Say for example you have the following abap code:
Select * from mara
For all entries in itab
Where matnr = itab-matnr.
If the actual source of the material list (reprented here by itab) is actually another databa table, like:
lect matnr from mg
into corresponding fields of table itab
where ?
Then you could have ud one sql statement that joins both tables.
Select t1.*
From mara t1, mg t2
Where t1.matnr = t2.matnr
And T2?.
So what are the drawbacks of using the "for all entires" instead of a join ?
At run time , in order to fulfill the "for all entries " request, the abap engine will generate veral sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine us (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement veral times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste becau it may lead to re-reading of data pages.
returing to the above example , lets say that our itab contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time.
Select * from mara
Where matnr in ( ...) 草麻黄
Db2 will be able to perform this sql statement cheaply all 50 times, using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will e that some of the data pages, especially the root and middle-tire index pages have been re-read each execution.
Even though db2 has mechanisms like buffer pools and quential detection to try to minimize the i/o cost of such cas, tho mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory. Had you coded the join, db2 would have known that you actually need 500 rows from mara, it would have been able to u other access methods, and potentially consume less getpages i/o and cpu.
In other words , when you u the "for all entries " clau instead of coding a join , you are depriving the databa of important information needed to lect the best access pat
h for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will e a simple statement that us an index , he is less likely to realize that this statement is executed in a loop unnecessarily.
In conclusion I suggest to "think twice" before using the "for all entries" clau and to evaluate the u of databa views as a means to:
a. simplify sql
b. simplify abap code
c. get around open sql limitations.
Omer Brandis
DB2 DBA & SAP Basis professional (and all around nice guy)
il
另外，附上NOTE 48230
Summary
微信批量加好友
Symptom
Performance problems with the open SQL statement "SELECT ... FOR ALL ENTRIES ...".
Other terms
FOR_ALL_ENTRIES
Reason and Prerequisites
The open SQL statement "SELECT ... FOR ALL ENTRIES ..." is an ABAP-specific enhancement of the SQL standard. This variant of the SELECT statement allows the ABAP programmer to join an internal program table with one or veral databa tables. (For a detailed description of that statement type plea refer to the corresponding ABAP documentation.)
Since there is no analogous statement in the SQL standard, the open SQL statement has to be mapped from the databa interface of the ABAP environment to one or veral mantically equivalent SELECT statements which can be procesd by the DB platform. Several profile parameters allow a definition of how the databa interface should carry out this mapping with regard to the databa. This note describes the parameters that can be ud to control the "SELECT ... FOR ALL ENTRIES" statement and their effect.
脚骨拐突出Solution
The parameters mentioned in this note have considerable effects on most of the critical databa commands and influence the performance of the whole system to a great extent. For this reason, before changing the parameters described in this note, a detailed problem analysis by experienced SAP consultants or the support team is required. Plea note in particular that changing the parameters may often solve a local performance problem but it may also cau a still bigger problem to occur at another place. For this reason, prior to changing the profile parameters - which has a global effect
on all statements - you should check first whether the performance problem might be caud by one or two positions in the corresponding application which can be corrected by a local change of the critical SQL statements.
The following profile parameters are available:
rsdb/prefer_join (ab Relea 7.0)
If you t this parameter to "1" the SELECT ... FOR ALL ENTRIES is implemented using a join. Note that this variant is only supported by the DB6 (DB2 UDB) and MS SQL Server databa platforms.
rsdb/prefer_union_all
You can override this parameter using rsdb/prefer_join = 1. The following remarks relate to rsdb/prefer_join = 0.
Setting this parameter to "1" generates a linking of entire statements with UNION; tting it to "0" generates an OR link of conditions in the WHERE clau. Each of the linked partial statements/conditions reprents an entry of the input table [itab].
For Example:
The open SQL statement
SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f.
is mapped to an SQL statement which is consistent with the standard:
rsdb/prefer_union_all = 0
绘本我爸爸=>
SELECT ... WHERE f = itab[1]-f
OR f = itab[2]-f
...
OR f = itab[N]-f
rsdb/prefer_union_all = 1
=>
SELECT ... WHERE f = itab[1]-f
UNION ALL SELECT ... WHERE f = itab[2]-f
....
UNION ALL SELECT ... WHERE f = itab[N]-f
Where N is the number of rows in itab, and itab[i]-f is the value of
field f in the i-th table row. 扁头综合症
rsdb/prefer_in_itab_opt
If this parameter is t to "1", a statement where only one field in the WHERE clau depends on the converted internal table is reflected by a statement with an IN clau. However, this is only possible if the field reference and the WHERE condition are simple enough: in esntial the field reference must be a not negated EQ condition.
For Example:
If parameter rsdb/prefer_in_itab_opt is t to "1", the open SQL
SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f.
is mapped to the following SQL statement:
SELECT ... WHERE f IN (itab[1]-f, itab[2]-f, ..., itab[N]-f)
rsdb/max_blocking_factor
This parameter specifies an upper limit for the number of entries taken in from [itab] to be procesd in one statement. This means that if the internal table specified in the FOR ALL ENTRIES clau contains more than rsdb/max_blocking_factor rows, the open SQL statement is split into veral statements for the databa the results of which are collected in the DB interface and then returned as an overall result to the ABAP program. For an internal table with N rows
N / "rsdb/max_blocking_factor" + 1
individual SELECT statements are issued for the databa. However, this parameter has no effect on the translation to IN (...) (for prefer_in_itab_opt).
Additionally the technical maximum blocking factor is calculated for each statement,
so no limits of the databa system are exceeded. If the limit of the blocking factor is lower than max_blocking_factor, the limit is ud implicitely.
rsdb/max_in_blocking_factor
This parameter, analogously to rsdb/max_blocking_factor, gives the upper limit for the number of entries to be procesd from [itab] if the concrete statement is reflected on an IN clau (e prefer_in_itab_opt).
Analogously to rsdb/max_blocking_factor also the limit of the blocking factor is ud instead of rsdb/max_in_blocking_factor, if otherwi the upper limits of the databa system would be exceeded.
rsdb/prefer_fix_blocking
If the number of entries in [itab] is not divisible by max_blocking_factor, less entries (conditions) are allocated to the last statement which has been generated for processing t
he FOR ALL ENTRIES statement. The result is a new statement.
If the same FOR ALL ENTRIES statement is executed very frequently with a different number of entries in the input table [itab], different statements are created up to the maximum of max_blocking_factor statements.
This can be avoided by the above profile parameter.
If this parameter is t to "1", at most two statements of different length are generated. This is achieved by repeating the last value in the input table as if [itab] has been padded to the blocking factor ([itab] is not really modified).
rsdb/min_blocking_factor
If this parameter is t to a value larger than "0" AND if rsdb/prefer_fix_blocking is t, 2 different blocking factors are ud: a smaller (min_blocking_factor) and a larger factor (max_blocking_factor).
However, the min_blocking_factor is only ud if there are only a few entries in [itab]: A little simplified, if the following applies: "Entries [itab] < max_blocking_factor / 2"
rsdb/min_in_blocking_factor
This parameter works in conjunction with rsdb/min_blocking_factor, for the ca that the addition FOR ALL ENTRIES has been implemented with an IN clau (e prefer_in_itab_opt).
Control over FOR ALL ENTRIES Hints
Under the heading Databa Interface Hints, Note 129385 describes the options you have for influencing the databa interface by entering hints. The hints are evaluated in the databa interface itlf and are not pasd on to the databa.
Starting with kernel Relea 4.6B all the above mentioned FOR ALL ENTRIES parameters can be t via such a hint for a single statement. In the example: 激励方案模板
SELECT * FROM [..] FOR ALL ENTRIES IN [..] WHERE [..]
%_HINTS ORACLE '&prefer_in_itab_opt 1&&prefer_fix_blocking -1&'.
年轻的英文This way, the boolean parameter 'prefer_in_itab_opt' is explictly t and the boolean parameter 'prefer_fix_blocking' is t to its default value.
FOR ALL ENTRIES hints, like hints are generally only ud as a a corrective device in emergency situations; Note 129385 goes into this. The hints described here should only be ud with careful consideration.
