September 2004

The Specialist
Database News You Can Use
A monthly newsletter for Oracle usersSeptember 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Bind Variables and Cursor Sharing

You’re invited to submit your Oracle-related questions to us at askdbspecialists09@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Oracle Releases Critical Security Update

This month’s question comes to us from Philip in Berkeley, California: What are the valid settings for the cursor_sharing parameter, and when would I use them?

Roger Schrag of the Database Specialists team responds: The cursor_sharing instance parameter was added in Oracle 8i as a way to reduce parse time for applications that do not use bind variables. In Oracle 9i, the feature was enhanced to handle certain special conditions. This parameter can be a big help for applications that run large numbers of similar SQL statements without bind variables.

The default value for the cursor_sharing parameter is EXACT. In this situation, Oracle will only consider that two SQL statements might have the same execution plan if the text of the two statements is exactly the same (white space included). Consider the following two queries:

 

	SELECT * FROM emp WHERE ename = 'KING';
	SELECT * FROM emp WHERE ename = 'SCOTT';

The two statements are not exactly the same, so Oracle will need to hard parse each. Hard parses can be expensive, especially for complex queries where the optimizer must choose the best execution plan from many possibilities.

If you had coded the queries to use bind variables instead of literal values, they might have looked like:

 

	SELECT * FROM emp WHERE ename = :ename;
	SELECT * FROM emp WHERE ename = :ename;

As you can see, the two queries are now identical—the only difference is the value of the bind variable :ename. A hard parse occurs on the first execution, but all subsequent executions are soft parsed because Oracle already has what it needs in the shared SQL area.

If the application does not use bind variables and cannot be changed, then you might consider setting the cursor_sharing parameter to FORCE. This tells Oracle that if a parse is attempted on a SQL statement that is not already parsed in the shared SQL area, then before performing a hard parse, Oracle should rewrite the query with “pseudo” bind variables and see if the rewritten query matches something in the shared SQL area. If a suitable match is found, then a soft parse occurs and the hard parse is avoided.

With cursor_sharing set to FORCE, the queries for KING and SCOTT get rewritten as:

 

	SELECT * FROM emp WHERE ename = :"SYS_B_0";

In this way, the cursor_sharing feature enables applications that do not use bind variables to benefit from a reduction in hard parses. But there are cases where forced cursor sharing can be a bad thing. Consider a JOURNAL_ENTRIES table with a column called POSTED that can be set to ‘y’ or ‘n’. Only the newest journal entries have not yet been posted, so an index is added to the POSTED column to speed queries like:

 

	SELECT * FROM journal_entries WHERE posted = 'n';

But you certainly don’t want the index to be used on queries like:

 

	SELECT * FROM journal_entries WHERE posted = 'y';

If you gather statistics on the table and the POSTED column and leave cursor_sharing set to EXACT, Oracle will be smart enough to use the index when searching for unposted items and avoid the index when looking for posted items. But if you set cursor_sharing to FORCE, the two queries will share the same execution plan and suboptimal behavior will result.

To address this dilemma, in Oracle 9i it is possible to set cursor_sharing to SIMILAR. This setting tells Oracle to behave as if cursor_sharing were set to FORCE, but not when the column involved has optimizer statistics and those statistics indicate a skewed data distribution. In other words, don’t force cursor sharing if the values of the literals have bearing on the execution plan.

With cursor_sharing set to SIMILAR, Oracle (in theory) can always do the right thing: A query containing literals will not be tampered with if those literals influence the execution plan. Queries with literals that don’t impact the execution plan are rewritten with pseudo bind variables in order to minimize hard parses.

The cursor_sharing instance parameter, introduced in Oracle 8i and enhanced in Oracle 9i, can be a godsend for reducing parse time in applications that don’t use bind variables. Proceed carefully, however, because there can be odd interactions between cursor sharing and facilities such as stored outlines, the EXPLAIN PLAN statement, and the AUTOTRACE feature of SQL*Plus. For example, the execution plan shown by EXPLAIN PLAN may not be the actual execution plan used once pseudo bind variables are introduced. Furthermore, setting cursor_sharing to SIMILAR can cause high version counts in v$sqlarea, and earlier releases of Oracle 8i and 9i definitely had some bugs related to cursor sharing.

 

On August 31, Oracle Corporation announced a new series of patches to address critical security holes that have been discovered in the Oracle database, application server, and Enterprise Manager. Most versions of the database are affected, ranging from version 8.1.7.4 all the way up to 10.1.0.2. Getting to Know Oracle Internals

Metalink bulletins 281188.1 and 281189.1 provide important information along with a patch availability matrix you can use to determine which patch numbers to download for your specific platform and Oracle version.

Oracle Corporation isn’t saying much about the security vulnerabilities, except that “several buffer overflow, format string, SQL injection and other types of vulnerabilities were discovered and reported to Oracle.” A little more information can be found at http://www.kb.cert.org/vuls/id/316206. You can also read the current security alerts on Oracle’s website at http://www.oracle.com/technology/deploy/security/alerts.htm.

 

Finding the time to learn about and understand Oracle internals can be challenging, but this knowledge can help a great deal when troubleshooting problems and optimizing performance. Getting the scoop on undocumented features or fixes can be tricky. Julian Dyke runs this website, and he thanks well-known Oracle gurus such as Jonathan Lewis and Steve Adams “for supplying input or amendments to this website.” Handy Oracle Resource Library

Just a few of the useful things you will find on this website:

  • A list of the various types of dumps an Oracle process can write, and the syntax for initiating a conditional or immediate dump.
  • A list of some of the more useful Oracle events, and the syntax for setting events in a session or across the instance.
  • Documentation for the dbms_system package.

 

Many Oracle professionals are aware of the Oracle-L email list where “all things Oracle” are discussed and dissected. It has many contributors such as authors Jonathan Lewis, Cary Millsap, and Mogens Norgaard who actively read and answer questions posted to the list. Oracle OpenWorld Coming to San Francisco

The manager of the Oracle-L list, Jared Still, has a website well worth checking out. He simply titles his home page, “Oracle, Perl and Whatever.” However, the site is full of articles and utilities that you might find handy—even if you don’t use Perl. You’ll find all this (plus information on the Oracle-L list) at http://www.cybcon.com/~jkstill. Some topics include:

  • Encrypting data in Oracle (includes code samples)
  • Creating a web interface for your SQL scripts (CGI script provided)
  • Using Perl DBI with Oracle (a template)

 

For those who like to plan ahead, the Oracle OpenWorld conference will take place December 5-9, 2004 in San Francisco. Billed as “the premier Oracle event for business decision-makers, IT management, and line of business end users,” Oracle OpenWorld is attended by thousands each year. Learn what Oracle Corporation has in store for 2005. The conference will feature a User Group Day—a day filled with participation from several national Oracle user groups that will feature parallel technical sessions, a closing best practices panel, and a networking reception. Online registration is now open at http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=17563.

Leave a Reply

Your email address will not be published. Required fields are marked *