When would bind peeking be done for queries that vary only in values used in the WHERE
clause?
A.
When the column used in the WHERE clause has evenly distributed data and histogram exists
on that column.
B.
When the column used in the WHERE clause has evenly distributed data and index exists on
that column.
C.
When the column used in the WHERE clause has non uniform distribution of data, uses a bind
variable, and no histogram exists for the column.
D.
When the column used in the WHERE clause has non uniform distribution of data and
histogram exists for the column.
Explanation:
Anwer is C
C seems correct for me to
Errata.
B seems correct.
https://books.google.be/books?id=9bYYAAAAQBAJ&pg=PA406&lpg=PA406&dq=bind+peeking+uniform+distribution&source=bl&ots=2ZiBHv3v5V&sig=RpeJgi9MeppyIIk6INnTsbN11dE&hl=fr&sa=X&ei=QpWzVIKEIcj2UrbjgNAF&ved=0CC8Q6AEwAg#v=onepage&q=bind%20peeking%20uniform%20distribution&f=false
Previously, when a SQL statement was first parsed with a provided bind variable, the database kept its execution plan for the lifetime. And although bind peeking worked incredibly well for evenly distributed values, it was not quite effective at handling skewed data. Adaptive Cursor Sharing (ACS) introduced in Oracle Database 11g Release 2, is designed specifically to address this problem by keeping several plans for the same statement with different binds. This allows you to pick execution plans based on the actual bind values. ACS is enabled by default and can’t be disabled, remaining completely transparent to end user and developer.
C is not correct.
Oracle 11gr2 have something called “adaptive cursor sharing”, which will cause the statement to be re-parsed if the bind variable values have different characteristics. To do this, ACS and bind aware cursors rely on histograms. So in case of skewed data and without histogram Oracle cannot choose the best plan with bind variable
D correct.
I also believe B should be correct answer
“Bind” peeking happens during the hard parse, when there’s s “Bind” variable! Only the answer C mentions the bind variable (although that’s not the only factor). NONE of the other answer mention anything about bind variables and if in any of those other situation you don’t have a bind variable in the predicate, no bind peeking occurs!
Agree, C is correct
I would choose C. The only answer where is stressed – query uses a bind
variable
In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
hey there and thank you for your info – I have certainly picked up anything new from right here. I did however expertise several technical points using this web site, since I experienced to reload the website lots of times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I am complaining, but sluggish loading instances times will often affect your placement in google and can damage your high-quality score if advertising and marketing with Adwords. Well I’m adding this RSS to my email and could look out for much more of your respective exciting content. Ensure that you update this again soon..
http://www.justmommies.com/users/louise3t-863729
Appreciate you sharing, great post. Really Great.
rca ieftin online http://goo.gl/6GqA7S
Tengo He leído un particular escribir-up en el en su blog . Estás un gran
http://www.opinionesdating.com/meetic-opiniones/
I loved your blog post.Thanks Again. Great.
Thank you ever so for you blog article.Really thank you! Keep writing.