Stored outlines Plans Not getting used...

From: RaviAlluru <ravi.alluru_at_gmail.com>
Date: 17 Nov 2005 13:17:35 -0800
Message-ID: <1132262255.843344.55710@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 Received on Thu Nov 17 2005 - 15:17:35 CST