Using cursor_sharing in Oracle8i and Oracle9i
Oracle Tips by Burleson Consulting
Cursor_sharing is a new initialization
parameter in Oracle8i (8.1.6) that is designed to help manage
the clutter problems with nonsharable SQL. Cursor_sharing can
take the following values:
The FORCE option forces statements that may differ in
some literals but are otherwise identical, to share a cursor,
unless the literals affect the meaning of the statement. This is
achieved by replacing literals with system-generated bind
variables and causes increased sharing of literal SQL.
exact (the default value) The
exact parameter causes only identical SQL statements to share a
cursor. This is the standard pre-Oracle8i method.
When cursor_sharing is set to
force, Oracle adds an extra layer of parsing that identifies
statements as equivalent if they differ only in the values of
literals, hashing them to identical library cache objects. You will
see that under the right circumstances this setting can help solve
the performance problems of literal SQL.
WARNING: Oracle technical support states
that cursor_sharing should be set to force only when
the risk of suboptimal plans is outweighed by the improvements in
cursor sharing. Forcing cursor sharing among similar (but not
identical) statements can have unexpected results in some DSS
applications and in applications using stored outlines.
Setting cursor_sharing=force may be worth
considering if your system has these characteristics:
Are there a large number of statements in the
shared pool that differ only in the values of their literals?
Is the response time low due to a very high
number of library cache misses (i.e., hard parses and library
cache latch contention)?
In cases where the Remote DBA has added cursor_sharing=force,
this directive has indeed made the SQL reusable, dramatically
reducing the strain on the shared pool. The downside is Oracle’s
warnings that some SQL can get suboptimal execution plans.
Oracle9i Enhancements to Cursor Sharing
A new feature in Oracle9i allows the CBO to
change execution plans even when optimizer plan stability is used.
This is called “peeking” and allows the CBO to change execution
plans when the value of a bind variable would cause a significant
change to the execution plan for the SQL.
To illustrate, consider a simple example of
an index on a region column of a customer table. The
region column has four values, north, south, east and west.
The data values for the region column are highly skewed with
90% of the values in the south region. Hence, the CBO would be
faster performing a full-table scan when south is specified,
and an index range scan when east, west, or north is
When using cursor sharing, the CBO changes
any literal values in the SQL to bind variables. Hence, this
statement would be changed as follows:
region = ‘west’
The transformation replaces the literal west
with a host variable:
region = ‘:var1’
In Oracle9i, the CBO “peeks” at the values of
user-defined bind variables on the first invocation of a cursor.
This lets the optimizer determine the selectivity of the where
clause operator, and change the execution plan whenever the south
value appears in the SQL.
This enhancement greatly improves the
performance of cursor sharing when a bind variable is used against a
highly skewed column.
Techniques to Reduce SQL Parsing
One of the goals of SQL tuning is to ensure
that all pre-parsed SQL statements are reusable. Remember, to be
reusable, an incoming SQL statement must exactly match a - SQL
statement in the library cache. Even small variations in SQL syntax
will cause Oracle to reparse the SQL statement. The
v$sql.executions column can be used to see the number of times a
SQL statement has been reused. There are several techniques that can
be used to ensure that all SQL is reusable.
Place all SQL inside stored procedures When
all SQL is encapsulated inside stored procedures, and the stored
procedures are placed into packages, all SQL can be guaranteed to
Avoid literal values in SQL Any
SQL statement that contains embedded literal values is highly
unlikely to be reused (e.g., select * from sales where name =
‘JONES’;). These non-reusable statements can fill the library
cache with non-reusable SQL statements. The solution is to
encourage all developers to use host variables in all SQL.
Next, let’s look at the details on how Oracle
generates the execution plan for a SQL statement.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.