Does Oracle chose a default execution plan when parsing a prepared statement?

Question

According to this Oracle documentation, I can assume that the Optimizer postpones the hard parse and it doesn't generate an execution plan until the first time a prepared statement is executed:

"The answer is a phenomenon called bind peeking. Earlier, when you ran that query with the bind variable value set to 'NY', the optimizer had to do a hard parse for the first time and while doing so it peeked at the bind variable to see what value had been assigned to it."

But when executing an EXPLAIN PLAN for a prepared statement with bind parameters, we get an executed plan. On his site, Markus Winand says that:

"When using bind parameters, the optimizer has no concrete values available to determine their frequency. It then just assumes an equal distribution and always gets the same row count estimates and cost values. In the end, it will always select the same execution plan."

Which one is true? Does an execution plan get generated when the statement is prepared using an evenly distribution value model, or is the hard parsing postponed until the first execution time.

Answer 1

This discussion misses a very important point about bind variables, parsing and bind peeking; and this is Histograms! Bind variables only becomes an issue when the column in question have histograms. Without histograms there is no need to peek at the value. Oracle have no information then about the distribution of the data, and will only use pure math (distinct values, number of null values, number of rows etc) to find the selectivity of the filter in question.

Binds and histograms are logical opposites. You use bind variables to get one execution plan for all your queries. You use histograms to get different execution plans for different search values. Bind peeking tried to overcome this issue. But it does not do a very good job at it. Many people have actually characterized the bind peeking feature as "a bug". Adaptive Cursor Sharing that comes around in Oracle 11g does a better job of solving this.

Actually I see to many histograms around. I usually disable histograms (method opt=>'for all columns size 1', and only create them when I truly need them.

And then to the original question: "Does Oracle choose a default execution plan when parsing a prepared statement?" Parsing is not one activity. Parsing involves syntax checking, semantic analysis (does the tables and columns exist, do you have access to the tables), query rewrite (Oracle might rewrite the query in a better way - for instance - if we use the filters a=b and b=c, then Oracle can add the filter a=c), and of course finding an execution plan. We actually differ between different types of parsing - soft parse and hard parse. Hard parsing is where Oracle also have to create the execution plan for the query. This is a very costly activity.

Back to the question. The parsing doesn't really care if you are using bind variables or not. The difference is that if you use bind, you probably only have to do a soft parse. Using bind variables your query will look the same every time you run it (therefor getting the same hash_value). When you run a query Oracle will check (in the library cache) to see if there all ready is an execution plan for your query. This is not a default plan, but a plan that allready exist because someone else has executed the same query (and made Oracle do a hard parse generating an execution plan for this query) and the execution plan hasn't aged out of the cache yet. This is not a default plan. It's just the plan the optimizer at parse time considered the best choice for your query. When you come to Oracle 12c it actually gets even more complicated. In 12 Oracle have Adaptive Execution plans - this means that the execution plan has an alternative. It can start out with a nested loop, and if it realize that it got the cardinality estimates wrong it can switch to a hash join in the middle of the execution of the query. It also have something called adaptive statistics and sql plan directives. All to make the optimizer and Oracle to make better choises when running your SQLs :-)

Answer 2

The first bind peek actually happens at the first execution. The plan optimization is deferred it doesn't happen at the prepare phase. And later on another bind peek might happen. Typically for VARCHAR2 when you bind two radically different values (i. e. in length of first value 1 byte and later 10 bytes) the optimizer peeks again and it might produce a new plan. In Oracle 12 it's extended even more, it has adaptive join methods. So optimizer suggest NESTED LOOPs but when it's actually being executed after many more rows than estimated comes it switches to HASH join immediately. It's not like adaptive cursor sharing where you need to make a mistake first to produce new execution plan.

Also one very important thing to prepared statements. Since these just re-executes the same cursor as is created with the first execution. They will always execute the same plan, there cannot be any adaptation. For adaptation and alternative execution plans at least SOFT parse must occur. So if the plan is aged out from shared pool or invalidated for any reason.

Explain plan is not cursor it will never respect bind variables. It's only display cursor where you can see bind variable information.

You can find actual information about captured bind values in V$SQL_BIND_CAPTURE.

Answer 3

According to Tom Kyte bind peeking takes place at the hard-parse stage, which chimes with the first quote in your post. In 11g the optimizer is even able to come up with different plans for different bind ranges, which directly contradicts the second quote (although to be fair he is talking about bind variables and not peeking specifically).

The query in the application uses bind values that drive it to one plan or the other consistently. It is only when the plan flip-flops between two radically different execution paths, and for some segment of users, that you have a really bad plan. In such cases, Oracle Database 11g might be the right answer for you, because it accommodates multiple plans.

Answer 4

In general, Oracle behavior starting from 11g is best described by adaptive cursor sharing (see http://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#BGBJGDJE)

For JDBC (Thin Driver) specifically: When using PreparedStatements, no plan is generated before the execution step.

See the following example:

String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "adaptiveCSTest";
((OracleConnection) conn).setEndToEndMetrics(metrics, (short) 0);

String getObjectNames = "select object_name from key.objects where object_type=?";

PreparedStatement objectNamesStmt = conn.prepareStatement(getObjectNames);
// module set, but statement not parsed 

objectNamesStmt.setString(1, "CLUSTER");
// same state

ResultSet rset1 = objectNamesStmt.executeQuery();
// statement parsed and executed