Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?

21 June 2016 2 Comments Written by Christian Antognini

The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.

The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement always take place?

The short answer is no.

I’m aware of three cases where it doesn’t take place. The first two cases are summarized by the following note that I published in the second edition of Troubleshooting Oracle Performance (page 434).

Cursor sharing doesn’t replace literal values contained in static SQL statements executed through PL/SQL. For dynamic SQL statements, the replacement takes place only when literals aren’t mixed with bind variables. This isn’t a bug; it’s a design decision. You can use the cursor_sharing_mix.sql script to reproduce this behavior.

The third case is something I learned recently: literal replacement doesn’t take place if the library cache contains a cursor that can be reused. A simple example of this behavior is shown by the following example:

  • Connect, flush shared pool, and disable cursor sharing
SQL> connect chris/ian
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SESSION SET cursor_sharing = exact;

SQL> connect chris/ian SQL> ALTER SYSTEM FLUSH SHARED_POOL; SQL> ALTER SESSION SET cursor_sharing = exact;

  • Run the test query and display its execution plan. Since cursor sharing is disabled, as expected no literal replacement took place.
SQL> SELECT * FROM t WHERE c2 = 'One';
 
        C1 C2
---------- -----
         1 One
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  agv8c3k7abbrb, child number 0
-------------------------------------
SELECT * FROM t WHERE c2 = 'One'
 
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='One')

SQL> SELECT * FROM t WHERE c2 = 'One'; C1 C2 ---------- ----- 1 One SQL> SELECT * FROM table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID agv8c3k7abbrb, child number 0 ------------------------------------- SELECT * FROM t WHERE c2 = 'One' Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='One')

  • Reconnect and enable cursor sharing
SQL> connect chris/ian@dba12102.antognini.ch
SQL> ALTER SESSION SET cursor_sharing = force;

SQL> connect chris/ian@dba12102.antognini.ch SQL> ALTER SESSION SET cursor_sharing = force;

  • Run the same test query as before and display its execution plan. Even though cursor sharing is enabled, no literal replacement took place! Notice that since a connect took place before the execution of the test query, what we see can’t be influenced by the fact that the client might have cached the cursor.
SQL> SELECT * FROM t WHERE c2 = 'One';
 
        C1 C2
---------- -----
         1 One
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  agv8c3k7abbrb, child number 0
-------------------------------------
SELECT * FROM t WHERE c2 = 'One'
 
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='One')

SQL> SELECT * FROM t WHERE c2 = 'One'; C1 C2 ---------- ----- 1 One SQL> SELECT * FROM table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- SQL_ID agv8c3k7abbrb, child number 0 ------------------------------------- SELECT * FROM t WHERE c2 = 'One' Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='One')

  • Run a different test query. Since cursor sharing is enabled, as expected literal replacement took place.
SQL> SELECT * FROM t WHERE c2 = 'Two';
 
        C1 C2
---------- -----
         2 Two
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  5jczgx2mp0jn2, child number 0
-------------------------------------
SELECT * FROM t WHERE c2 = :"SYS_B_0"
 
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"=:SYS_B_0)

SQL> SELECT * FROM t WHERE c2 = 'Two'; C1 C2 ---------- ----- 2 Two SQL> SELECT * FROM table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- SQL_ID 5jczgx2mp0jn2, child number 0 ------------------------------------- SELECT * FROM t WHERE c2 = :"SYS_B_0" Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=:SYS_B_0)

  • Finally, check the content of the library cache for the test queries. As expected, the first query was executed twice, and the second query was executed only once. Notice that the SQL id and child number match with the ones shown above.
SQL> SELECT sql_id, child_number, sql_text, executions
  2  FROM v$sql
  3  WHERE sql_text LIKE 'SELECT * FROM t WHERE c2 = %';
 
SQL_ID        CHILD_NUMBER SQL_TEXT                                           EXECUTIONS
------------- ------------ -------------------------------------------------- ----------
agv8c3k7abbrb            0 SELECT * FROM t WHERE c2 = 'One'                            2
5jczgx2mp0jn2            0 SELECT * FROM t WHERE c2 = :"SYS_B_0"                       1

SQL> SELECT sql_id, child_number, sql_text, executions 2 FROM v$sql 3 WHERE sql_text LIKE 'SELECT * FROM t WHERE c2 = %'; SQL_ID CHILD_NUMBER SQL_TEXT EXECUTIONS ------------- ------------ -------------------------------------------------- ---------- agv8c3k7abbrb 0 SELECT * FROM t WHERE c2 = 'One' 2 5jczgx2mp0jn2 0 SELECT * FROM t WHERE c2 = :"SYS_B_0" 1

The next question to ask is: what happens if the library cache contains two shareable cursors, one that contains the literal (i.e. the cursor wasn’t subject to literal replacement) and another that doesn’t contain it?

