Stored Outlines in SAP

[volker_borowski]

Hi,

is anyone using stored outlines within SAP?

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

Thanks

Volker

[Former Member · Accepted solution]

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

[volker_borowski · Accepted solution]

>

> If you go for outlines you would need to
> 
> a) capture the query exactly as it is executed by the application, hints and all
> yk

Hi YK,

this would be a major "gotcha".

My statement has bind variables and I am not able to force the plan

to the correct one by invalidating an index.

I either need a hint (which will not be in the generated code) or

change the sequence of the tables in the from clause.

So in both ways the stored outline would refer to a statement,

that is literally diffrent.

So if I got you correctly, I will not be able to like the plan of a statement to a diffrent one?

The change I'd have to do would be

:

FROM

table1 T_00,

table2 T_01,

table3 T_02,

table4 T_03,

table5 T_04,

table6 T_05,

table7 T_06,

table8 T_07,

WHERE

:

to

:

FROM

table2 T_01,

table3 T_02,

table1 T_00,

table4 T_03,

table5 T_04,

table6 T_05,

table7 T_06,

table8 T_07,

WHERE

:

Now as for the SQL Syntax, this make absolutely no diffrence.

But the plan changes to effectively 50% less consistent gets (~12000 -> ~6500)

and the total optimizer costs for both plans are identical.

So I would like to link the plan for statement 2 to the literal statement 1.

Volker

[Former Member · Accepted solution]

>

> >

> > If you go for outlines you would need to
> > 
> > a) capture the query exactly as it is executed by the application, hints and all
> > yk

> http://forums.sdn.sap.com/post!reply.jspa?messageID=8458029

> Hi YK,

>

> this would be a major "gotcha".

> My statement has bind variables and I am not able to force the plan

> to the correct one by invalidating an index.

> I either need a hint (which will not be in the generated code) or

> change the sequence of the tables in the from clause.

>

> So in both ways the stored outline would refer to a statement,

> that is literally diffrent.

>

> So if I got you correctly, I will not be able to like the plan of a statement to a diffrent one?

no - it works the other way round:

Statement A -> worse plan

you would make it to use the more efficient plan (i.e. using session parameters and such)

capture that plan and use it on Statement A.

>

>

> The change I'd have to do would be

>

> :

> FROM

> table1 T_00,

> table2 T_01,

> table3 T_02,

> table4 T_03,

> table5 T_04,

> table6 T_05,

> table7 T_06,

> table8 T_07,

> WHERE

> :

>

> to

>

> :

> FROM

> table2 T_01,

> table3 T_02,

> table1 T_00,

> table4 T_03,

> table5 T_04,

> table6 T_05,

> table7 T_06,

> table8 T_07,

> WHERE

> :

>

> Now as for the SQL Syntax, this make absolutely no diffrence.

> But the plan changes to effectively 50% less consistent gets (~12000 -> ~6500)

> and the total optimizer costs for both plans are identical.

>

> So I would like to link the plan for statement 2 to the literal statement 1.

If you have control over the statement check out the ORDERED hint and forget stored outlines

SELECT /*+ ORDERED */ ....FROM ....WHERE

tables get joined in the order of the from clause.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50601

But same applies: Don't try to be more clever than the CBO (99% of the time he is smarter than you)....

>

> Volker

[volker_borowski · Accepted solution]

Hi YK,

still not sure if I got you right.

I either have to change the sequence of the tables (without any hint !) and the plan changes to the "better" one.

Or I provide a hint /*+ leading(T_01) */ which is the leading table in both, the "good" and the "bad" plan,

where in "good" T_00 is joined on position 2 or 3 and in "bad" T_00 is joined as the last table.

I can do this for ST05 explains and SQLPLUS testruns.

The application generates the sequence T_00 ... T_07 and has no hint which I can in no way influence.

So since I can in no way force the "original" statement to use the "good" plan,

(tried real and faked stats, rebuilding indexes) I have no option to "capture" the plan for this exact statement for later use.

I am only able to get the "correct" plan for a statement that is litterally diffrent from the original,

as I have to provide a hint or change the table sequence.

So if it is a requirement, to get the "good" plan with the "original" statement,

I'd consider my question answered, with a result, that stored outlines provide no solution to this specific problem.

Did I get this right?

Volker

[stefan_koehler · Accepted solution]

Hello Volker,

> Did I get this right?

Just to put another beast into the cage ... why using the "old" stored outlines?

If you are on Oracle 10g .. you can solve your issue with "SQL Profiles".

Just check out this blog (and the "more advanced" part of the script in Take II). It is exactly what you are looking for.

[http://kerryosborne.oracle-guy.com/2009/07/how-to-attach-a-sql-profile-to-a-different-statement/]

Regards

Stefan

[volker_borowski · Accepted solution]

>

>... 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

[volker_borowski]

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

[lbreddemann]

> 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.

Well, you should open a support call anyhow for this.

In many cases you might already get a workaround that does not require stored outlines or code changes.

> 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.

That's true - query rewrite needs to be enabled for that.

And we don't want to see this, as it adds another level of complexity to the query optimization process which is usually not required.

Therefore, really, go for a support message in this case.

regards,

Lars

[volker_borowski]

Hi Lars,

I already did log a call for that.

It already has been on hold for a while, as we

have been asked to apply the latest Optimizer Merge Fix,

before anyone wanted to take a look (which I can fully understand).

Now applying a patch to this 33TB beast requires some

overhead at out site (planning downtimes an going through E-Q-P and so on).

To make it short: meanwhile we have applied #14,

but even before it made it's way to production, #15 came out

(and my call has been closed automaticly meanwhile )

I will continue the "official" way of course,

but look for other solutions in addition, as I can not say, if I will

ever be able to claim the error in a "up-to-date" patched environment.

Note 105047 has nothing on it, where as some Features are clearly

marked as "not allowed".

BTW, we use several features in this system (with approval from SAP!)

that have been approved in Note 105047 later (somebody has to go first

(like hashpartitioning and function based indexes)

So I was just trying to find out, if there is someone, who is already

doing it (using stored outlines) even may be on pilot project level.

Volker

[lbreddemann]

> have been asked to apply the latest Optimizer Merge Fix,

> before anyone wanted to take a look (which I can fully understand).

Right choice!

> Now applying a patch to this 33TB beast requires some

> overhead at out site (planning downtimes an going through E-Q-P and so on).

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!

> I will continue the "official" way of course,

> but look for other solutions in addition, as I can not say, if I will

> ever be able to claim the error in a "up-to-date" patched environment.

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

Based on that it should be possible to at least figure out where the CBO is taking the wrong turn and whether this would be different in a newer patch or not.

To prevent messages from beeing closed automatically you can update them every now and then to let us know that you're still working on it at your side.

> Note 105047 has nothing on it, where as some Features are clearly

> marked as "not allowed".

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.

> BTW, we use several features in this system (with approval from SAP!)

> that have been approved in Note 105047 later (somebody has to go first

> (like hashpartitioning and function based indexes)

Well, FBIs don't need a change in parameters and hash partitioning doesn't require this as well.

These are both features that can be easily controlled and that don't impact the overall system.

> So I was just trying to find out, if there is someone, who is already

> doing it (using stored outlines) even may be on pilot project level.

Sure - and it might be an option to approach this problem.

But I still would propose to discuss this with the SAP Oracle support.

regards,

Lars

[volker_borowski]

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

[markus_doehr2]

Hi Volker,

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

Markus