Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > Lack of bind variables

Lack of bind variables

Overview

This document explains how DPA reports information when bind variables are not used in an
application as well as how to make configuration changes so DPA reports statistics as if bind
variables were being used.

One of the first steps Oracle performs when parsing an incoming SQL statement is to compare
the text with existing statements in the shared pool to see if the statement can be shared. If the
SQL statement differs textually in any way the statement cannot be shared. This causes Oracle to
perform a hard parse for each statement, which can be expensive, and store each statement in
the shared pool increasing memory consumption. DPA will also display each of these similar
statements as distinct entries in the repository instead of aggregating statistics for the group as a
whole. Consider the following SQL statements:

select * from employees where last_name = ‘SMITH’;
select * from employees where last_name = ‘ANDREWS’;

These statements are not exactly the same so both will be stored separately in the Oracle shared
pool even though they are very similar to each other. These statements will also be displayed as
two distinct statements in DPA.

Bind variables

To solve this problem, the optimal solution is developing or modifying applications to share SQL
through the use of bind variables:

select * from employees where last_name = :ename;
select * from employees where last_name = :ename;

In this case Oracle would do a hard parse on the first statement, but all executions after that will
be soft parsed because the exact same statement is already in the shared pool.

Make Oracle use cursor sharing

If the application does not use bind variables and cannot be modified, setting the
CURSOR_SHARING database parameter (introduced in Oracle 8i), to FORCE or SIMILAR
(Oracle 9i or later) should be considered. The difference between SIMILAR and FORCE is that
SIMILAR will enable the sharing of similar SQL statements as long as the execution plan is not
deteriorated. The value of FORCE will force Oracle to share SQL statements even if the
execution plan may be less than optimal. With CURSOR_SHARING the two SQL statements in
the first example would be rewritten by Oracle to use bind variables as:

select * from employees where last_name = :”SYS_B_0”;

When should I use cursor sharing?

The CURSOR_SHARING parameter can be used to solve performance problems in applications
that do not use bind variables. FORCE should only be used as a last resort when the risk of using
suboptimal execution plans is outweighed by the performance gains in cursor sharing. The use of
SIMILAR will allow Oracle to choose when to rewrite the query with bind variables. A query with
values that do not impact the execution plan will be rewritten with bind variables, but if a literal
value causes a difference in execution plans, the query will not be altered.

Note: Forcing the use of cursor sharing can have unexpected results when using stored outlines
or complex queries. Also, star transformations are not supported with CURSOR_SHARING set to
FORCE or SIMILAR. Modifications to any Oracle parameters should always be thoroughly tested
before being implemented on a critical Oracle database environment.

Conclusion

When using DPA to monitor a database with CURSOR_SHARING turned on, statistics collection
will more accurately reflect the true performance patterns of the database. Because DPA will
identify the query as one specific SQL statement, rather than multiple, separate statements,
reported statistics will be more accurate. SolarWinds recommends the use of bind variables as a good
programming practice. When bind variables are not an option, SolarWinds recommends that
CURSOR_SHARING be evaluated for application compatibility, and turned on if possible to both
improve performance and improve accuracy of monitored data.

Last modified
19:15, 22 Jun 2016

Tags

Classifications

Public