Re: Stored outlines Plans Not getting used...

From: RaviAlluru <ravi.alluru_at_gmail.com>
Date: 24 Nov 2005 09:13:56 -0800
Message-ID: <1132852436.922378.93920@g44g2000cwa.googlegroups.com>

Jonathan Lewis wrote:
> "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

Thanks Jonathan,

   Here is what I tried :

capture the outline for the actual SQL

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 = :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 no t in ( '/item/payment', '/item/payment/reversal', '/item/refund', '/item/adjustment', '/item/dispute', '/item/settlement', '/ item/writeoff' ) )

Is the actual sql ...(Note :1 and :2 , bind variables being used instead of the actual literals )

Created the the table ITEM_T on INTEGRATE203 ( as schema on the same instance as PROD ) with around 200 rows. Ran stats on the table. Created all the indexes minus the offending index.

Did an explain plan for the above sql .. It gave me the correct explain plan

i.e used the index INDEX RANGE SCAN PIN144.I_ITEM_AR_ACCOUNT_OBJ__ID

Captured the stored outline for this query...

Ran the query again and it did use the STORED OUTLINE, as the used column in USER_OUTLINES changes to USED.

Then went back to my PROD schema PIN203.

did

alter session set use_stored_outlines=TRUE

did the same query , it still gets stuck and shows the explain plan with the wrong index .

Also noticed as you had suggested in your article that user_table_name on OL$HINTS shows INTEGRATE203.ITEM_T

Did try the hack again

changed this to PIN203.ITEM_T

Tried the query again against PROD PIN203 . Still have the same issue.

Now this is what I am thinking I will do : 1. I wuill drop the offending index :I_ITEM_AR_BILL_OBJ__ID from ITEM_T on PIN203.

2. Drop any existing outlines and create a new outline from within PIN203 . It should have the correct explain plan now.

3. Try it out with the query .

4. Re create the index .

5. See if my original query still uses the explain plan from the Stored Outline.

Ours is a 2TB database and the number of rows on ITEM_T is 20 million. So the above needs an outage to try . I was wanting to avoid this.

Can you think of anything else?

I really appreciate your help.

Ravi Received on Thu Nov 24 2005 - 11:13:56 CST