Does Oracle mean the default execution plan when parsing a prepared statement?

Question

According to this Oracle documentation, I can assume that the optimizer defers hard analysis and does not create an execution plan until the first time that the prepared statement runs:

“The answer is a phenomenon called peeking binding. Previously, when you ran this query with the value of the binding variable set to“ NY ”, the optimizer had to perform hard parsing for the first time, and at the same time it would look into the binding variable to see what value was assigned to her. "

But when executing EXPLAIN PLAN for a prepared statement with binding parameters, we get a completed plan. On its website, Markus Winand says that:

"When using the binding parameters, the optimizer does not have specific values ​​available to determine their frequency. Then it simply takes an equal distribution and always gets the same estimates of the number of rows and the value. At the end, it will always choose the same execution plan."

Which one is correct? Whether an execution plan is created when the instruction is prepared using a model with a uniform distribution value, or is a tight parsing analysis delayed until the first execution time.

Answer 1

The first click of the binding actually occurs on the first run. Optimization of the plan is delayed, which does not happen at the preparation stage. And later another click may occur. Typically for VARCHAR2, when you bind two radically different values ​​(that is, the length of the first value is 1 byte and later 10 bytes), the optimizer looks again and can create a new plan. In Oracle 12, it has expanded even further; it has adaptive federation methods. Therefore, the optimizer offers NESTED LOOPs, but when it is actually executed after many other lines than are evaluated, it immediately switches to a HASH connection. This is not like adaptive cursor allocation when you need to make a mistake first to create a new execution plan.

. , . , . SOFT-. , .

, , . , .

V $SQL_BIND_CAPTURE.

Answer 2

, ; ! , . . Oracle ( , , ..), .

. . . Bind peeking . . peeking bind "". , Oracle 11g, .

- . ( opt = > " 1" , .

: " Oracle ?" . , ( , ), (Oracle - , a = b b = c, Oracle a = c) , , . - . - , Oracle . .

. - , . , , bind, , , . , , ( hash_value). , Oracle ( ), , - . , , , - ( Oracle , ), , . , . Oracle 12c, . 12 Oracle Adaptive Execution - , . , , , - . -, sql-. , Oracle SQL-: -)

Answer 3

Tom Kyte peeking , . 11g , (, , , , ).

, . , . Oracle Database 11g , .

Answer 4

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

In particular, for JDBC (Thin Driver): when using PreparedStatements, nothing is created before the execution stage.

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