Oracle Cursor Sharing
Version 11.2.0.3

General Information
The following demos were inspired by Julian Dyke's presentation at the UKOUG's November 2006 conference.

What the doc says:
Oracle automatically notices when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared - that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory use on the database server, thereby increasing system throughput.

In evaluating whether statements are similar or identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.

One of the first stages of parsing is to compare the text of the statement with existing statements in the shared pool to see if the statement can be shared. If the statement differs textually in any way, then Oracle does not share the statement.

Exceptions to this are possible when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE. When this parameter is used, Oracle first checks the shared pool to see if there is an identical statement in the shared pool. If an identical statement is not found, then Oracle searches for a similar statement in the shared pool. If the similar statement is there, then the parse checks continue to verify the executable form of the cursor can be used. If the statement is not there, then a hard parse is necessary to generate the executable form of the statement.

Statements that are identical, except for the values of some literals, are called similar statements. Similar statements pass the textual check in the parse phase when the CURSOR_SHARING parameter is set to SIMILAR or FORCE. Textual similarity does not guarantee sharing. The new form of the SQL statement still needs to go through the remaining steps of the parse phase to ensure that the execution plan of the preexisting statement is equally applicable to the new statement.

Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.

Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to share SQL. The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.

The CURSOR_SHARING initialization parameter can solve some performance problems. It has the following values: FORCE, SIMILAR, and EXACT (default). Using this parameter provides benefit to existing applications that have many similar SQL statements.

The optimal solution is to write sharable SQL, rather than rely on the CURSOR_SHARING parameter. This is because although CURSOR_SHARING does significantly reduce the amount of resources used by eliminating hard parses, it requires some extra work as a part of the soft parse to find a similar statement in the shared pool.

Consider setting CURSOR_SHARING to SIMILAR or 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 or SIMILAR 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 or SIMILAR, the outlines must be generated with CURSOR_SHARING set to FORCE or SIMILAR and with the CREATE_STORED_OUTLINES parameter.

Using CURSOR_SHARING = SIMILAR (or 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 and above
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

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%';

-- set cursor sharing to SIMILAR
ALTER SYSTEM SET cursor_sharing='SIMILAR' 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
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 SIMILAR
ALTER SYSTEM SET cursor_sharing='SIMILAR' 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 Slave 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
CURSOR_SHARING_EXACT
Tuning

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-2013 Daniel A. Morgan All Rights Reserved