Oracle cursor_sharing cached cursors
Oracle Database Tips by Donald Burleson
Back in version 8i when we first were introduced
to the CURSOR_SHARING initializaiton parameter we thought it was a
great idea. However, several false starts, multiple bugs and
performance that didn't quite meet expectations caused most users to
shelve the idea of CURSOR_SHARING until a more stable release.
Essentailly there were two options for the CURSOR_SHARING parameter;
EXACT, which meant the cursors (SQL statements) had to be exact in
order to be shared, and FORCE which means every literal was replaced
with a bind variable. The attraction was, many users were (and are)
stuck with poorly implemented applications form third-parties that
couldn't have the source code altered except by an act of God, so
the ability to force bind variable use seemed a good idea.
We soon discovered that it wasn't always a good idea to force bind
variables for 100% of statements. When a bind variable was what is
known as "unsafe", that is, it directly affects the execution plan
depending on its value (such as in a range type query, or when an IN
list is used) then it wasn't such a good idea to replace the
literals in a statement with a bind variable. Luckily, in 9i, or so
we thought, they gave us SIMILAR which supposedly would only
substitute when the bind variable was "safe". However, this doesn't
seem to be the case.
At a recent client site I had the perturbing problem that the shared
pool (at over 500 megabytes) was filled to 100%, but only with a
little over 1700 SQL objects! For those familiar with shared pools
and the amount of SQL they can hold, you will realize this size pool
should be able to hold 30,000 or more SQL areas. Looking at the
statspack SQL reports, showed some of the SQL areas had as many as
900 versions. In V$SQLAREA, these versions all get showed as one
object so typical monitoring using counts of the SQL areas in
V$SQLAREA didn't show this issue.
Further investigation using the V$SQL_SHARED_CURSOR view showed that
in the case of one SQL with over 700 versions, only 7 showed any
indication that they shouldn't be shared. We attempted to reconcile
and based on some MOSC research, setting event "10503 trace name
context forever, level 128" which was suposed to correct some issues
when character, raw, long raw and long values where replaced with
bind variables. We bounced the database and found absolutely no
difference. Using a search on v$sql_shared_cursor (searchs against
various forms of "high number of SQL versions" proved fruitless)
found bug report 3406977.8 which talked about a bug, 3406977, which
affects versions 184.108.40.206 through 220.127.116.11 and is fixed in 18.104.22.168
that causes statements like "select * from test where id in
(1,2,3);" and "select * from test where id in (2,2,3);" to be made
into multiple versions if CURSOR_SHARING is set to FORCE or SIMILAR!
Luckily there is a back port for 22.214.171.124 for both platforms (AIX 5L
and Solaris 64 Bit) at the client site that we can apply that
(crossing my virtual fingers) will fix the issue. Some of the
effects of this also cause high CPU usage (CPU Usage showing as a
major amount of the response time, with the result of CPU usage -
(CPU recursive + CPU Parse) being a major fraction, in our case CPU
Recursive and the result of the equation (known as CPU Other) where
both near 50% of the total CPU Usage with CPU parse being at 2%.
This large value for the CPU other calculation points at the
multi-versioning as being a possible issue. I will keep you all
posted on the results of the patch.