Re: Different Plans for Literal Vs Bind Variables

Karen, thank you for taking the time to analyze the problem.  It is not
often that I learn something new, and  I¹m sure your efforts educated many.


On 1/12/09 3:55 PM, "Karen Morton" <oraseeker@xxxxxxxxx> wrote:

> The bind plan is using the default selectivity of 5%.  In the first query, the
> optimizer computes it will return basically all the rows in PS_DEPT_TBL.  So,
> when the indexes are costed, you can note how the cost_cpu has increased from
> 225140 (in the good plan) to 585001.  That accounts for the 3.03 to 3.07
> difference between plans.  I find it a bit odd, since an IFFS will hit all the
> index blocks in either case, but the optimizer adds some extra cpu weighting
> to the plan where it thinks you only need a small portion of the rows.  I
> suppose it makes sense if you think about how if you really only needed a few
> rows, it would make sense to use a range scan vs a index fast full scan so I
> can see why the cost may be higher (even by a bit) in this case.
> 
> The bottom-line is that the optimizer uses a 5% selectivity for the bind plan
> and the actual computed selectivity based on density for the other plan.  The
> 5% estimate causes the IFFS on IDX$$_7D3D0001 plan to be costed slightly
> higher and therefore leads to a choice to not use it.
> 
> The problem appears two-fold and the bind variable is the root of both issues:
> 1) The bind variable version is using a default selectivity value (5%)
> 2) The bind variable version doesn't transform the SELECT MAX subquery
> 
> An outline may work to intercept the particular query and allow you to store
> the "good" plan in the outline so that it gets used all the time.  Or, you may
> have to find a way to edit the view source to allow for more effective
> optimizer query transformation.  However, since this all started with the
> difference between the plan using literal values vs. the plan using binds, is
> it possible to simply change the code for this query to always use literals?
> It would depend on that query's execution frequency and the number of distinct
> values anticipated for the date bind variable as to whether or not that would
> be a good idea.  Or, maybe you could change the end query to use a hint to
> help force the view to merge or to set CARDINALITY for the PS_DEPT_TBL to
> "force" the plan choice you want.
> 
> Perhaps someone else has another idea for you but it appears to me that you've
> got to either adjust the source or adjust the actual end query.
> 
> Karen
> 
> Karen Morton
> Method R Corporation
> 
> 
> -----------------
> 
> 
> On Mon, Jan 12, 2009 at 11:31 AM, Ian MacGregor <ian@xxxxxxxxxxxxxxxxx> wrote:
>> Here are the Single  Table Access Paths for PS_DEPT_TBL
>> SINGLE TABLE ACCESS PATH
>> ...
>>   Table: PS_DEPT_TBL  Alias: C
>>     Card: Original: 875  Rounded: 875  Computed: 874.50  Non Adjusted: 874.50
>> ...
>>   Access Path: index (index (FFS))
>>     Index: IDX$$_7D3D0001
>>     resc_io: 3.00  resc_cpu: 225140
>>     ix_sel: 0.0000e+00  ix_sel_with_filters: 1
>>   Access Path: index (FFS)
>>     Cost:  3.03  Resp: 3.03  Degree: 1
>>       Cost_io: 3.00  Cost_cpu: 225140
>>       Resp_io: 3.00  Resp_cpu: 225140
>> ==================================================================
>> Binds 
>> 
>> SINGLE TABLE ACCESS PATH
>>   Table: PS_DEPT_TBL  Alias: C
>>     Card: Original: 875  Rounded: 44  Computed: 43.75  Non Adjusted: 43.75
>>   Access Path: index (index (FFS))
>>     Index: IDX$$_7D3D0001
>>     resc_io: 3.00  resc_cpu: 585001
>>     ix_sel: 0.0000e+00  ix_sel_with_filters: 1
>>   Access Path: index (FFS)
>>     Cost:  3.07  Resp: 3.07  Degree: 1
>>       Cost_io: 3.00  Cost_cpu: 585001
>>       Resp_io: 3.00  Resp_cpu: 585001
>>   ...
>> The cardinality of each table is indeed different.  Changing this particular
>> view is it is part of how PeopleSoft performs fine-grained access control.
>> 
>

Other related posts: