Database Reference
In-Depth Information
number of cached cursors, you either need to know the application being used or you have to analyze it to find out
how many SQL statements are subject to a high number of soft parses. Then, based on this first estimation, some tests
will be necessary to verify whether the value is good. During such tests, it's possible to verify the effectiveness of the
cache not only by verifying the impact on the response time, but also by looking at the statistics resulting from the
following query. Note that the same statistics are available at the system level as well. In any case, you should focus on
a single session that has experienced the problematic load in order to find meaningful clues.
SQL> SELECT sn.name, ss.value
2 FROM v$statname sn, v$sesstat ss
3 WHERE sn.statistic# = ss.statistic#
4 AND sn.name IN ('session cursor cache hits',
5 'session cursor cache count',
6 'parse count (total)')
7 AND ss.sid = 42;
NAME VALUE
-------------------------- ----------
session cursor cache hits 9997
session cursor cache count 9
parse count (total) 10008
First, compare the number of cached cursors (
session cursor cache count
) with the value of the
session_
cached_cursors
initialization parameter. If the first is less than the second, it means that incrementing the value of
the initialization parameter should have no impact on the number of cached cursors. Otherwise, if the two values are
equal, increasing the value of the initialization parameter might be useful in order to cache more cursors. In any case,
it makes no sense to exceed the value of the
open_cursors
initialization parameter. For example, according to the
previous statistics, nine cursors are present in the cache. Since the
session_cached_cursors
initialization parameter
was set to 50 during the test, increasing it serves no purpose.
Second, using the additional figures, it's possible to check how many parse calls were optimized with server-side
statement caching (
session cursor cache hits
) relative to the total number of parse calls (
parse count (total)
). If
the two values are close, it probably isn't worthwhile to increase the size of the cache. In the case of the previous statistics,
more than 99% (9,997/10,008) of the parses are avoided thanks to the cache, so increasing it is probably pointless.
CaUtION! BUGS
the values provided by the
parse count (total)
and
session cursor cache hits
statistics are subject to
several bugs. the bugs you are most likely to notice are the following:
·
as of version 11.1.0.6, the
session cursor cache hits
statistic is incremented for cursors
taking advantage of the pL/sQL client-side statement caching. as a result, the
session cursor
cache hits
statistic can be much higher than the
parse count (total)
statistic. Using
client-side statement caching is the default for a pL/sQL program. thus, the
session cursor
cache hits
statistic is quite useless when pL/sQL is in use.
as of version 11.2.0.1, the
·
session cursor cache hits
statistic at the session level is stored in
an unsigned integer taking 16 bits. therefore, sessions with more than 65,535 hits experience an
overflow, and the value restarts from 0. and, even though the statistic at the system level doesn't
have such a limitation, an overflow at the session level still causes the system level statistic to
decrease by 65,535. as a result, the
session cursor cache hits
statistic is almost useless
