Oracle Cursor Sharing
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
The following demos were inspired by Julian Dyke's presentation at the UKOUG's November 2006 conference.

When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values, the optimizer can better determine the selectivity of a WHERE clause condition as ias if literals had been used, thereby improving the plan.

The Oracle database automatically deters whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool. The database performs the following steps t compare the text of the SQL statement to statements already in the pool.
  • the statement's text is hashed
  • the hash value is compared with hashes of statements already in the shared pool and if a match is found, following a text comparison, the assumption is made that the statements are the same and the same plan can be used.
    • This, of course, is nonsense and doesn't work well in the real world
In database 12cR1 CURSOR_SHARING = SIMILAR was deprecated and the recommended values are EXACT and FORCE. Consider setting CURSOR_SHARING to FORCE if both of the following questions are true:
  1. Are there statements in the shared pool that differ only in the values of literals?
  2. Is the response time low due to a very high number of library cache misses?
Caution:
Setting CURSOR_SHARING to FORCE prevents any outlines generated with literals from being used if they were generated with CURSOR_SHARING set to EXACT.

To use stored outlines with CURSOR_SHARING=FORCE, the outlines must be generated with CURSOR_SHARING set to FORCE and with the CREATE_STORED_OUTLINES parameter.

Using CURSOR_SHARING = FORCE can significantly improve cursor sharing on some applications that have many similar statements, resulting in reduced memory usage, faster parses, and reduced latch contention.
Data Dictionary Objects
V$OPEN_CURSOR V$SQL_CURSOR V$SYSTEM_CURSOR_CACHE
V$SESSION_CURSOR_CACHE V$SQL_SHARED_CURSOR  
Modes
EXACT default since 8.1.6
FORCE since 8.1.6
SIMILAR 9.0.1 to 11.2.0.4 only
Session Setting Syntax ALTER SESSION SET <sharing_mode> = <mode_name>;
ALTER SESSION SET cursor_sharing='EXACT';
System Setting Syntax ALTER SYSTEM SET <sharing_mode> = <mode_name> [SCOPE=<BOTH|MEMORY|SPFILE];
ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=BOTH SID=*;
 
Demos
Tom Kyte's Demo from asktom.oracle.com conn uwclass/uwclass@pdbdev

CREATE TABLE t (x NUMBER, y NUMBER);

alter system flush shared_pool;
alter system flush shared_pool;

select count(y) from t where x = 1;
select count(y) from t where x = 10;
select count(y) from t where x = 20;
select count(y) from t where x = 30;
select count(y) from t where x = 40;
select count(y) from t where x = 50;
select count(y) from t where x = 60;
select count(y) from t where x = 70;
select count(y) from t where x = 80;
select count(y) from t where x = 99;

set linesize 121
col sql_text format a50
col value_string format a5

SELECT s.sql_text, b.value_string, s.plan_hash_value
FROM gv$sql_bind_capture b, gv$sql s
WHERE s.hash_value = b.hash_value
AND s.address = b.address
AND s.child_number = b.child_number
AND s.sql_text LIKE 'select count(y) from t where x =%';
Query to determine parse statistics SELECT pa.SID, pa.VALUE "Hard Parses", ex.VALUE "Execute Count"
FROM V$SESSTAT pa, V$SESSTAT ex
WHERE pa.SID = ex.SID
AND pa.STATISTIC#=(
  SELECT STATISTIC#
  FROM V$STATNAME
  WHERE NAME = 'parse count (hard)')
AND ex.STATISTIC#=(
  SELECT STATISTIC#
  FROM V$STATNAME
  WHERE NAME = 'execute count')
AND pa.VALUE > 0;
 
Parent Cursors
Cursor Sharing Demo conn / as sysdba

set linesize 121
col name format a30
col value format a30
col sql_text format a65

-- verify default value of EXACT
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%cursor%';

-- if not set cursor sharing to EXACT
 ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=BOTH;

-- verify default value of EXACT
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%cursor%';

-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two similar SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

desc gv$sql

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

-- set cursor sharing to FORCE
ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;

-- verify FORCE was set
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%cursor%';

-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two similar SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';
 
Child Cursors
Mismatch demos conn / as sysdba

set linesize 121
col name format a30
col value format a30
col sql_text format a65

-- set cursor sharing to FORCE
ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;

-- verify SIMILAR was set
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%cursor%';

-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two similar SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text, sql_id
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

desc v$sqlarea

SELECT fetches, executions, parse_calls, disk_reads
FROM v$sqlarea
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT application_wait_time, user_io_wait_time, cpu_time
FROM v$sqlarea
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT optimizer_mode, optimizer_cost, sorts
FROM v$sqlarea
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT latitude FROM uwclass.servers WHERE srvr_id = 4;

-- ======================================
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer_mode%';

-- default is ALL_ROWS. Let's change it
ALTER SESSION SET optimizer_mode = CHOOSE;

-- verify the change
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer_mode%';

SELECT latitude FROM uwclass.servers WHERE srvr_id = 5;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

-- we now have two child cursors. Let's see why
desc gv$sql_shared_cursor

ANYDATA_TRANSFORMATION Is criteria for opaque type transformation and does not match
AUTH_CHECK_MISMATCH Authorization/translation check failed for the existing child cursor
BIND_MISMATCH The bind metadata does not match the existing child cursor. Likely a difference in bind variable definition.
BIND_PEEKED_PQ_MISMATCH Cursor based around bind peeked values
BIND_UACS_DIFF One cursor has bind UACs and one does not
BUFFERED_DML_MISMATCH Buffered DML does not match the existing child cursor 
CURSOR_PARTS_MISMATCH Cursor was compiled with subexecution (cursor parts were executed)
DESCRIBE_MISMATCH The typecheck heap is not present during the describe for the child cursor
DIFF_CALL_DURN If dependent SQL cursor/single call
DIFFERENT_LONG_LENGTH Value of LONG does not match
EXPLAIN_PLAN_CURSOR The child cursor is an explain plan cursor and should not be shared
FLASHBACK_CURSOR Cursor non-shareability due to flashback
FLASHBACK_TABLE_MISMATCH Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
INCOMP_LTRL_MISMATCH Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
INCOMPLETE_CURSOR Cursor is incomplete: typecheck heap came from call memory
INST_DRTLD_MISMATCH Insert direct load does not match the existing child cursor
INSUFF_PRIVS Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM Insufficient privileges on remote objects referenced by the existing child cursor
LANGUAGE_MISMATCH The language handle does not match the existing child cursor
LITERAL_MISMATCH Non-data literal values do not match the existing child cursor
LITREP_COMP_MISMATCH Mismatch in use of literal replacement
LOGICAL_STANDBY_APPLY Logical standby apply context does not match
LOGMINER_SESSION_MISMATCH LogMiner Session parameters mismatch
MULTI_PX_MISMATCH Cursor has multiple parallelizers and is slave-compiled
MV_QUERY_GEN_MISMATCH Internal, used to force a hard-parse when analyzing materialized view queries
MV_REWRITE_MISMATCH Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
MV_STALEOBJ_MISMATCH Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built
NO_TRIGGER_MISMATCH Cursor and child have no trigger mismatch
OPTIMIZER_MISMATCH A change to any of 33 supported parameters such as SORT_AREA_SIZE or OPTIMIZER_INDEX_COST_ADJUSTMENT and 151 unsupported parameters such as _unnest_subquery that change the optimizer environment.
OPTIMIZER_MODE_MISMATCH Optimizer mode has changed (for example, ALL_ROWS vs CHOOSE)
OUTLINE_MISMATCH The outlines do not match the existing child cursor
OVERLAP_TIME_MISMATCH Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME
PDML_ENV_MISMATCH PDML environment does not match the existing child cursor
PLSQL_CMP_SWITCHS_DIFF PL/SQL anonymous block compiled with different PL/SQL compiler switches. See DBMS_WARNING page of the library.
PQ_SLAVE_MISMATCH Top-level slave decides not to share cursor
PX_MISMATCH Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.
REMOTE_TRANS_MISMATCH The remote base objects of the existing child cursor do not match
ROLL_INVALID_MISMATCH Marked for rolling invalidation and invalidation window exceeded
ROW_LEVEL_SEC_MISMATCH The row level security policies do not match
ROW_SHIP_MISMATCH Session does not support row shipping, but cursor built in one that did
SEC_DEPTH_MISMATCH Security level does not match the existing child cursor
SLAVE_QC_MISMATCH The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave)
SQL_REDIRECT_MISMATCH SQL redirection mismatch
SQL_TYPE_MISMATCH The SQL type does not match the existing child cursor
STATS_ROW_MISMATCH The existing statistics do not match the existing child cursor. May be caused by tracing
STB_OBJECT_MISMATCH STB has come into existence since cursor was compiled
TOP_LEVEL_DDL_MISMATCH Is top-level DDL cursor
TOP_LEVEL_RPI_CURSOR Is top level RPI cursor
TRANSLATION_MISMATCH The base objects of the existing child cursor do not match. For example objects in different schemas with the same name.
TYPCHK_DEP_MISMATCH Cursor has typecheck dependencies
TYPECHECK_MISMATCH  The existing child cursor is not fully optimized
UNBOUND_CURSOR The existing child cursor was not fully built (in other words, it was not optimized)
USER_BIND_PEEK_MISMATCH Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan

