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:

• force 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 specified.

When using cursor sharing, the CBO changes any literal values in the SQL to bind variables.  Hence, this statement would be changed as follows:

select
   customer_stuff
from
   customer
where
   region = ‘west’
;

The transformation replaces the literal west with a host variable:

select
   customer_stuff
from
   customer
where
   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 be identical.

• 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.