Skip to Content

Stored Outlines in SAP

Hi,

is anyone using stored outlines within SAP?

Would you pleas share your experience with it's implementation.

Thanks

Volker

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 25, 2009 at 01:24 PM

    Hi,

    stored outlines:

    "are used to store execution plans. Stored outlines are comparable to hints, with the difference that they work on the server and are not immediately apparent from the SQL statement."

    What you want to achieve with it is optimizer plan stability:

    i.e. you upgrade to a new ORACLE version knowing that has difficulties with the CBO and want to ensure it uses the optimal plan for the statement as before (really, under SAP environment this is the only case I could think of to use them)...

    Besides this, stored outlines may help if you have your WHERE clause using literals. But it may that the outline(s) does not cover all predicate combinations because you have some skewness in the data. Remember when you have volatile tables you want to have the CBO choose the right plan considering the data volume and table objects like indexes and such...

    If you go for outlines you would need to

    a) capture the query exactly as it is executed by the application, hints and all

    b) get it to have the plan you want in some session by some method (eg: setting session parameters, invalidating the index referenced)

    c) capturing that plan and using it.

    It sure adds to complexity as Lars said...

    bye

    yk

    Add comment
    10|10000 characters needed characters exceeded

    • >

      >... why using the "old" stored outlines?

      Hi Stefan,

      because I did not know better.

      This was the just to get information about a possible approach.

      I never noticed this other option before.

      Will look into it (and especially the possible side effects).

      Thanks for bringing this up as a possible solution.

      Will do some testing.

      Volker

  • Nov 25, 2009 at 11:49 AM

    Hi Volker,

    how is that related to "SAP on Oracle"? Can you elaborate?

    Markus

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 25, 2009 at 12:18 PM

    Hi Markus,

    I have a join that is performing badly and so far I

    was wondering, if that might be a way to push

    the join order in a way that I like to have.

    Hints are not possible, because the table itself and the

    access-routines are generated from customizing settings

    (very horrorable, because the same tables have diffrent

    names in E - Q and P systems (yes, really !),

    it is a Bank-Analyzer System and the statement involved is a

    journal table join of 8 members).

    I do not like to change Code inside a generated section of code that

    would effect a couple of other generated tables from

    which I only have problems with two.

    I do not like to change the generated code at all

    (for adding a hint, which would work).

    I do not like to change the SAP code that generates this code

    for I have no idea of side effects to other tables that SAP

    generates in that area (beside I am not able to locate this code :-).

    Faking stats did not do the trick on this one.

    The costs for both statements (the one SAP generates and

    the one I want to be executed) are exactly the same.

    I did not find a way to fake stats to change the join order.

    I think there is a bug involved, but going through the entire

    procedure and wait for a development support solution would

    require a couple of months.

    Right now I am looking for a workaround.

    So the idea is to tell the database via stored ouline

    ' Hey, if this statement comes in, do it this way '

    I never used stored outlines in Oracle (SAP or native) so far and I

    was just trying to gather some kind of information about this

    if somebody else is using this with SAP and if that is an option.

    I.e. I found out so far that one needs to enable query_rewrite to

    use this and SAP standard recommendation is to switch it off.

    But I can not calculate what this means to the system.

    Volker

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Lars,

      > Excuse me - but where is the connection between the size of your database and the time it takes to install the current software patch?

      > I know patching an Oracle instance is horrible but it's not that horrble!

      Agreed! But I'm just the guy to do, not the guy to decide, and for the guy to decide it is an argument.

      > As of now it's not forbidden - it's just untested and not recommended to use stored outlines.

      > And personally I don't think that it would be a good idea to generally promote this feature.

      > It's better to approach the product issue that causes your problems.

      Agreed! But I still claim the right to be curious 😊

      > Well, CBO merge fix 14 is current 'enough' I'd say.

      I'll give it a try and open a new call.

      Volker