This section requires some basic knowledge of tuning, as it pertains to bind peeking and Adaptive Cursor Sharing. Before the 10g Version of Oracle Database, there were performance issues related to bind peeking. The query optimizer takes a quick look (peek) at user-defined bind variables the first time they appear in a cursor. A bind variable is a substitution variable instead of a literal—in this example as A: instead of the literal 100. That peek may or may not be the best value to base the execution plan of a query on. The next time the optimizer encounters that same cursor, no more peeking takes place no matter the bind value, because the cursor is shared among all the queries for the same information.
Why wouldn't the first peek be the best one to use? It depends on the mathematical distribution of the data. For example, a table with three column values—1, 10, 100, contains 245 rows, 2 rows and 6 rows of each value respectively. There is a larger...
