Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Aman.

Asked: October 15, 2007 - 11:18 am UTC

Last updated: October 15, 2007 - 2:18 pm UTC

Version: 10.2.0.1

Viewed 1000+ times

You Asked

Dear sir,
I am not able to understand bind variable peeking.It must have some benefit but still I amnot able to get that.Why this feature was introduced?Can you explain the concept with an example?
Thanks and best regards,
Aman....

and Tom said...

You should read these:

http://asktom.oracle.com/Misc/tuning-with-sqltracetrue.html
http://asktom.oracle.com/Misc/sqltracetrue-part-two.html

Basically if you have skewed data and executed a query like:

select * from t where column = ?

and we did not peek, we would use "default guesses" to estimate the cardinality. This often led to a full scan, and developers would throw the /*+ index */ hint in there and say "stupid cbo, have to hint it".

The reason they hinted - because at the point in the application where they did that query - they used inputs that were highly selective. Only we didn't know that. So, enter bind peeking - now we'd use the index.

But - sometimes, someone would hard parse the query with non-selective inputs, and then the 'bad stuff' happens. The wrong plan gets in place. And - if you don't know about bind peeking - it gets very difficult to diagnose this.

In 11g - they've added "intelligent cursor sharing" and bind peeking.
http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07493

In the next issue of Oracle Magazine, I'll cover this in more detail (this new feature)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions