A test comparison of bound variables and unbound variables in Oracle 10g

First, create a test table and record the parsing statistics:

To insert data in a loop, the following code does not use binding variables:

It is found that 11 hard parsers have been added.

When you query the v$sqlarea view, you can find these SQL that cannot be shared. Note that each SQL is executed only once. These SQL not only parse the intensive SQL resources, but also take up shared memory to store these different SQL codes:

Rebuild the test table and do a second test:

This time, bind variables are used, and the same 10 data inserts are used:

Now take a look at the statistical database parsed by SQL. Hard parsing has increased from 145 to 147.

For this SQL, there is only one copy in the shared pool, parsed once, and executed 10 times. This is the advantage of binding variables:

Since Oracle has provided a new initialization parameter to force variable binding on the server side since 8.1.6, this parameter is cursor_sharing. Initially, this parameter has two optional settings: exact and force.