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; |
- 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') |
- Reconnect and enable cursor sharing
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') |
- 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) |
- 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 |
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; |
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' |
excellent
clear and lucid explanation . Thanks !
Many thanks – this saved me hours of investigation into a very convoluted issue :)