Solution to the bind peeking problem in oracle

Using the Bind Variable can reduce SQL PARSE, but using the Bind Variable has a bad point:
 
Incorrect execution plan may be used for a column with skew. Before Oracle 9i, if the WHERE condition contains all
 
When binding variables, you can only use fixed selective parameters to determine the execution plan.
 
= Operation and> = the operation selectivity is 5%, and the range scan selectivity is 25%. The default value may generate bad executions.
 
Line plan. So a new technology emerged in Oracle 9i, bind peeking. What is bind peeking?
 
? When the SQL statement is executed for the first time, the optimizer determines the execution plan based on the bound variables (if a bar chart exists)
 
. Bind peeking is performed only when the SQL is executed HARD PARSE for the first time and the second time
 
If this SQL statement is called, BIND PEEKING is not performed again. In this case, there is another risk.
 
Column skew is very powerful, so using bind peeking is not safe, because different parameters can only be substituted.
 
Taking the execution plan for the first execution, the execution plan is just like throwing a roll, and it depends on luck. This situation occurs.
 
, The application should not use the Bind Variable, but should be changed to the direct value.
In this case, refresh the sharing pool alter system flush shared_pool;
Or alter session set "_ optim_peek_user_binds" = false;
 
We can adjust the default bind peeking behavior of the database through implicit parameters:
 
_ OPTIM_PEEK_USER_BINDS. If we want to disable Bind Variable Peeking, we can set this parameter.
 
The value is False.
 
SQL> alter session set "_ optim_peek_user_binds" = false
 
 
Using Bind Var can improve performance mainly because it can avoid unnecessary Hard Parse as much as possible)
 
It saves time and a lot of CPU resources.
 
After a Client submits an SQL statement to Oracle, Oracle will first Parse it (Parse), and then
 
Submit the parsing result to Optimiser for optimization and obtain the optimal Query Plan that Oracle considers.
 
And then execute the SQL statement according to the optimal Plan (of course, if you only need soft resolution here
 
A few steps ).
 
After Oracle receives the SQL statement submitted by the Client, it first finds whether there is an existing SQL statement in the Shared Pool.
 
The parsed SQL statement is exactly the same as the SQL statement you just received (note that the statement is exactly the same here.
 
The characters in a sentence must be exactly the same, and the objects involved must be exactly the same ). When the same
 
The parser does not parse the new SQL statement here and directly uses the previously parsed results. This saves resolution time.
 
And the CPU resources consumed during parsing. Especially when a large number of short SQL statements are run in OLTP, the results will be quite clear.
 
. Because the time of one or two SQL statements may not feel much, but when the amount is large, it will be obvious
 
.
 
 
However, one disadvantage of variable binding is that the execution plan is not necessarily that SQL is in the real application.
 
The execution plan used. In this case, we can view the event 10053 event.

Author sky7034