Post date: 14-Jan-2010 13:11:02
Metalink DOC ID 34433.1
Supporting Example
====================
###########
###########
CASE 1
###########
###########
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string SIMILAR
SQL> variable n1 varchar2(20)
SQL> exec :n1:='Administration'
PL/SQL procedure successfully completed.
SQL> select department_id, department_name, manager_id from departments where department_name=:n1;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
10 Administration 200
SQL> select sql_text, executions from sys.v_$sqlarea where sql_text like '%select department_id, department_name, manager_id from departments%';
SQL_TEXT EXECUTIONS
---------------------------------------------------------------------------------------------------- ----------
select department_id, department_name, manager_id from departments where department_name=:n1 1
SQL> exec :n1:='Marketing'
PL/SQL procedure successfully completed.
SQL> select department_id, department_name, manager_id from departments where department_name=:n1;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
20 Marketing 201
SQL> select sql_text, executions from sys.v_$sqlarea where sql_text like '%select table_name from dba_tables where table_name%';
no rows selected
SQL> select sql_text, executions from sys.v_$sqlarea where sql_text like '%select department_id, department_name, manager_id from departments%';
SQL_TEXT EXECUTIONS
---------------------------------------------------------------------------------------------------- ----------
select department_id, department_name, manager_id from departments where department_name=:n1 2
###########
###########
CASE 2
###########
###########
SQL> alter session set cursor_sharing='EXACT';
Session altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> variable n2 varchar2(100)
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> exec :n1:='Purchasing'
PL/SQL procedure successfully completed.
SQL> exec :n2:='Human Resources'
PL/SQL procedure successfully completed.
SQL> select department_id, department_name, manager_id from departments where department_name=:n1;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
30 Purchasing 114
SQL> select department_id, department_name, manager_id from departments where department_name=:n2;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
40 Human Resources 203
SQL> select sql_text, executions from sys.v_$sqlarea where sql_text like '%select department_id, department_name, manager_id from departments%';
SQL_TEXT EXECUTIONS
---------------------------------------------------------------------------------------------------- ----------
select department_id, department_name, manager_id from departments where department_name=:n2 1
select department_id, department_name, manager_id from departments where department_name=:n1 1
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
###########
###########
CASE 3
###########
###########
SQL> alter session set cursor_sharing='FORCE';
Session altered.
SQL> select department_id, department_name, manager_id from departments where department_name=:n1;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
30 Purchasing 114
SQL> select department_id, department_name, manager_id from departments where department_name=:n2;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
------------- ------------------------------ ----------
40 Human Resources 203
SQL> select sql_text, executions from sys.v_$sqlarea where sql_text like '%select department_id, department_name, manager_id from departments%';
SQL_TEXT EXECUTIONS
---------------------------------------------------------------------------------------------------- ----------
select department_id, department_name, manager_id from departments where department_name=:n2 1
select department_id, department_name, manager_id from departments where department_name=:n1 1
select sql_text, executions from sys.v_$sqlarea where sql_text like '%select department_id, departme 1
nt_name, manager_id from departments%'