Skip to Content

I got some nice feedback on my first blog about the oracle optimizer behavior in a special case. So i decided to start a tiny series about common issues with the oracle optimizer in a SAP environment. I don’t have a specific topic order. I just start with the topics that come to my mind and what i have seen over years very regularly.

Introduction

The first part of this series will cover a pretty common topic called “IN-LIST”. SAP tried to hide the database layer (open SQL) completely from its ABAP layer, which makes sense (of course) regarding the reusability of code for different database platforms. But if you hide a layer behind an interface (SAP DBSL), it is pretty easy to run in some issues without knowing it.

I don’t focus on specific database code like adding specific database hints to ABAP code or using native SQL. The guys who are using these technics are mostly aware of what is happening behind.

The IN-LISTs

Basically an IN-LIST is an expression in the WHERE clause of a SQL. You can specify multiple values and query a table (and even more, but this is not used in a SAP environment).

Let’s assume the following: You want to query an employee table and search for several personnel numbers. You can write the query in two ways (for the simplest way):

  1. SELECT <col> from <tab> where <col> = <p-number> or <col> = <p-number> or <col> = <p-number> ……
  2. SELECT <col> from <tab> where <col> in (<p-number>, <p-number>, <p-number>)

As you can see the second example is way shorter and become even more common, if you have more column values.

Now think about a SAP internal table which is used to query a database table. The SAP DBSL uses the IN-LISTs for FOR ALL ENTRIES lists in ABAP.

As an example regarding the 2 SELECTs above it looks like this in ABAP:

SELECT <col> from <tab> into <itab2> FOR ALL ENTRIES IN <itab> where <col> = <itab>-<col>

SAP DBSL

rsdb/max_in_blocking_factor (= number of values in IN-LIST by each execution)

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.

The default value of rsdb/max_in_blocking_factor is 5 (as far as i can remember). This means, that every database SQL contains 5 values in the IN-LIST, if you execute a SELECT in ABAP code with FOR AL ENTRIES. The database SQL is executed several times (with 5 IN-LIST values) in a row until all internal table entries are processed, if your internal ABAP table has more than 5 values.

Now you maybe think “Cool, so we can increase the parameter rsdb/max_in_blocking_factor and run less SQL statements on the database with more IN-LIST values”. Yes you can do that of course (i have used this approach a few times too), but you need to know the limitations and impact on the Oracle cost based optimizer in such scenarios. However you can also run into issues even with the default value of 5, but i get into that details later on.

The optimizer on IN-LISTs

This part of the blog tries to demonstrate the limits of the CBO regarding IN-LISTs (especially in a SAP environment with disabled CBO  enhancements). The demos are run on an Oracle 11.2.0.3 database. The optimizer will transfer the IN-LIST into (multiple) “OR” concatenation internally, but this should be a side note only.

SQL> create table ZTEST as select trunc(dbms_random.value(1,6)) col
           from dba_objects where rownum <=100;
SQL> exec dbms_stats.gather_table_stats(NULL,'ZTEST');

Now we have a table ZTEST and a column col with 5 nearly equally distributed values over 100 rows. To simplify the example i will disregard “special configurations” like histograms, etc.

Basic algebra:

  • How many rows do we expect by running a query on table ZTEST with “col IN (1)”?
    • … i would say 20 (= 100 * (1/5) * 1) .
  • How many rows do we expect by running a query on table ZTEST with “col IN (1,2)”?
    • … i would say 40 (= 100 * (1/5) * 2).
  • How many rows do we expect by running a query on table ZTEST with “col IN (1,2,3)”?
    • … i would say 60 (= 100 * (1/5) * 3).
  • How many rows do we expect by running a query on table ZTEST with “col IN (1,1,1)”?
    • … i would say 20 (= 100 * (1/5) * 1).
  • How many rows do we expect by running a query on table ZTEST with “col IN (1,1,2)”?
    • … i would say 40 (= 100 * (1/5) * 2).

Let’s verify our calculations with the calculations of the CBO.

SQL> select col from ZTEST where col in(1);

Bildschirmfoto 2012-08-02 um 14.46.00.png

SQL> select col from ZTEST where col in(1,2);

Bildschirmfoto 2012-08-02 um 14.48.24.png

SQL> select col from ZTEST where col in(1,2,3);

Bildschirmfoto 2012-08-02 um 14.49.19.png

SQL> select col from ZTEST where col in(1,1,1);

Bildschirmfoto 2012-08-02 um 14.50.31.png

