cursor_sharing.txt
12-05-2001 Bikle
---------------------

Dave,

Your application was written by developers who made poor use of "bind variables".

Oracle 8.1.6 and later provides a feature inside the database which
helps us deal with this problem.  We trigger the feature via a
parameter in the init.ora.

The parameter-value-pair, CURSOR_SHARING=FORCE, allows the Oracle
kernel to collect thousands of similar sql statements cached in the
sql buffer.
   
Once they are collected, the kernel collapses them into a single sql
statement which contains what are called bind variables.

It's a beautiful feature which helps the Oracle kernel make more efficient
use of memory.

For your other applications, to see if you may gain from
CURSOR_SHARING=FORCE, run the query listed below which may return many
thousands of rows:

SELECT SUBSTR(sql_text,1,77) sql_text FROM v$sql ORDER BY sql_text;

Use your eyes to look for many sql statements which appear to be similar.

If you see groups of sql statements each containing hundreds of similar
sql statements, you will benefit if your DBA sets CURSOR_SHARING=FORCE
at the back-end Oracle database.

In the old days, it was up to the developers to build applications
which relied upon sql statements which contained bind variables.

This forced the Oracle kernel to avoid caching similar sql statements
in the SQL cache.

The CURSOR_SHARING=FORCE feature is a huge gain for some applications which do not use
many bind variables, or perhaps none at all.

Before I used CURSOR_SHARING=FORCE on your database I noticed these errors
appearing in the log files generated by your application:

ORA-04031: unable to allocate 4096 bytes of shared memory

Also I saw them in file named the "alert log" which captures some information
about events associated with the back-end database.

For more description type...
oracle cursor_sharing=force
at google.com

Holler if questions.

-Dan