Starting with Performance Tuning - Oracle Database 11gR2 Performance Tuning - page 3

For example, we could code something like the following (using SQL*Plus, connected as
user HR ):
SQL>SELECT * FROM hr.jobs WHERE job_id = 'SA_MAN';
This is equivalent to the following:
SQL>VARIABLE JOBID VARCHAR2(10)
SQL>EXEC :JOBID := 'SA_MAN'
SQL>SELECT * FROM hr.jobs WHERE job_id = :JOBID;
The big difference between the two examples is in the way the database parses the
statements when they are called more than once with different values. Executing the
statements the second time, in the first case will require a hard parse, whereas in the
second case, Oracle will reuse the execution plan prepared at the time of the first
execution, resulting in a huge performance gain.
This behavior is due to the way Oracle checks whether a SQL statement is
already in memory or needs to be parsed. A hash value of the SQL string
is calculated, and is compared to the hash values already in memory. If we
supply a different literal value each time, a new hash value will get generated
for a SQL statement and hence Oracle has to parse the statement every time.
Using bind variables will not change the SQL string so Oracle has to parse the
statement only once; from there on it will find the hash value in memory—if it
doesn't age out—thus reusing the execution plan already existing in memory.
Cursor sharing is another problem related to the parse process. We can set the database
parameter CURSOR_SHARING to the values SIMILAR or FORCE , to mitigate the drawbacks
related to not using bind variables. In this situation, the database will parse two queries with
a different SQL text to a single cursor; for example:
SQL>SELECT * FROM hr.jobs WHERE job_id = 'SA_MAN';
SQL>SELECT * FROM hr.jobs WHERE job_id = 'AC_ACCOUNT';
Both of these statements will be parsed to a single cursor if the parameter CURSOR_SHARING
is set to one of the values mentioned.
When a query is dynamically built by the application—for example, to reflect different types of
user-defined filters or sorting options—it's important that the statement is built always in the
same way—using bind variables, of course—to facilitate the reuse of the cursors, mostly if the
CURSOR_SHARING parameter is set to the value EXACT .