Re: Bind Variable Peeking
- From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Nov 2006 23:55:16 -0500
[repost due to over-quote]
If Jonathan reads your question than he would probably give you the
answer right away...
But I'm a mere mortal so just few tips in the order of precedence I
would look at them:
- how do you know that bind variable peeking happened?
- compare 10053 trace
- bind variable peeking can happens even without histograms (example I
posted a while ago -
http://blog.oracloid.com/2006/07/bind-variable-peeking-with-no-histograms)
- other things might be relevant (cursor_sharing not exact, for example)
Hope this can get you started.
On 11/21/06, Ray Feighery <rjfeighery@xxxxxxxxx> wrote:
9.2.0.6 <http://9.2.0.6>
Solaris 8
Bind Variable peeking is supposed to look inside the bind variables when
the query is first run (hard parsed). Yet when I run a query with
exactly the same values I get different execution paths between the
literal and bind variable statements.
This is after the shared pool has been flushed (tkprof confirms a
library cache miss). The key difference in the execution plans is that
with literal values the optimizer can resolve the inlist to access
("X"."PARENT_ID"=(-1) OR "X"."PARENT_ID"=21971987), but the bind
variable version is split into 50 OR statements.
I've analyzed the table and there shouldn't be any histograms. Plans are
generated from v$sql_plan.
Any ideas why there is a difference? It causes a huge difference in time
(from 0.68s to 9.49s).
I "solved" the problem using a stored outline, but I still do not
understand why a different execution plan is generated between bind
variables and literals.
--
Best regards,
Alex Gorbachev
The Pythian Group
Sr. Oracle DBA
http://www.pythian.com/blogs/author/alex/
http://blog.oracloid.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
