Parsing - Troubleshooting Oracle Performance - page 428

In summary, if an application uses literals and cursor sharing is set to similar , the behavior depends on the
existence of relevant histograms. If they do exist, similar behaves like exact . If they don't exist, similar behaves like
force . This means that if you are facing parsing problems, more often than not, it's pointless to use similar .
Server-Side Statement Caching
This feature is similar to client-side statement caching because it's designed to reduce overhead when too many soft
parses are taking place. From a conceptual point of view, the two types of statement caching are similar, except that
one is implemented on the server side and the other on the client side. From a performance point of view, however,
the differences are considerable. In fact, the server-side implementation is far less powerful than the client-side
implementation. This is because the server-side implementation reduces the overhead of soft parses on the server
side only, and in more than a few circumstances, the overhead of soft parses is much greater on the client than on
the server. The only real advantage of the server-side implementation is the ability to cache SQL statements that are
executed by PL/SQL or Java code deployed in the database engine.
If an application performs a lot of soft parses, the high pressure on library cache latches and mutexes may lead
to a noticeable contention on the database engine as well. The following database-side resource usage profile shows
such a situation. Note that to generate it, test case 2 was started while the database engine was processing more than
30,000 parses per second for the same SQL statement. Although this is certainly not a common workload, it helps
demonstrate the impact of server-side cursor caching.
Total Number of Duration per
Component Duration % Events Event
--------------------------- -------- ------- ---------- ------------
SQL*Net message from client 4.166 54.569 10,000 0.000
library cache: mutex X 2.622 34.339 158 0.017
CPU 0.557 7.294 n/a n/a
latch free 0.265 3.473 1 0.265
SQL*Net message to client 0.014 0.177 10,000 0.000
cursor: pin S 0.011 0.148 1 0.011
--------------------------- -------- -------
Total 7.635 100.000
Whenever the server-side overhead of soft parses is a problem and the application cannot be changed, server-side
statement caching may be useful. In this specific case, after enabling it and reapplying the same load, the resulting
resource usage profile is the following. Notice how most waits related to library cache latches and mutexes disappeared.
Total Number of Duration per
Component Duration % Events Event
--------------------------- -------- ------- --------- ------------
SQL*Net message from client 4.646 85.959 10000 0.000
CPU 0.420 7.769 n/a n/a
cursor: pin S 0.328 6.070 2 0.164
SQL*Net message to client 0.011 0.202 10000 0.000
--------------------------- -------- -------
Total 5.405 100.000
Server-side statement caching is configured through the session_cached_cursors initialization parameter. Its
value specifies the maximum number of cursors each session is able to cache. So if it's set to 0, the feature is disabled,
and if it's set to a value greater than 0, it's enabled. In version 10.2, the default value is 20; and from version 11.1