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:
You can also
tailor cursor_sharing
for a specific group of
users using Mike Ault's
script collection.
Other notes on cursor
sharing include:
