Re: Bind Variable Peeking

[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_at_gmail.com> 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
--
http://www.freelists.org/webpage/oracle-l