Skip to Content

When trying to improve the performance of their programs ABAP developers have a difficult relationship with database hints: they usually go from never to often and afterwards to never again. This post is to try to bring those in the third phase to a forth use-them-when-appropriate phase.

 

The first never phase is caused by sheer ignorance. Junior abapers often don’t even know what an explain plan is, let alone a database hint. Ergo, they never use them.

 

The second is a often phase, or maybe a too often phase. As abapers begin to see that sometimes the database optimizer does not behave exactly as they expect, database hints are an obvious and easy solution, and it is often not easy to resist the temptation to force a badly behaved optimizer to work as we want it to. After all, the programmer should be in charge, right?

 

The third phase is when the developer realizes that by blindly adding hints and therefore trying to outsmart the optimizer, he will often make more bad than good. In his classic book +Economics in One Lesson +Henry Hazlitt says that “economics is the science of seeing general consequences”, and I would argue that applies perfectly to performance optimization. The problem, of course, is that these general consequences are often quite hard to anticipate: you try to improve for a specific situation and you end up making it worse in other cases. The natural reaction? To be afraid, to be very afraid of database hints, and so go back to never using them. The most experienced abapers usually recommend that database hints should only be used as a last resort (which is a sensible recommendation), and the wisest among us understand this basically as a +never use it +recommendation.

 

 

So should database hints ever be used? Well, yes, if you understand exactly what you are doing. Lately I have had to solve two similar performance problems, and I believe they are cases where hints are a good solution.

 

The first case was a select to a Z table (the real select was a bit more complicated, but let’s keep it simple here):

    SELECT matnr

    INTO   lt_matnr

    FROM   ztable

    WHERE  aufnr = p_aufnr

    AND    charg = ' '.

The primary key for ZTABLE is order number and item (AUFNR and POSNR), and there is a secondary index by batch (CHARG). For this query the optimizer chooses the index by CHARG because the batch is very selective (even more selective than the order number). The problem is that the batch is very selective except when we are looking for rows with no batch (as is the case here). A little over 6% of the rows have no batch, so AUFNR would be much more selective. Database hints allow us to easily force Oracle not to use the index by batch in this case.

    SELECT matnr

    INTO   lt_matnr

    FROM   ztable

    WHERE  aufnr = p_aufnr

    AND    charg = ' '

*   do not use index by batch because it is bad for this case (charg = ' ')

    %_HINTS ORACLE ‘NO_INDEX(“&TABLE&” “ZTABLEBAT”)’.</p></pre><p>Notice that we are not telling Oracle what to do, but rather what not to do. This is often better since it still gives Oracle some freedom to choose the best plan. In this case Oracle chooses the primary key (AUFNR + POSNR), which is a pretty good choice.</p><p> </p><p> </p><p>Our second problem was similar. This time, however, it was already coded with a dynamic where clause so that if batch is empty then it is not considered. Here is (a simplified part of) the code:</p><pre>    IF NOT <charg> IS INITIAL.<p>      l_where = ‘charg = <charg>’.</p><p>      APPEND l_where TO lt_where.</p><p>    ENDIF.</p><p> </p><p>    SELECT </p><p>      INTO TABLE lt_mseg</p><p>      FROM mseg </p><p>     WHERE werks    = <werks></p><p>       AND matnr    = <matnr></p><p>       AND (lt_where).</p></pre><p>Table MSEG has a Z index by CHARG. Again, MSEG has lots of entries with empty CHARG (for non-batch managed materials), but here we were never adding CHARG = ‘ ‘ to the where clause. So what is the catch? For non-batch managed materials +with split valuation +then field CHARG will end up with the valuation type (BWTAR), and that is also a very unselective value. The solution here was to add the same hint as before, but only when we are dealing with non batch managed materials.</p><pre>    IF NOT <charg> IS INITIAL.<p>      l_where = ‘charg = <charg>’.</p><p>      APPEND l_where TO lt_where.</p><p>    ENDIF.</p><p> </p><p>    IF <xchpf> IS INITIAL.</p><p>      l_hints = ‘NO_INDEX(“&TABLE&” “MSEGZ01″)’.

    ENDIF.

 

    SELECT Again, we are telling Oracle not to use a certain path, but only in the situations where we are sure that that would be a bad choice.

 

 

Quick conclusions then: (1) while database hints should be used with care and by experienced abapers only, they are not necessarily the root of all evil; (2) instead of forcing the path to use it is often safer to force not to use a certain path; and (3) if the hint only applies in certain cases then they can be added dynamically as shown in the last example.</p>

To report this post you need to login first.

3 Comments

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

  1. Hermann Gahm
    Hi Rui,

    first of all, i like seeing you blogging on these topics. Please continue.

    I totally agree on the phases since i see them exactly like you described quite often.

    One question regarding your examples: Did you consider histograms? Would they have worked in these cases?  If they would work they would probably be more elegant than hints.

    To answer your questions:

    Yes, it is not easy to resist the temptations to use hints. Specific problems can so easily be “fixed” and one gets easily good results.

    Especially from a suport perspective they are even more temtating since one can quickly “solve” an issue. But the additional maintenance effort should not be neglected (but that is usually not the problem of the support…).

    A hint is a decision what should be done or what should not be done. It forces the optimizer to do or not to do something (whenever technical possible). Is that decision that i make still a good decision in 6 months (when data volume, data distribution changes)? Is that decision still good after an upgrade (SAP or database version)? (And maybe is that my problem then,
    will i still be in charge? 😉

    With a certain level of knowledge i see hints used quite often. And sometimes the people even
    know what they do 😉

    Kind regards,

    Hermann

    (0) 
      1. Hermann Gahm
        Hi Rui,

        yes i expected more reactions on the topic as well… .

        It should not take much longer to calculate histograms. With the basis guys: Ask them for some proof 😉

        I agree that your hints will not be dangerous or stop working (if the index name does not change).  You named the most important part: the documentation. I think it should be documented what has been changed and why and what circumstances led to the decision for the change. With that information it is easy to understand later… and change it again (if the circumstances changed) or stick with it (if the situation is still the same).

        Kind regards,

        Hermann

        (0) 

Leave a Reply