8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 : Automatically Convert Literals to Bind Variables in SQL Statements
- Literals, Substitution Variables and Bind Variables
CURSOR_SHARING
The CURSOR_SHARING
initialization parameter allows existing applications to improve SQL reuse. The allowable values are listed below.
EXACT
- (Default) Only statements with an exact text match will share the same cursor.FORCE
- The database substitutes system generated bind variables for all literals, thereby increasing the chances of a text match.SIMILAR
- Deprecated in Oracle 11.2. The database substitutes system generated bind variables for all literals, thereby increasing the chances of a text match. Oracle will force similar statements to share the SQL area without deteriorating execution plans.
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:
- CURSOR_SHARING
- CURSOR_SHARING and Bind Variable Substitution
- CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements
- Literals, Substitution Variables and Bind Variables
Hope this helps. Regards Tim...