Oracle Binding variables

SELECT * FROM table where id =?

Similar to the above SQL, if you do not have to bind the variable, each time Oracle will be considered a different SQL, will be generated at each execution of the execution plan, and the execution plan generation is very CPU-consuming, imagine, if 1000 concurrency is executing this statement, equals at the same time generating 1000 execution plans.

If a bound variable is used, Oracle considers the same statement even if the value of the ID is changing, and is saved to the shared pool only the first time it is generated and executed once. The following 999 executions do not need to generate the execution plan, directly to use.

CPU performance is on the one hand, on the other hand, do not use binding variables, the latter will also lead to more serious problems, because a large number of execution plans into the shared pool, will be limited memory fills, and these execution plans are very small. Cut the memory space very finely, wait until the next time a large statement needs to be saved to the shared pool, even if the space is sufficient, also because there is no continuous space and error.

So how to use the binding variable, in fact, is very simple, in Java, is to use preparestatement, in fact, everyone is so used, just know that oracle behind the use of binding variables and the disadvantage of not binding variables are not many people.

But binding variables are not always available at any time, and sometimes using bound variables is not necessarily good.

For example, the above statement, when Id=1, assuming that there is only one id=1 in the table, this time the execution plan will choose to walk the index.

When id=2, assuming that the table id=2 records accounted for 90%, this time obviously to go full-table scan.

However, because the second execution no longer generates an execution plan, it is generated directly with the first execution. The query performance will be poor when id=2.

However, this problem was solved in Oracle 11g, which still exists at 9i and 10g.

Even so, bound variables cannot be abused.