BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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:

   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 proceduresWhen 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 SQLAny 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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter