8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements

Oracle 9i introduced the CURSOR_SHARING parameter, which determines how the database handles statements containing literal values.

This shouldn't be considered a solution for badly written applications. This is a sticking plaster solution that can be used to help while the application is refactored to use bind variables. In addition, it doesn't give you any protection against SQL injection in the way using bind variables directly does.

Related articles.

CURSOR_SHARING

The CURSOR_SHARING initialization parameter allows existing applications to improve SQL reuse. The allowable values are listed below.

In an ideal world the application should be written to encourage cursor sharing, but applications that don't use bind variables may see a reduction in hard parses, and a reduced number of similar statements in the shared pool when using cursor sharing.

In the following example we run two SQL statements containing literal values and display the contents of the V$SQLAREA view.

-- Run two statements containing literals.
select * from dual where dummy = 'LITERAL1';
select * from dual where dummy = 'LITERAL2';


-- Check the contents of the shared pool.
column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'LITERAL1'                           1
select * from dual where dummy = 'LITERAL2'                           1

SQL>

We can see both statements are stored in the shared pool, each with a single execution.

Next we enable enable cursor sharing for the session, and repeat a similar test.

-- set cursor sharing.
alter session set cursor_sharing=force;

-- Run two statements containing literals.
select * from dual where dummy = 'LITERAL3';
select * from dual where dummy = 'LITERAL4';


-- Check the contents of the shared pool.
column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'LITERAL1'                           1
select * from dual where dummy = 'LITERAL2'                           1
select * from dual where dummy = :"SYS_B_0"                           2

SQL>

We can see the database has substituted a system generated bind variable called :"SYS_B_0" for the literal values. This now means the two statement we ran have an exact test match, so we only have a single statement added to the shared pool, which has two executions.

After Logon Trigger

Setting the CURSOR_SHARING parameter at system level is rather harsh. It's better if you can build it into the connection for a specific problem application. If it can't be added to your application login code, you can target specific schemas the problem applications uses by adding an after logon trigger to them.

The following trigger relates to the after logon event for the TEST user. It sets the CURSOR_SHARING parameter to the value FORCE after the session has logged on.

create or replace trigger test.after_logon_trg
after logon on test.schema
begin
  execute immediate 'alter session set cursor_sharing=force';
end;
/

CURSOR_SHARING_EXACT Hint

When cursor sharing is enabled all literals are converted to bind variables. If there is a reason you don't want this to happen for a specific statement you can use the CURSOR_SHARING_EXACT hint to prevent the conversion of literals to bind variables for this statement.

select /*+ CURSOR_SHARING_EXACT */ * from dual where dummy = 'LITERAL5';

For more information see:

Hope this helps. Regards Tim...

Back to the Top.