Databases Reference
In-Depth Information
There's more...
A bind variable is a placeholder, used in our SQL statement, which can bind with actual
values during execution. We can also obtain the statistics related to parses by selecting
from the dynamic performance view
V$SYSSTAT
:
SELECT * FROM V$SYSSTAT WHERE NAME LIKE 'parse%';
In the following screenshot, you can see an example of the output:
In this example, 1095 out of 5110 are hard parses.
Concurrency and scalability
We have seen that using bind variables improves performance, due to the hard/soft parse
of the statements; there is a huge improvement in latch contention as well. When the
database is parsing a statement, it acquires a latch on the structures involved (shared SQL
area and library cache), and this is a huge limitation on concurrency and scalability. If there
are many users using the same application—without bind variables—the contention for these
shared latches will increase, and there will be many wait events as many users try to acquire
the same resource.
If there is an application that doesn't use bind variables, the entire database, and other
applications that use them, will suffer a drop in performance. The "bad" application (the
one that doesn't use bind variables) will insert many statements in the library cache. This
is because it doesn't reuse them, and every statement is different from the previous
execution. In most cases, the statements of the "good" applications are flushed from the
library cache because there isn't enough space.
