FW: Different Plans for Literal Vs Bind Variables

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 12 Jan 2009 11:17:55 -0500
re-snipped smaller to fit on list limit

 

  _____  

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Sunday, January 11, 2009 6:19 PM
To: 'oraseeker@xxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'; 'ian@xxxxxxxxxxxxxxxxx'
Subject: RE: Different Plans for Literal Vs Bind Variables

 

What Karen said about it missing the optimization, COMBINED with the fact
that it is binds versus literals makes me think that the opportunity given
by the still hard wired A.TREE_NAME = 'DEPT_SECURITY' is not surfacing
within the first 2000 permutations.

 

As a cheap and easy iso-functional re-write of the view, the first thing I
would try is to force the projection of PSTREENODE via PSAPSTREENODE at the
outset via the rownum virtual table trick. The useful part of doing it this
way, though quite possibly technically inferior to Karen's suggestion, is
that it will be transparently obvious to "Peoplesoft" aka Oracle support
that the view is in fact isofunctional done this way.

 

The other possibility to try to kick the optimizer in the shins to do the
right thing without changing the view at all is adding a histogram on
A.TREE_NAME (since that is hardwired into the view, it should help with the
cardinality).

 

The view reformulation would be:

 

create or replace force view "SYSADM"."PS_DEPT_SCRTY_VW" ("SET_ID", "EFFDT",
"DEPTID", "DESCR", "TREE_NODE_NUM",  "TREE_NODE_NUM_END") AS

select b.setid, a.effdt, b.deptid, b.descr,
a.tree_node_num,a.tree_node_num_end

from

   (select rownum, tree_node, setid, effdt, tree_node_num,tree_node_num_end

       from pstreenode

       where tree_name = 'DEPT_SECURITY'

       order by tree_node, setid, effdt) a,

   ps_dept_tbl b

where a.tree_node = b.deptid

    and a.setid       = b.setid

    and b.effdt = (select max(effdt) from ps_dept_tbl c where c.dept_id =
b.deptid and c.setid = b.setid and c.effdt <= a.effdt);

 

While the ordering in the virtual table may seem gratuitous, it should be
effective in requiring only one pass if you end up with a nested loop
against the IDX$_7D3D0001 index. Easy to see whether it is faster for your
data.

 

I hope this helps. I'm not sure whether the permutations parameter survives
as an _parm, in which case it still might be interesting to see if the
better plan is just not coming up as a choice in the first 2000. Of source
if you see that plan evaluated and rejected in the 10053 trace, that is a
moot point.

 

Clearly, your original point, that it is definitely generating plans and
your second point, that a different cost is assigned to one of the row
sources is definitely true. Karen's point that it is a very tiny difference
might mean missing the hugely cheaper view merge is the real problem.

 

Wolfgang moved on to getting additional details from the 10053, which might
point you to a very good rewrite of the view or indicate more about how the
bind variable version is missing the huge opportunity to execute more
cheaply.

 

This is all stuff that ain spose' ta work dat way. If it gets it right with
literals it ought to get it right with bind variables (aside from the peek
problem, which you ruled out). Of course this may be something Oracle has
already weeded out by 11.1.0.7 which would reduce the value of tracking it
down precisely. I haven't been watching quite that closely.

 

Good luck!

 

mwf

 

<snip>
FW: Different Plans for Literal Vs Bind Variables

Other related posts:
