Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Stored Outlines in SAP

Hi,

is anyone using stored outlines within SAP?

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

Thanks

Volker

Former Member replied

>

> >

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question