If you're a developer working with relational databases such as the Oracle RDBMS and Microsoft SQL Server, the single most effective thing you can do to ensure your code is efficient and scalable is to use bind variables (and now you can even use them with Oracle 9i OLAP). Bind variables are important because they allow Oracle to reduce the amount of 'hard parsing' (and hence CPU activity) carried out when processing SQL statements, and are pretty much a requirement when building systems with many concurrent users.

Other notes on cursor_sharing:

The dynamic shop often has SQL that is generated by ad-hoc query tools with hard-coded literal values embedded within the SQL. As we know, hard-coded literal values make the SQL statements nonreusable unless cursor_sharing=force is set in the Oracle initialization file. Shops that are plagued with nonreusable SQL can set cursor_sharing=force.

Mike Ault has great scripts for Locating similar SQL in Oracle to see if your SQL is using bind variables.  Systems that need cursor sharing are littered with SQL that contains literal values, like this:

select cust_stuff from customer where cust_name = 'JONES';

These system that benefit from cursor_sharing have low "executions" because the SQL cannot be re-used it is only executed once. (as seen in the v$sql view) and the need for cursor sharing van be seen by Oracle hard parse (sharing criteria) elapsed time.  Other symptoms indicating the need for cursor_sharing include:

• High CPU attributable to shared pool activity

• High hard parses

• Low number of executions for individual SQL statements

You can also tailor cursor_sharing for a specific group of users using Mike Ault's script collection.  Other notes on cursor sharing include: