| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored outlines Plans Not getting used...
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 )
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 )
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 )
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
Jonathan Lewis wrote:
> "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
> >
> >
>
>
>
> 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.
>
>
> > >
>
>
>
>
 |
 |