Re: Different Plans for Literal Vs Bind Variables

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: