Re: Different Plans for Literal Vs Bind Variables

From: Karen Morton <oraseeker_at_gmail.com>
Date: Mon, 12 Jan 2009 15:55:59 -0800
Message-ID: <6ab80b720901121555y51acd16dx46b5b8070c4babf3_at_mail.gmail.com>



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_at_slac.stanford.edu>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.

>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 12 2009 - 17:55:59 CST

Original text of this message