SAP R/3 форум ABAP консультантов

[admin]

Note 881083 - Blocking factors on Oracle-based systems
(объясняет преобразование FOR ALL ENTRIES в параметр IN в SQL запросе)

Symptom

You require information regarding the optimal way to configure the rsdb/max_blocking_factor and rsdb/max_in_blocking_factor parameters when using an Oracle database.

Reason and Prerequisites

If you use the DBI parameters offered by SAP (rsdb/max_blocking_factor or rsdb/max_in_blocking_factor) you can ensure that the lists specified in ABAP within FOR ALL ENTRIES are distributed over several SELECT statements. If, for example, a FOR ALL ENTRIES list contains 200 entries and the blocking factor is set to 5, then instead of a statement with 200 values, the system issues 40 statements with 5 values each.

For technical reasons, no similar option exists to allow you to restrict the length of long IN lists, with the exception of FOR ALL ENTRIES expressions. If you encounter problems when working with long IN lists, you need to consider using an ABAP denomination of the lists or a FOR ALL ENTRIES expression.

Both parameters are similar in meaning. The main differences in terms of their use and effect are the following:

rsdb/max_in_blocking_factor
The rsdb/max_in_blocking_factor parameter converts the FOR ALL ENTRIES lists into statements with IN lists. It is used if the rsdb/prefer_in_itab_opt parameter is set to 1 (this is the default as of Kernel 6.x) and any time a conversion into IN lists is technically possible. An example of when it is not possible to convert to IN lists is if there are several references to the FOR ALL ENTRIES lists in the WHERE conditions.

For more information, see Note 634263.

rsdb/max_blocking_factor
The rsdb/max_blocking_factor parameter converts the FOR ALL ENTRIES lists into statements with OR operations. As a prerequisite, the rsdb/prefer_in_itab_opt parameter must be set to 0 (this is the default for kernels up to and including 4.6D), or it must not be technically possible to convert to an IN list.

Whilst up to 4.6D, it was generally only the rsdb/max_blocking_factor parameter that played a role, both parameters are relevant as of 6.x (with sdb/prefer_in_itab_opt = 1). Generally, both parameters should be set to the same value.

If you do not set these parameters explicitly, the SAP profile parameter overview (such as report RSPARAM) always indicates a value of -1 instead of the default value. However, you can use a work process trace file dev_w<num> (in transaction ST11) to determine the default value. It contains a parameter overview, which includes actual parameter values:

B dbtran INFO (init_connection '<DEFAULT>' [ORACLE:620.02]):
B max_blocking_factor = 5, max_in_blocking_factor = 5,
B min_blocking_factor = 10, min_in_blocking_factor = 5,
B prefer_union_all = 0, prefer_union_for_select_all = 0,
B prefer_fix_blocking = 0, prefer_in_itab_opt = 1,
...

For further details about the parameter settings for FOR ALL ENTRIES, see Note 48230.

Using IN lists instead of OR operations has the following advantages:

Lower memory consumption in PGA and Shared Pool (Note 869006)
Lower parsing times and less CPU consumption when parsing (Note 712624 ()
Lower costs for index accesses for longer lists (Note 750631)
Avoiding transitivity problems with views/joins in relation to OR (Note 176754 (12))

This note deals with the question regarding the best setting for the two parameters.

Solution

Setting both high and low values for the parameters has disadvantages:

High values
Since the costs for an index access increase linearly in proportion to the length of the IN lists or the number of OR concatenations (Note 750631), having too high a value runs the risk of the CBO deciding on a suboptimal Full Table Scan instead of an index access.
A high number of OR concatenations can lead to increased memory consumption, CPU consumption, and increased parsing times.

Low values
If you split a FOR ALL ENTRIES list into several individual statements, each with only a few elements, this leads to an unnecessarily large amount of communication with the database. This places a particularly high load on the network and on the Oracle client (see Note 805934).
In addition, there is an overhead in the database interface that separates the statements and then needs to reconstruct the result again.
If a FOR ALL ENTRIES statement is executed at the same time by many work processes, this may result in wait situations on the Oracle Library cache (for example, "cursor: pin S", "library cache pin"). The problem is reduced with higher blocking values and the resulting lower number of executed SQL statements.

In extreme cases, a significant increase in blocking factors can reduce the runtime of certain transactions to less than half. Nevertheless, in other places increased values can lead to the disadvantages described above. It is therefore difficult to make blanket recommendations for the optimal parameter settings.

We recommend that you set default values of 5 for OLTP parametrized databases, and 1 for OLAP parametrized databases. If problems occur in a mixed environment (such as if you use DP and PP/DS in an APO environment), refer to Note 458077. These values ensure optimal performance in the majority of customer systems. We do not recommend reducing these values further (if this is at all possible). If, in spite of the low default values, there are still incorrect CBO decisions, you can use other measures (hints, adjusting statistical values in accordance with Note 724545) to correct this.

If you suspect that the performance of some transactions, or the entire system, is suffering due to the values for the blocking factors being too low, and thereby the increased number of SQL statements that are issued, you can increase the parameters in stages (for example 5 -> 20 -> 50 -> 100) and check the effects of the changes in a non-production environment. If you notice negative side effects, there are the following possible solutions:

Incorrect CBO decision
Optimize the access as within a normal SQL optimization (Note 766349), for example by using hints, adjusting statistics or changing the source code of the application.
As a test, set the OPTIMIZER_INDEX_CACHING Oracle parameter to 50 (if this is not already the case due the standard recommendation for the parameter) to significantly reduce the costs for accessing a long IN list (Note 750631) and to reduce the likelihood of incorrect CBO decisions. In this context, it is also conceivable that rsdb/max_in_blocking_factor will be selected higher than rsdb/max_blocking_factor (for example, twice as high).
If this only affects individual SQL statements of a low blocking factor and if there are negative side effects of raising the blocking factors globally, you can raise the blocking factor individually for this statement by using a DBI hint in accordance with Note 772497, for example:

%_HINTS ORACLE '&max_in_blocking_factor 50&'.
Increased resource consumption
If statements with OR concatenations suffer from increased parsing times, increased memory or CPU consumption, you should reduce the rsdb/max_blocking_factor parameter again (whilst rsdb/max_in_blocking_factor can keep the higher value).