Thursday, September 24, 2009

What is Bind Variable Peeking?

Bind Variable peeking is when optimizer looks into the literal behind the behind variable. When a query with bind variable is submitted to Oracle, and the parsed representation of that query is already present in the library cache, then optimzer simply uses the already present plan, regardless of the value behind the variable.

Now with literals optimizer could make much more informed decision about the best way to access the data (i.e the best execution plan). For example, if historgram is present on the column of the table, then the optimizer already knows as how frequently or rarily a value is present in that column, and with literal in query, optimizer could use the best access path, but in case of bind variable it cannot do that because it doesn't know the value. So in case of bind variable it uses bind variable peeking to know the value. But when it uses the bind variable peeking and gets an access path, it always uses the same plan for the subsequent similar queries with bind variables up to 10g, though it has grown more intelligent in the 11g.

But as a general tip, you might want to use bind variables in the queries of OLTP and literals in the queries of data warehouse. Because in OLTP, more often than not, the parsing time is as crucial as execution time, while in DW the parsing time is very little as compared to the execution time given the high volume of data.

1 comment:

Anonymous
said...

Birmingham Web Design Birmingham web designing is a Birmingham based blog which has been designed by Suhanasoft, and Indian web Design Company. The company provides website design, web development and search Engine Optimization services at affordable prices. Our creative design and developing secure website has made us popular among our clients. We have developed several websites from demark.

Collaborate14

Presenting at

My Book

Speaking at:

It's All About Passion

Oracle ACE. Exadata Certified Specialist, Oracle Blogger, Oracle DBA, OCP in 11g DBA and 10g DBA and working in Oracle products since 2001. Speaker at conferences like Oracle Open World, UKOUG, Collaborate, Insync etc around the globe. Currently working with Pythian Australia as Senior Oracle DBA.