Re: Stored outlines Plans Not getting used...

"RaviAlluru" <ravi.alluru_at_gmail.com> wrote in message news:1132616639.493200.70500_at_g49g2000cwa.googlegroups.com...
> Thanks Jonathan,
> For your response. This is what I tried...
>
> In a schema "pin144" , with a siumilar structure minus the offending
> index did the following :
>
>
> alter session set create_stored_outlines = true
>
> create or replace outline ADM30203
> on
> 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' ) )
>
> Notice the above statment has no hints as you suggested.
>
> alter session set create_stored_outlines = false
>
> select name, category, used, sql_text
> from USER_outLines
>
> ADM30203 DEFAULT UNUSED is the result.
>
> On this same schema I try ..
>
> alter session set use_storEd_outlines=TRUE
>
> 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' ) )
>
> The following is the explain plan for the above statement...
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 2
>
> SORT AGGREGATE 1 54
> TABLE ACCESS BY INDEX ROWID PIN144.ITEM_T 1 54 2
>
> INDEX RANGE SCAN PIN144.I_ITEM_AR_ACCOUNT_OBJ__ID 187
> 1
>
>
> and when I go back and query the user_outlines table ..
>
> select name, category, used
> from USER_outLines
>
> ADM30203 DEFAULT USED is the result.
>
> So all is good up to now.
>
> Next I export the ol$% tables from the outln schema on this instance by
> doing the following:
>
> It exports the 3 tables ...
>
> exp outln/outln_at_stmaurd2 tables='OL$' 'OL$NODES' 'OL$HINTS'
> log=outln_stmaurd2.log
>
> and I import this back into the production instance by doing this ...
>
> imp outln/outln_at_stmaurp2 file=expdat.dmp log=imp_outln_stmaurp2
> ignore=y full=y
>
> It imports them without errors...
>
> I log in as outln on my production instance and do the following ...
>
> update ol$hints set user_table_name='PIN203.ITEM_T'
> where user_table_name='PIN144.ITEM_T'
>
> and
>
> update ol$ set creator='PIN203'
>
> where PIN203 is our PRODUCTION schema and PIN144 is our TEST schema...
>
> Then I log in as PIN203 schema on PRODUCTION and do the following ..
>
>
>
> 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' ) )
>
> It sill uses the old explain plan ..
>
> 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
>
> whereas it does show
>
> select name, category, used
> from USER_outLines
>
> ADM30203 DEFAULT USED is the result. for the above wuery.
>
>
> So even though now we have a user outline stored in production without
> the hint and using the proper index, a query with the same sql doesnt
> use the correct explain plan.
>
>
> Is there anytrhing else I am missing. An oracle parameter or something
> that will enable the use of Stored Outlines?
>
> Thanks again.
>
> Ravi
>
>

My original post said:

> capture the outline for the actual SQL
>

You haven't done this, you've created a stored outline from an input string. Under any circumstances this could be sufficiently different from the incoming text that the match does not occur.

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

Since you've moved to a separate database, it would have been best to operate under a schema with the same name - I note you have updated the ol$hints table directly: never assume that anyone else's code will work if you hack their data outside of their control.

Most significantly, I notice you have done the following:

> alter session set use_stored_outlines=TRUE
> alter session set cursor_sharing=similar

You said nothing about cursor_sharing in your explanation of creating the stored outline. Look at user_outlines, I believe you will find that the stored SQL has literal values in it (as a result of the 'create or replace'). If you CAPTURE (i.e. set create_stored_outlines to true) an outline for the statement on production, you will find that at run time the text has been changed to include bind variables like :"SYS_B_0", so the captured text and the created text do not match.

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