SELECT child_number, child_address, optimizer_mode_mismatch
FROM gv$sql_shared_cursor
WHERE address = '34329344';

-- ====================================== OPTIMIZER PARAMETERS
-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two similar SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer_index_caching%';

-- default is ALL_ROWS. Let's change it
ALTER SESSION SET optimizer_index_caching = 40;

-- verify the change
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer_index_caching%';

SELECT latitude FROM uwclass.servers WHERE srvr_id = 4;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

SELECT child_number, child_address, optimizer_mismatch
FROM gv$sql_shared_cursor
WHERE address = '34291F58';

 -- ====================================== STATISTICS COLLECTION
-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two similar SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

exec dbms_monitor.session_trace_enable;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

exec dbms_monitor.session_trace_disable;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 4;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

SELECT child_number, child_address, stats_row_mismatch
FROM gv$sql_shared_cursor
WHERE address = '34291F58';

-- ====================================== TRANSLATION MISMATCH
-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as client1 run SQL statement
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;

-- as client2 run SQL statement
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

-- as client1 run SQL statement
SELECT latitude FROM servers WHERE srvr_id = 1;

-- as SYS look in the shared pool / multiple children not created
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

-- as client1
GRANT SELECT ON servers TO abc;

-- as ABC
CREATE TABLE servers AS
SELECT * FROM uwclass.servers;

-- as client1 run SQL statement
SELECT latitude FROM servers WHERE srvr_id = 1;

-- as client2 run SQL statement
SELECT latitude FROM servers WHERE srvr_id = 1;

-- as SYS look in the shared pool / multiple children not created
SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%servers%';

SELECT child_number, child_address, translation_mismatch
FROM gv$sql_shared_cursor
WHERE address = '3013AF5C';

 -- ====================================== BIND MISMATCH - finish down
-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as client 1
VARIABLE b1 VARCHAR2(15);

BEGIN
  SELECT netaddress
  INTO :b1
  FROM servers
  WHERE srvr_id = 1;
END;
/

print b1

VARIABLE b2 VARCHAR2(15);

BEGIN
  SELECT netaddress
  INTO :b2
  FROM servers
  WHERE srvr_id = 1;
END;
/

SELECT latitude FROM servers WHERE netaddress = :b2;

BEGIN
  SELECT netaddress
  INTO :b1
  FROM servers
  WHERE srvr_id = 2;
END;
/

SELECT latitude FROM servers WHERE netaddress = :b 1;

SELECT child_number, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%latitude%';

-- clear all cached SQL statements
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as client 1
VARIABLE b1 VARCHAR2(15);

BEGIN
  SELECT netaddress
  INTO :b1
  FROM servers
  WHERE srvr_id = 1;
END;
/

SELECT latitude FROM servers WHERE netaddress = :b1;

VARIABLE b1 VARCHAR2(30);

BEGIN
  SELECT netaddress
  INTO :b1
  FROM servers
  WHERE srvr_id = 1;
END;
/

SELECT latitude FROM servers WHERE netaddress = :b1;

SELECT address, child_address, sql_text
FROM gv$sql
WHERE sql_fulltext LIKE '%latitude%';

SELECT child_number, child_address, bind_mismatch
FROM gv$sql_shared_cursor
WHERE address = '?';

Related Topics
Database Security
Built-in Functions
Built-in Packages
Adaptive Cursor Sharing
CURSOR_SHARING_EXACT
Tuning
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx