Database Reference
In-Depth Information
·
in version 11.2.0.3, the
parse count (total)
statistic isn't incremented for cursors taking
advantage of server-side statement caching. as a result, the
session cursor cache hits
statistic can be much higher than the
parse count (total)
statistic. since using server-side
statement caching is the default, the
parse count (total)
statistic is practically useless in
version 11.2.0.3. this bug was fixed in version 11.2.0.4.
the bottom line is to be careful when interpreting and relying upon the
session cursor cache hits
statistic.
review the known bugs to make sure none apply to your situation, and especially keep the three listed here in mind.
It's also important to notice that in the previous statistics there were “only” 9,997 hits in the cache. Since test case
2 executed the same SQL statement 10,000 times, why weren't there 9,999? The answer is that a cursor is put in the
cursor cache only when it has been executed several times. The reason for this is to avoid caching cursors that are
executed only once. Getting 9,999 could be possible only when a shareable cursor is already present in the library
cache prior to the first parse call.
In summary, server-side statement caching is an important feature. In fact, when correctly sized, it might save
some overhead server side. However, just because this feature is available, there is no excuse for the application to
not manage cursors properly in the first place, especially because, as you have already seen, the parsing overhead is
higher when the caching is performed server side instead of client side.
Using Application Programming Interfaces
The goal of this section is to describe the features related to parsing for different application programming interfaces.
As described in the previous sections, to avoid unnecessary hard and soft parses, three central features should be
available: bind variables, the ability to reuse statements, and client-side statement caching. Table
12-1
summarizes
which of these features are available with which application programming interface. The next sections provide some
detailed information for PL/SQL, OCI, JDBC, ODP.NET, and PHP.
Table 12-1.
Overview of the Features Provided by Different Application Programming Interfaces
Application Programming Interface
Bind Variables
Reusing Statements
Client-Side Statement Caching
Java Database Connectivity (JDBC)
java.sql.Statement
java.sql.PreparedStatement
✓
✓
✓
Oracle Call Interface (OCI)
✓
✓
✓
Oracle C++ Call Interface (OCCI)
✓
✓
✓
Oracle Data Provider for .NET (ODP.NET)
✓
✓
Oracle Objects for OLE (OO4O)
✓
Oracle Provider for OLE DB
✓
✓
PHP (PECL OCI8 extension)
✓
✓
✓
PL/SQL
Static SQL
✓
✓
Native dynamic SQL
(
EXECUTE IMMEDIATE
)
✓
✓