The answer to this question depends on the version you are using.

This is the behavior I noticed during my tests:

  • 11.1.0.6 – 11.2.0.1: the cursor that contains the literal is used
  • 11.2.0.2 – 12.1.0.1: the cursor that was subject to literal replacement is used
  • 12.1.0.2: the cursor that contains the literal is used (same as pre-11.2.0.2 versions)

If you are interested to test this behavior, the following is the test case I used to find out which version uses which method.

DROP TABLE t PURGE;
 
CREATE TABLE t (c1 NUMBER, c2 VARCHAR2(5));
 
INSERT INTO t VALUES(1, 'One');
INSERT INTO t VALUES(2, 'Two');
INSERT INTO t VALUES(3, 'Three');
INSERT INTO t VALUES(4, 'Four');
COMMIT;
 
execute dbms_stats.gather_table_stats(user,'t')
 
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET session_cached_cursors = 0;
 
DECLARE
  l_t t%ROWTYPE;
  PROCEDURE set_cursor_sharing(p_value VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing = ' || p_value;
	dbms_output.put_line('CURSOR_SHARING: ' || upper(p_value));
    dbms_output.put_line('*********************************');
  END set_cursor_sharing;
  PROCEDURE print_info_prev_cursor IS
    l_cursor_sharing v$parameter.value%TYPE;
    l_sql_id v$session.prev_sql_id%TYPE;
    l_child_number v$session.prev_child_number%TYPE;
    l_sql_text v$sqlarea.sql_text%TYPE;
    l_cursor_open VARCHAR2(5);
  BEGIN
    SELECT prev_sql_id, prev_child_number 
    INTO l_sql_id, l_child_number 
    FROM v$session 
    WHERE sid = sys_context('userenv','sid');
 
    SELECT sql_text
    INTO l_sql_text
    FROM v$sqlarea
    WHERE sql_id = l_sql_id;
 
    dbms_output.put_line('SQL_ID:         ' || l_sql_id);
    dbms_output.put_line('CHILD_NUMBER:   ' || l_child_number);
    dbms_output.put_line('SQL_TEXT:       ' || l_sql_text);
    dbms_output.put_line('---------------------------------');
  END print_info_prev_cursor;
BEGIN
  set_cursor_sharing('exact');
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t;
  print_info_prev_cursor;
 
  set_cursor_sharing('force');
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t;
  print_info_prev_cursor;
 
  set_cursor_sharing('exact');
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Three''' INTO l_t;
  print_info_prev_cursor;
 
  set_cursor_sharing('force');
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Three''' INTO l_t;
  print_info_prev_cursor;
  EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Four''' INTO l_t;
  print_info_prev_cursor;
END;
/
 
SELECT sql_id, child_number, executions, sql_text 
FROM v$sql 
WHERE parsing_schema_name = user 
AND sql_text like 'SELECT * %'
ORDER BY sql_id;

DROP TABLE t PURGE; CREATE TABLE t (c1 NUMBER, c2 VARCHAR2(5)); INSERT INTO t VALUES(1, 'One'); INSERT INTO t VALUES(2, 'Two'); INSERT INTO t VALUES(3, 'Three'); INSERT INTO t VALUES(4, 'Four'); COMMIT; execute dbms_stats.gather_table_stats(user,'t') ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET session_cached_cursors = 0; DECLARE l_t t%ROWTYPE; PROCEDURE set_cursor_sharing(p_value VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing = ' || p_value; dbms_output.put_line('CURSOR_SHARING: ' || upper(p_value)); dbms_output.put_line('*********************************'); END set_cursor_sharing; PROCEDURE print_info_prev_cursor IS l_cursor_sharing v$parameter.value%TYPE; l_sql_id v$session.prev_sql_id%TYPE; l_child_number v$session.prev_child_number%TYPE; l_sql_text v$sqlarea.sql_text%TYPE; l_cursor_open VARCHAR2(5); BEGIN SELECT prev_sql_id, prev_child_number INTO l_sql_id, l_child_number FROM v$session WHERE sid = sys_context('userenv','sid'); SELECT sql_text INTO l_sql_text FROM v$sqlarea WHERE sql_id = l_sql_id; dbms_output.put_line('SQL_ID: ' || l_sql_id); dbms_output.put_line('CHILD_NUMBER: ' || l_child_number); dbms_output.put_line('SQL_TEXT: ' || l_sql_text); dbms_output.put_line('---------------------------------'); END print_info_prev_cursor; BEGIN set_cursor_sharing('exact'); EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t; print_info_prev_cursor; set_cursor_sharing('force'); EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t; print_info_prev_cursor; set_cursor_sharing('exact'); EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Three''' INTO l_t; print_info_prev_cursor; set_cursor_sharing('force'); EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''One''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Two''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Three''' INTO l_t; print_info_prev_cursor; EXECUTE IMMEDIATE 'SELECT * FROM t WHERE c2=''Four''' INTO l_t; print_info_prev_cursor; END; / SELECT sql_id, child_number, executions, sql_text FROM v$sql WHERE parsing_schema_name = user AND sql_text like 'SELECT * %' ORDER BY sql_id;

For example, the following is an excerpt of the relevant part of the output I got in 12.1.0.2.

CURSOR_SHARING: EXACT
*********************************
SQL_ID:         2tdq596yh0b7j
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='One'
---------------------------------
CURSOR_SHARING: FORCE
*********************************
SQL_ID:         2tdq596yh0b7j
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='One'
---------------------------------
SQL_ID:         4d7v8dagr9aa8
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2=:"SYS_B_0"
---------------------------------
CURSOR_SHARING: EXACT
*********************************
SQL_ID:         2tdq596yh0b7j
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='One'
---------------------------------
SQL_ID:         0mf3qvhxxzcfa
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='Two'
---------------------------------
SQL_ID:         6xgzqhxqgm88v
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='Three'
---------------------------------
CURSOR_SHARING: FORCE
*********************************
SQL_ID:         2tdq596yh0b7j
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='One'
---------------------------------
SQL_ID:         0mf3qvhxxzcfa
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='Two'
---------------------------------
SQL_ID:         6xgzqhxqgm88v
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2='Three'
---------------------------------
SQL_ID:         4d7v8dagr9aa8
CHILD_NUMBER:   0
SQL_TEXT:       SELECT * FROM t WHERE c2=:"SYS_B_0"
---------------------------------
 
SQL> SELECT sql_id, child_number, executions, sql_text
  2  FROM v$sql
  3  WHERE parsing_schema_name = user
  4  AND sql_text like 'SELECT * %'
  5  ORDER BY sql_id;
 
SQL_ID        CHILD_NUMBER EXECUTIONS SQL_TEXT
------------- ------------ ---------- --------------------------------------------------
0mf3qvhxxzcfa            0          2 SELECT * FROM t WHERE c2='Two'
2tdq596yh0b7j            0          4 SELECT * FROM t WHERE c2='One'
4d7v8dagr9aa8            0          2 SELECT * FROM t WHERE c2=:"SYS_B_0"
6xgzqhxqgm88v            0          2 SELECT * FROM t WHERE c2='Three'

CURSOR_SHARING: EXACT ********************************* SQL_ID: 2tdq596yh0b7j CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='One' --------------------------------- CURSOR_SHARING: FORCE ********************************* SQL_ID: 2tdq596yh0b7j CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='One' --------------------------------- SQL_ID: 4d7v8dagr9aa8 CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2=:"SYS_B_0" --------------------------------- CURSOR_SHARING: EXACT ********************************* SQL_ID: 2tdq596yh0b7j CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='One' --------------------------------- SQL_ID: 0mf3qvhxxzcfa CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='Two' --------------------------------- SQL_ID: 6xgzqhxqgm88v CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='Three' --------------------------------- CURSOR_SHARING: FORCE ********************************* SQL_ID: 2tdq596yh0b7j CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='One' --------------------------------- SQL_ID: 0mf3qvhxxzcfa CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='Two' --------------------------------- SQL_ID: 6xgzqhxqgm88v CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2='Three' --------------------------------- SQL_ID: 4d7v8dagr9aa8 CHILD_NUMBER: 0 SQL_TEXT: SELECT * FROM t WHERE c2=:"SYS_B_0" --------------------------------- SQL> SELECT sql_id, child_number, executions, sql_text 2 FROM v$sql 3 WHERE parsing_schema_name = user 4 AND sql_text like 'SELECT * %' 5 ORDER BY sql_id; SQL_ID CHILD_NUMBER EXECUTIONS SQL_TEXT ------------- ------------ ---------- -------------------------------------------------- 0mf3qvhxxzcfa 0 2 SELECT * FROM t WHERE c2='Two' 2tdq596yh0b7j 0 4 SELECT * FROM t WHERE c2='One' 4d7v8dagr9aa8 0 2 SELECT * FROM t WHERE c2=:"SYS_B_0" 6xgzqhxqgm88v 0 2 SELECT * FROM t WHERE c2='Three'

11gR1, 11gR2, 12cR1, Cursor Sharing, Query Optimizer
Ad: The Method R Guide to MASTERING ORACLE TRACE DATA
Statement-level PARALLEL Hint

2 Comments

  1. Halim Halim
    27 March 2017    

    excellent

    Reply
  2. Rajesh Ramakrishnan Rajesh Ramakrishnan
    29 June 2019    

    clear and lucid explanation . Thanks !

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.