Databases Reference
In-Depth Information
Using the pattern illustrated in this recipe we will reduce the number of soft parses; the
statement is parsed only once in this line:
PreparedStatement ps = conn.prepareStatement("select first_name,
last_name from employees");
If the same statement had been parsed previously, a soft parse will occur. If the
PreparedStatement
is not closed, it can be executed multiple times—changing the
values assigned to bind variables—and only a "light" soft-parse will occur, with no syntax
and semantic check.
PL/SQL and parsing
Good news! What we have seen regarding parsing in the previous paragraph is managed
automatically by the PL/SQL engine.
In a PL/SQL procedure, we don't need to explicitly prepare a statement before executing
it, because the DML statements inside our procedures are automatically parsed once per
session, and not once per execution. Subsequent calls use a "softer" soft-parse, which we
can call a "light" soft-parse to distinguish it.
For example, if our application is written in Java, we have to parse the
execute
procedure
statement once, and subsequent calls to the procedure won't produce unnecessary hard
(and soft) parses, but only the unavoidable "light" soft-parse.
Diagnosing soft and hard parsing
Now we know the difference between hard, soft, and "light" soft-parse, and how to design
and write our application to reduce parsing.
But can we diagnose a parsing problem, caused by any third-party application, whose
sources we cannot inspect?
To find the answer, take a look at the dynamic performance views and monitor the following
values—library cache hit ratio and parse count.
Query the
V$LIBRARYCACHE
, using the following statement to view hit ratios related to
different areas of the database:
SELECT NAMESPACE, GETS, PINS, GETHITRATIO FROM V$LIBRARYCACHE
