Re: Different Plans for Literal Vs Bind Variables
- From: "Karen Morton" <oraseeker@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, ian@xxxxxxxxxxxxxxxxx
- Date: Sun, 11 Jan 2009 12:17:26 -0800
Ian,
From what the 10053 shows, the cost is virtually identical for the IFFS in
each case. For the "good" plan, it is 3.03 and the "bad" plan is 3.07. The
very slight difference is in the cost adjustment for cpu usage (225140 vs
585001). In the binds plan, the cpu usage is estimated to be more than
double that of the other plan, but still, the cost was effected so slightly
that it's virtually not a difference.
The two plans differ in a significant way that I see that hasn't been
mentioned. The bad plan doesn't view merge for the SELECT MAX() portion of
the source view. So, what ends up happening is that over 2 million rows end
up getting grabbed and hashed into memory. Well, not really, since they
don't fit in memory. So, a lot of time is spent doing direct path reads and
writes moving the hash table parts in and out of memory to complete. All
that work was wasted because it gets thrown away in the end. The good plan
appears to have transformed the query such that it didn't need to resolve
that SELECT MAX() portion independently and that saved the day. I wonder if
the reason this happened was due to some limitation of the optimizer to
transform the query properly due to the bind variable usage. It seems
likely, but I can't confirm that theory (maybe someone else on the list
can).
To take the analysis a bit further though, what were the costs of the
indexes that were chosen? Given what you've shown so far, the other indexes
that were used for the binds plan must've had a cost of less than 3 making
the optimizer think they were better choices. I think you need to focus on
what was chosen in the bad plan as your next step and determine why it was
costed less than the IFFS with a cost of 3.07. Somehow the optimizer
thought what it chose was better....why?
Also, what is the estimated cardinality that is calculated for the
PS_DEPT_TBL in each 10053? Would you share the SINGLE TABLE ACCESS section
of the 10053 for each for the PS_DEPT_TBL? I'm wondering if the cardinality
estimate for the table might be a clue. Did both 10053's show the same
estimated cardinality value for the table or did the presence of the bind
variable cause a different estimate which effected things?
Finally, can you change the view source? I've seen wonders worked with
changing that SELECT MAX syntax to use an analytic and reformulating the
query a bit differently. If you can change the view source, you may be able
to eliminate the problems the optimizer is having in a very easy way. Just
change the view source to remove the problem areas and replace them with
syntax that the optimizer can better work with. I can provide an example if
you need it if changing the view is an option.
Karen
Karen Morton
Method R Corporation
karen.morton@xxxxxxxxxxxx
------------------------------
Date: Sat, 10 Jan 2009 22:34:28 -0800
Subject: Re: Different Plans for Literal Vs Bind Variables
From: Ian MacGregor <ian@xxxxxxxxxxxxxxxxx>
The definition of IDX$_7D3D0001 is
... etc... <rest of post clipped>
Other related posts:
