oracle cursor不能共享原因及示例

1. cursor概念介绍

cursor 游标,是ORACLE数据库中SQL解析和执行的载体，本质上是C语言中的一种结构structure. ORACLE数据库中cursor分类： shared cursor包括： parent cursor、child cursor session cursor包括： 隐式游标 implicit cursor、 显式游标 explicit cursor、 参考游标 ref cursor 两者关系是:一个session cursor只能对应一个shared cursor，一个shared cursor可以同时对应多个session cursor。共享游标信息可以通过V$SQL_SHARED_CURSOR视图进行查询，游标不能共享的原因也在此视图中查询。 Cursor不能共享主要是指子游标中存储的执行计划不能共享，此处只涉及shared cursor中parent cursor/ child cursor结构信息： shared cursor 指library cache中的一种库缓存对象，是缓存在library cache中的SQL语句/匿名PL/SQL语句所对应的库缓存对象，具体存储SQL文本、解析树、SQL涉及的对象定义、所使用的绑定变量类型和长度、执行计划等。 shared cursor分为父游标parent cursor，子游标 child cursor. 可以在v$sqlarea中查看当前库缓存中的父游标，在v$sql中查看child cursor. 父游标parent cursor，子游标 child cursor的结构是一样的(均以库缓存对象句柄的方式缓存在库缓存中，namespace属性的值均为CRSR)， 区别在于目标SQL的SQL文本会存储在其父游标parent cursor所对应的库缓存对象句柄的NAME中(子游标的库缓存对象句柄的NAME中是空，只有通过父游标才能找到子游标)。 SQL的解析树和执行计划会存储在其子游标 child cursor的库缓存对象句柄的HEAP6中，ORACLE在父游标parent cursor所对应的库缓存对象句柄的HEAP 0有CHILD TABL中存储从属的所有子游标 child cursor的库缓存对象句柄地址。 即通过访问父游标的HEAP 0中的CHILD TABLE从而依次访问从属于父游标的所有子游标。 在ORACLE数据库里，任意一个目标SQL一定会同时对应两个shared cursor。父游标parent cursor，子游标 child cursor. 父游标在内存中的地址可以通过视图：v$sql. ADDRESSf进行查询 Address of the handle to the parent for this cursor 子标在内存中的地址可以通过视图：v$sql. CHILD_ADDRESS进行查看Address of the child cursor 或者在V$SQL_SHARED_CURSOR中查询

SQL_ID VARCHAR2(13) SQL identifier
ADDRESS RAW(4 | 8) Address of the parent cursor
CHILD_ADDRESS RAW(4 | 8) Address of the child cursor

2.游标不能共享可能原因 游标不能共享的原因很多，Oracle通过V$SQL_SHARED_CURSOR视图的列值是Y/N来显示当前指定SQL_ID不能被共享的原因，在特殊情况下可能出现V$SQL_SHARED_CURSOR视图的列值全为N但是确实不能共享，这种情况可能是遇到了bug. 下面列举游标不能共享的主要原因;

UNBOUND_CURSOR
The existing child cursor was not fully built (in other words, it was not optimized)
SQL_TYPE_MISMATCH
The SQL type does not match the existing child cursor
**OPTIMIZER_MISMATCH
The optimizer environment does not match the existing child cursor (The optimizer mode has changed and therefore the existing child cannot be re-used).
For example:
select count(*) from emp; ->> 1 PARENT, 1 CHILD
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp; ->> 1 PARENT, 2 CHILDREN
Note: The behavior applies with the setting of trace events.
for example, if I turned on tracing with 10046 than I would get an OPTIMIZER_MISMATCH and another child cursor

OUTLINE_MISMATCH
The outlines do not match the existing child cursor. For example, if a user had created stored outlines previously for this command and they were stored in separate categories (say "OUTLINES1" and "OUTLINES2"), if they then executed the following:
alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;
alter session set use_stored_oulines= OUTLINES2;
select count(*) from emp;
The second execution of the select from emp would create another child since the outline used is different than the first run. This child would be marked as an OUTLINE_MISMATCH.

STATS_ROW_MISMATCH
The existing statistics do not match the existing child cursor. Check that 10046/sql_trace is not set on all sessions as this can cause this.
LITERAL_MISMATCH
Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH
Security level does not match the existing child cursor
EXPLAIN_PLAN_CURSOR
The child cursor is an explain plan cursor and should not be shared. Explain plan statements will generate a new child by default - the mismatch will be this.

BUFFERED_DML_MISMATCH
Buffered DML does not match the existing child cursor

PDML_ENV_MISMATCH
PDML environment does not match the existing child cursor

INST_DRTLD_MISMATCH
Insert direct load 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 cursor).

TYPECHECK_MISMATCH
The existing child cursor is not fully optimized

AUTH_CHECK_MISMATCH
Authorization/translation check failed for the existing child cursor
The user does not have permission to access the object in any previous version of the cursor. A typical example would be where each user has it's own copy of a table

**BIND_MISMATCH
The bind metadata does not match the existing child cursor.
For example, in the following, the definition of the bind variable 'a' has changed between the 2 statements:
variable a varchar2(100);
select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

DESCRIBE_MISMATCH
The type-check heap is not present during the describe for the child cursor

LANGUAGE_MISMATCH
The language handle does not match the existing child cursor

TRANSLATION_MISMATCH
The base objects of the existing child cursor do not match.
The definition of the object does not match any current version. Usually this is indicative of the same issue as "AUTH_CHECK_MISMATCH" where the object is different.

ROW_LEVEL_SEC_MISMATCH
The row level security policies do not match

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

REMOTE_TRANS_MISMATCH
The remote base objects of the existing child cursor do not match. For example:
USER1:select count(*) from <a data-cke-saved-href="/cdn-cgi/l/email-protection" href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>_db
USER2:select count(*) from <a data-cke-saved-href="/cdn-cgi/l/email-protection" href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>_db
Although the SQL is identical, the dblink pointed to by remote_db may be a private dblink which resolves to a different object altogether
LOGMINER_SESSION_MISMATCH

INCOMP_LTRL_MISMATCH

OVERLAP_TIME_MISMATCH
Error_on_overlap_time mismatch
SQL_REDIRECT_MISMATCH
SQL redirection mismatch
MV_QUERY_GEN_MISMATCH
Materialized view query generation
USER_BIND_PEEK_MISMATCH
User bind peeking mismatch
TYPCHK_DEP_MISMATCH
Cursor has type-check dependencies
NO_TRIGGER_MISMATCH
No trigger mismatch
FLASHBACK_CURSOR
No cursor sharing for flashback
ANYDATA_TRANSFORMATION
Anydata transformation change
INCOMPLETE_CURSOR
Incomplete cursor. When bind length is upgradeable (i.e. we found a child cursor that matches everything else except that the bind length is not long enough), we mark the old cursor is not usable and build a new one. This means the version can be ignored.
TOP_LEVEL_RPI_CURSOR
Top level/rpi cursor. In a Parallel Query invocation this is expected behaviour (we purposely do not share)
DIFFERENT_LONG_LENGTH
Different long length
LOGICAL_STANDBY_APPLY
Logical standby apply mismatch
DIFF_CALL_DURN
Different call duration
BIND_UACS_DIFF
Bind uacs mismatch
PLSQL_CMP_SWITCHS_DIFF
PL/SQL compiler switches mismatch
CURSOR_PARTS_MISMATCH
Cursor "parts executed" mismatch
STB_OBJECT_MISMATCH
STB object different (now exists). For explanation of STB_OBJECT_MISMATCH, please read following blog: https://blogs.oracle.com/optimizer/entry/my_cursor_wasn_t_shared
ROW_SHIP_MISMATCH
Row shipping capability mismatch
PQ_SLAVE_MISMATCH
PQ slave mismatch If you encounter this reason code and you are using parallel execution (PX), then check you really want to be using it.
This mismatch can be caused by running lots of small SQL statements which do not really need PX.
Also, if you are on versions prior to 11g you may be hitting Bug:4367986

TOP_LEVEL_DDL_MISMATCH
Top-level DDL cursor
MULTI_PX_MISMATCH
Multi-px and slave-compiled cursor
BIND_PEEKED_PQ_MISMATCH
Bind-peeked PQ cursor
MV_REWRITE_MISMATCH
MV rewrite cursor
ROLL_INVALID_MISMATCH
Rolling invalidation window exceeded. This is caused by the rolling invalidation capability in DBMS_STATS. The child cannot be shared as it's invalidation window is exceeded. See:
Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g
OPTIMIZER_MODE_MISMATCH
Optimizer mode mismatch
PX_MISMATCH
Parallel query execution mismatch. Refer to the following for known issues where this reason is shown:
Document 1629107.1 Common Bugs Associated with PX_MISMATCH

MV_STALEOBJ_MISMATCH
Materialixed View stale object mismatch
FLASHBACK_TABLE_MISMATCH
Flashback table mismatch
LITREP_COMP_MISMATCH
Literal replacement compilation mismatch

---------------
New in 11g :
PLSQL_DEBUG
Debug mismatch Session has debugging parameter plsql_debug set to true
LOAD_OPTIMIZER_STATS
Load optimizer stats for cursor sharing
ACL_MISMATCH
Check ACL mismatch
FLASHBACK_ARCHIVE_MISMATCH
Flashback archive mismatch
LOCK_USER_SCHEMA_FAILED
Failed to lock user and schema
REMOTE_MAPPING_MISMATCH
Remote mapping mismatch
LOAD_RUNTIME_HEAP_FAILED
Runtime heap mismatch
HASH_MATCH_FAILED
Hash mismatch. Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)
---------------
New in 11.2:

PURGED_CURSOR
Cursor marked for purging. The cursor has been marked for purging with dbms_shared_pool.purge

BIND_LENGTH_UPGRADEABLE
Bind length upgradeable and could not be shared because a bind variable size was smaller than the new value being inserted (marked as BIND_MISMATCH in earlier versions).
USE_FEEDBACK_STATS
Cardinality feedback. Cardinality feedback is being used and therefore a new plan could be formed for the current execution.
BIND_EQUIV_FAILURE
The bind value's selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan. For an example, see Document 836256.1. After each execution in the example, run:
select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';
... once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y
As can be seen, the new version is created due to BIND_EQUIV_FAILURE

3.cursor不能共享引起的问题 游标不能共享时，需要重新生成子游标，即发生硬解析，在parent cursor下新增加一个child cursor。parent cursor下child cursor 的总数，就是这个SQL的version count。 多版本引起内存占用/解析时持有相关LATCH时间过长导致LATCH争用、 产生新的执行计划后，执行计划如发生变化可能引起的性能波动、 过多的子游标导致占用大量shared pool内存进而引起ORA-4031错误； 其它异常如bug等 具体这些问题的分析处理是另一个大的话题了，此处就不再多说了。

4.cursor不能共享模拟示例：

实验1：绑定变量分级产生多个子游标

-->对应v$sql_shared_cursor-BIND_LENGTH_UPGRADEABLE列为Y  

create table t11(id number,name varchar2(100));

SQL> alter system flush shared_pool;
System altered.
SQL> variable a varchar2(20);
SQL> exec :a :='bys'
select count(*) from t11 where name = :a;

PL/SQL procedure successfully completed.
SQL>
COUNT(*)
----------
0
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t11%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------- ------------- ------------- ----------
select count(*) from t11 where name = :a 3vj03dwvt5qy2 1 1
SQL> variable a varchar2(40);
SQL> exec :a :='bys'
PL/SQL procedure successfully completed.
SQL> select count(*) from t11 where name = :a;
COUNT(*)
----------
0
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t11%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------- ------------- ------------- ----------
select count(*) from t11 where name = :a 3vj03dwvt5qy2 2 2

SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,child_address,EXECUTIONS,FIRST_LOAD_TIME,trunc(ELAPSED_TIME/1000/1000/60) elas_min,to_char(LAST_ACTIVE_TIME,'yyyymmdd hh24:mi:ss') LAST_ACTIVE_TIME from v$sql where sql_id='3vj03dwvt5qy2';

SQL_ID HASH_VALUE CHILD_NUMBER CHILD_AD EXECUTIONS FIRST_LOAD_TIME ELAS_MIN LAST_ACTIVE_TIME
------------- ---------- ------------ -------- ---------- -------------------- ---------- -----------------
3vj03dwvt5qy2 932371394 0 2D9C221C 1 2016-01-11/07:38:17 0 20160111 07:38:41
3vj03dwvt5qy2 932371394 1 325F8EC8 1 2016-01-11/07:38:17 0 20160111 07:38:41

SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address in('2D9C221C','325F8EC8');

ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
-------- ------------------------------ ---------- ---------- ----------
2D9C221C A 1 1 32
325F8EC8 A 1 1 128
####################
游标不能共享的原因：
SQL> select sql_id,child_address,child_number,sql_type_mismatch,OPTIMIZER_MISMATCH,OUTLINE_MISMATCH,STATS_ROW_MISMATCH,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor where sql_id='3vj03dwvt5qy2';

SQL_ID CHILD_AD CHILD_NUMBER S O O S B B
------------- -------- ------------ - - - - - -
3vj03dwvt5qy2 2D9C221C 0 N N N N N N
3vj03dwvt5qy2 325F8EC8 1 N N N N N Y

----BIND_LENGTH_UPGRADEABLE
VARCHAR2(1) (Y|N) Bind length(s) required for the current cursor are longer than the
bind length(s) used to build the child cursor

实验2：优化器模式不同导致的游标不能共享

-->对应v$sql_shared_cursor-OPTIMIZER_MODE_MISMATCH列为Y