SQL> select col from ZTEST where col in(1,1,2);

Bildschirmfoto 2012-08-02 um 14.51.23.png

… so the oracle database optimizer is really clever, isn’t it? The CBO calculates the same results (column E-Rows) as we expected it. It is even clever enough to remove the duplicates in the IN-LIST and estimate the correct amount of rows.

However SQLs are not executed this way (with literals) by SAP DBSL (default). If you execute an open SQL through SAP DBSL, it translates this SQL with bind variables. So let’s do the same exercise with bind variables and disabled “by SAP default” CBO enhancements (like bind variable peeking).

SQL> var val1 number;
SQL> var val2 number;
SQL> var val3 number;
SQL> var val4 number;
SQL> var val5 number;
SQL> exec :val1 := 1;
SQL> exec :val2 := 2;
SQL> exec :val3 := 3;
SQL> exec :val4 := 1;
SQL> exec :val5 := 1;
SQL> alter session set "_optim_peek_user_binds"=FALSE;
SQL> select col from ZTEST where col in(:val1);

Bildschirmfoto 2012-08-02 um 14.46.00.png

SQL> select col from ZTEST where col in(:val1,:val2);

Bildschirmfoto 2012-08-02 um 14.48.24.png    

SQL> select col from ZTEST where col in(:val1,:val2,:val3);

Bildschirmfoto 2012-08-02 um 14.49.19.png

SQL> select col from ZTEST where col in(:val1,:val4,:val5);

Bildschirmfoto 2012-08-02 um 14.49.19.png

SQL> select col from ZTEST where col in(:val1,:val4,:val2);

Bildschirmfoto 2012-08-02 um 14.49.19.png

… so the oracle database optimizer isn’t that clever anymore. The SAP default settings for oracle databases limit some CBO features (for other comprehensible reasons). In this case the optimizer can not look “behind” the bind variables and filter duplicates. This leads to a “over estimation” and can result in bad execution plans (wrong join order or join method are common issues then).

After demonstrating the CBO behavior there are some basic recommendations by using “FOR ALL ENTRIES”:

  1. Remove internal table duplicates (as far as possible)
  2. Use literals (by ABAP hint “substitute literals”), if the bad calculation is based on the values itself (and if the values do not change often)

Summary

There are several cases like out-of-range values, unequal distributed values (and so on), which can lead to a wrong calculation (by design), but this blog should focus on the basics in a SAP environment with some common recommendations.

If you have any further questions – please feel free to ask or in case of performance issues get in contact directly.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Volker Borowski

    Interesting.

    I found Oracle being more in favor of inlists than concatenations since Oracle 10.

    Had a nice proof in this discussion:

    http://scn.sap.com/message/10781559#10781559

    If parsing several ORs on the same column, the Optimizer is going for an internal inlist iteration, even if given as ORs in the SQL.

    In recent plans on FAE execution I always found inlists, as long as only a single column was taken from the internal table.

    When comparing more keys, there is no was out to use ORs in a way like:

    (k1=:a1 AND k2 =:a2) or

    (k1=:a3 AND k2=:a4) … repeated as defined by blocking.

    And even in this case, I think the optimizer is doing a union instead of concat.

    Need to take a close look when having this next time.

    Anyway, I am curious, how this will carry on in part two, *go go go* 😎

    Volker

    (0) 
    1. Stefan Koehler Post author

      Hey Volker,

      let’s see what the next topic will be 🙂

      Regarding your question about the INLIST ITERATOR. This has nothing to do with how the optimizer handles such an IN-LIST in the WHERE clause internally (keyword “Query Transformation”).

      Just a quick proof on that simple example from above.

      SQL> alter session set events ‘10053 trace name context forever, level 1’;

      SQL> select col from ZTEST where col in(1,1,2);

      Content from the CBO trace file:

      ————

      Final query after transformations:******* UNPARSED QUERY IS *******

      SELECT “ZTEST”.”COL” “COL” FROM “SYS”.”ZTEST” “ZTEST” WHERE “ZTEST”.”COL”=1 OR “ZTEST”.”COL”=1 OR “ZTEST”.”COL”=2

      kkoqbc: optimizing query block SEL$1 (#0)

      ————

      However the INLIST ITERATOR is used by index access (not by table). Tom Kyte has shown a pretty nice example of the improvement by INLIST ITERATOR option:

      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8906695863428#40860044986274

      Regards

      Stefan

      (0) 

Leave a Reply