Re: Stored outlines Plans Not getting used...

"RaviAlluru" <ravi.alluru_at_gmail.com> wrote in message news:1132262255.843344.55710_at_f14g2000cwb.googlegroups.com...
> Hello All,
> We are having a problem, in the problem that Stored Outlines are not
> getting used.
> We have a query generated from a third party tool, on which we seem to
> have no control to pass hints. So we decided to embark on Stored
> Outlines, and were of the opinion that once we create these outlines
> (with the proper Index hints ), every time a query similar query is
> executed , the explain plan from the Stored outline should be used. But
> this doesnt seem to be the case.
> here is what were are trying :
>
> alter session set create_stored_outlines=TRUE
>
> alter session SET use_stored_outlines = TRUE
>
> create or replace outline ADM30203
> on
> select /*+ index(item_t,I_ITEM_AR_ACCOUNT_OBJ__ID) */
> sum( item_t.due ),
> sum( item_t.item_total ), sum( item_t.adjusted ),
> sum( item_t.disputed), sum( item_t.recvd ),
> sum( item_t.transfered ),
> sum( item_t.writeoff )
> from item_t
> where (item_t.ar_account_obj_ID0 = :1
> and item_t.ar_account_obj_DB = 9 )
> and (item_t.ar_bill_obj_ID0 = :2 and item_t.ar_bill_obj_DB = 0 )
> and ( item_t.poid_type not in
> ( '/item/payment', '/item/payment/reversal', '/item/refund',
> '/item/adjustment', '/item/dispute',
> '/item/settlement', '/item/writeoff' ) )
>
> Explain with the hint is :
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 10
>
> SORT AGGREGATE 1 55
> TABLE ACCESS BY INDEX ROWID PIN203.ITEM_T 1 55 10
>
> INDEX RANGE SCAN PIN203.I_ITEM_AR_ACCOUNT_OBJ__ID 62 4
>
>
>
> ALTER SESSION SET CREATE_STORED_OUTLINES=FALSE
>
> ALTER SESSION SET CURSOR_SHARING=SIMILAR
>
> When I do a
> select * from user_outlines I do get
>
> ADM30203 DEFAULT UNUSED
> 11/17/2005 11:44:36 AM 9.2.0.4.0
> select /*+ index(ite C742E7CE6FD914CF25C1732AF19118AA
>
> But when I do try and execute a query with the following enabled:
>
> alter session SET use_stored_outlines = TRUE
>
> ALTER SESSION SET CURSOR_SHARING=SIMILAR
>
>
> select
> sum( item_t.due ),
> sum( item_t.item_total ), sum( item_t.adjusted ),
> sum( item_t.disputed), sum( item_t.recvd ),
> sum( item_t.transfered ),
> sum( item_t.writeoff )
> from item_t
> where (item_t.ar_account_obj_ID0 = 19556536
> and item_t.ar_account_obj_DB = 9 )
> and (item_t.ar_bill_obj_ID0 = 0 and item_t.ar_bill_obj_DB = 0 )
> and ( item_t.poid_type not in
> ( '/item/payment', '/item/payment/reversal', '/item/refund',
> '/item/adjustment', '/item/dispute',
> '/item/settlement', '/item/writeoff' ) )
>
>
> I get the following explain plan and the query is slower as expected ,
> as it doesnt use the same plan as the one in the stored outline.
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 3
>
> SORT AGGREGATE 1 55
> TABLE ACCESS BY INDEX ROWID PIN203.ITEM_T 1 55 3
>
> INDEX RANGE SCAN PIN203.I_ITEM_AR_BILL_OBJ__ID 9 4
>
>
>
> If I look at the USED column in the USER_OUTLINES view it still shows
> up as being UNUSED.
>
> My question is how do we force all similar queries, to use the PLAN
> from the STORED OUTLINE ?
>
> Am I missing something?
>
>
> Any help is greatly appreciated.
>
> Thanks
>
> Ravi Alluru
>

You've created an outline on a piece of text that includes the string

    '/*+ index(item_t,I_ITEM_AR_ACCOUNT_OBJ__ID) */' when you run the statement that does NOT include this string, Oracle CANNOT match the strings.

You stored text has to be a very close text match for the string that arrives from the application; the only variations allowed are in case and spaces.

Unless you are licensed to use the outline manager, you have to do something like the following:

    capture the outline for the actual SQL

    create a schema that holds tables and indexes     of the same names, and only the indexes you     want used

    Fake some data and/or stats into the schema     so that the plan you want on production is the     plan that happens automatically on the fake schema

    recompile the stored outline from within that schema.

Now, when you use stored outlines, the original SQL, when run against the production schema, will use the plan you have just regenerated.

There are a couple of articles on my website about stored outlines that you might want to check - but the above is the basic requirement.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005