SQL> show parameter optimizer_mo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> alter session set optimizer_mode='first_rows_10';
Session altered.
SQL> show parameter optimizer_mo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS_10
SQL> variable a varchar2(20);
SQL> exec :a :='bys'
PL/SQL procedure successfully completed.
SQL> select count(*) from t11 where name = :a;
COUNT(*)
----------
0
SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,child_address,EXECUTIONS,FIRST_LOAD_TIME,trunc(ELAPSED_TIME/1000/1000/60) elas_min,to_char(LAST_ACTIVE_TIME,'yyyymmdd hh24:mi:ss') LAST_ACTIVE_TIME from v$sql where sql_id='3vj03dwvt5qy2';
SQL_ID HASH_VALUE CHILD_NUMBER CHILD_AD EXECUTIONS FIRST_LOAD_TIME ELAS_MIN LAST_ACTIVE_TIME
------------- ---------- ------------ -------- ---------- -------------------- ---------- -----------------
3vj03dwvt5qy2 932371394 0 2D9C221C 1 2016-01-11/07:38:17 0 20160111 07:38:41
3vj03dwvt5qy2 932371394 1 325F8EC8 1 2016-01-11/07:38:17 0 20160111 07:38:41
3vj03dwvt5qy2 932371394 2 2D8B914C 1 2016-01-11/07:38:17 0 20160111 07:48:35
SQL> select sql_id,child_address,child_number,sql_type_mismatch,OPTIMIZER_MODE_MISMATCH,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE,AUTH_CHECK_MISMATCH,TRANSLATION_MISMATCH
2 from v$sql_shared_cursor where sql_id='3vj03dwvt5qy2';

SQL_ID CHILD_AD CHILD_NUMBER S O B B A T
------------- -------- ------------ - - - - - -
3vj03dwvt5qy2 2D9C221C 0 N N N N N N
3vj03dwvt5qy2 325F8EC8 1 N N N Y N N
3vj03dwvt5qy2 2D8B914C 2 N Y N N N N

--OPTIMIZER_MODE_MISMATCH ---因此优化器参数不同，导致游标不能共享。
OPTIMIZER_MODE_MISMATCH
VARCHAR2(1) (Y|N) Parameter OPTIMIZER_MODE mismatch (for example, all_rows
versus first_rows_1)

实验3：不同用户执行同一SQL语句产生多个子游标

-->对应v$sql_shared_cursor--AUTH_CHECK_MISMATCH,TRANSLATION_MISMATCH 列为Y

$ sqlplus test/test
SQL> create table t11 as select * from bys.t11;
Table created.

SQL> variable a varchar2(20);
SQL> exec :a :='bys'

PL/SQL procedure successfully completed.

SQL> select count(*) from t11 where name = :a;

COUNT(*)
----------
0
SQL> set linesize 180
SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,child_address,EXECUTIONS,FIRST_LOAD_TIME,trunc(ELAPSED_TIME/1000/1000/60) elas_min,to_char(LAST_ACTIVE_TIME,'yyyymmdd hh24:mi:ss') LAST_ACTIVE_TIME from v$sql where sql_id='3vj03dwvt5qy2';

SQL_ID HASH_VALUE CHILD_NUMBER CHILD_AD EXECUTIONS FIRST_LOAD_TIME ELAS_MIN LAST_ACTIVE_TIME
------------- ---------- ------------ -------- ---------- ------------------- ---------- -----------------
3vj03dwvt5qy2 932371394 0 2D9C221C 1 2016-01-11/07:38:17 0 20160111 07:38:41
3vj03dwvt5qy2 932371394 1 325F8EC8 1 2016-01-11/07:38:17 0 20160111 07:38:41
3vj03dwvt5qy2 932371394 2 2D8B914C 1 2016-01-11/07:38:17 0 20160111 07:48:35
3vj03dwvt5qy2 932371394 3 2DAA6A28 1 2016-01-11/07:38:17 0 20160111 07:51:48
SQL> select sql_id,child_address,child_number,sql_type_mismatch,OPTIMIZER_MODE_MISMATCH,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE,AUTH_CHECK_MISMATCH,TRANSLATION_MISMATCH
2 from v$sql_shared_cursor where sql_id='3vj03dwvt5qy2';

SQL_ID CHILD_AD CHILD_NUMBER S O B B A T
------------- -------- ------------ - - - - - -
3vj03dwvt5qy2 2D9C221C 0 N N N N N N
3vj03dwvt5qy2 325F8EC8 1 N N N Y N N
3vj03dwvt5qy2 2D8B914C 2 N Y N N N N
3vj03dwvt5qy2 2DAA6A28 3 N N N N Y Y
-----此次是因为AUTH_CHECK_MISMATCH,TRANSLATION_MISMATCH导致的游标不能共享
AUTH_CHECK_MISMATCH VARCHAR2(1) (Y|N) Authorization/translation check failed for the existing child cursor
TRANSLATION_MISMATCH VARCHAR2(1) (Y|N) The base objects of the existing child cursor do not match

5.参考文档

MOS文档： Troubleshooting: High Version Count Issues (文档 ID 296377.1) VIEW: "V$SQL_SHARED_CURSOR" Reference Note (文档 ID 120655.1)