Oracle Cursor Sharing Tuning


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Cursor Sharing

Version 11.1

General

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:

Are there statements in the shared pool that differ only in the values of literals?
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.

Modes

EXACT	default since 8.1.6
FORCE	since 8.1.6
SIMILAR	9.0.1 and above

Related Data Dictionary Objects

Cursor related views

SELECT view_name
FROM dba_views
WHERE view_name LIKE '%CURSOR%'
ORDER BY 1;

GV_
GV_
GV_
GV_
GV_

Parent cursor related views

SELECT DISTINCT table_name
FROM dba_tab_cols
WHERE table_name LIKE '%CURSOR%'
AND column_name LIKE '%PARENT%';

GV_

Child cursor related views

SELECT DISTINCT table_name
FROM dba_tab_cols
WHERE table_name LIKE '%CURSOR%'
AND column_name LIKE '%CHILD%'
ORDER BY 1;

GV_
GV_

System Setting Syntax

ALTER SYSTEM SET <sharing_mode> = <mode_name>
[SCOPE=<BOTH|MEMORY|SPFILE];

ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=BOTH;

Session Setting Syntax

ALTER SESSION SET <sharing_mode> = <mode_name>;

ALTER SESSION SET cursor_sharing='EXACT';

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 b, gv 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 pa, V ex
WHERE pa.SID = ex.SID
AND pa.STATISTIC#=(
SELECT STATISTIC#
FROM V
WHERE NAME = 'parse count (hard)')
AND ex.STATISTIC#=(
SELECT STATISTIC#
FROM V
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
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
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

-- as SYS look in the shared pool
SELECT address, child_address, sql_text
FROM gv
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
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
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
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
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
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
WHERE sql_fulltext LIKE '%uwclass%';

desc gv_

SELECT fetches, executions, parse_calls, disk_reads
FROM V
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT application_wait_time, user_io_wait_time, cpu_time
FROM V
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT optimizer_mode, optimizer_cost, sorts
FROM V
WHERE sql_id = '7f2fuwa2xyzyk';

SELECT latitude FROM uwclass.servers WHERE srvr_id = 4;

-- ======================================
SELECT name, value
FROM gv
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
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
WHERE sql_fulltext LIKE '%uwclass%';

-- we now have two child cursors. Let's see why.
desc gv_

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
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
WHERE sql_fulltext LIKE '%uwclass%';

SELECT name, value
FROM gv
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
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
WHERE sql_fulltext LIKE '%uwclass%';

SELECT child_number, child_address, optimizer_mismatch
FROM gv
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
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
WHERE sql_fulltext LIKE '%uwclass%';

SELECT child_number, child_address, stats_row_mismatch
FROM gv
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
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
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
WHERE sql_fulltext LIKE '%servers%';

SELECT child_number, child_address, translation_mismatch
FROM gv
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
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
WHERE sql_fulltext LIKE '%latitude%';

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

Related Topics

CURSOR_SHARING_EXACT

Tuning

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us