oracle 2个以上游标,诊断Oracle high version count(高版本游标)问题

为了查看子游标:

对于Oracle 9.2.x.x及以下版本

SQL>select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58';

对于oracle 10.0.x.x及以上版本

SQL> select * from v$sql_shared_cursor where address = '0000000386BC2E58';

对于oracle 9.2.x.x及以下版本查询的输出如下:

ADDRESS          KGLHDPAR        U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F

---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

可以看到有一个子游标(address: 0000000386BC2D080).mismatch信息都为N因为这是第一个子游标.如果另一个用户运行相同的语句(select count(*) from emp)再次执行上面查询输出如下:

ADDRESS          KGLHDPAR        U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F

---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N

现在可以看到第二个子游标(address: 0000000386A91AA0)且为什么与第一个子游标不能共享(‘Y’表示不匹配).原因如下:

(1) AUTH_CHECK_MISMATCH and (2) TRANSLATION_MISMATCH

这是因为新用户下的emp对象与scott用户下的emp对象不匹配.当不能访问scott用户的对象时且因为在每个用户方案下有一个emp对象而object_id不同所以翻译失败发生了一次mismatch.

在v$SQL_SHARED_CURSOR中给出了不能共享游标的原因

下面介绍一些游标不能共享的原因:

.UNBOUND_CURSOR--现有的子游标没有完全创建(换句话说不能被优化)

.SQL_TYPE_MISMATCH—sql类型与现有的子游标不匹配

.OPTIMIZER_MISMATCH—优化器环境与现有的子游标不匹配

例如:

SQL>select count(*) from emp; ->> 1 PARENT, 1 CHILD

SQL>alter session set optimizer_mode=ALL_ROWS

SQL>select count(*) from emp; ->> 1 PARENT, 2 CHILDREN

因为 optimizer_mode被改变,因为现有的子游标不能被共享

如果使用10046跟踪事件将会得到optimizer_mismatch和第三个子游标

使用cursortrace将会看到更详细的原因比如:

Optimizer mismatch(12)

其中括号内的数字给出了原因

1 = Degree used is not the default DOP

2 = In (RAC) cases where instance count is not the same, or session CPU count is not the same, or thread count is not the same

3 =  _parallel_syspls_obey_force is FALSE

4 = The PQ mode does not match.

5 = The degree does not match.

6 = The parallel degree policy does not match.

7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.

8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit

9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit

10 = Optimizer mode difference

11 =  Materialized View mismatch

12 = Optimizer environment mismatch (ie an optimizer parameter is different)

13 = Cardinality Feedback is use

.OUTLINE_MISMATCH—The outlines do not match the existing child cursor

If my user had created stored outlines previously for this command and they were stored in seperate categories (say "OUTLINES1" and "OUTLINES2") running:-

SQL>alter session set use_stored_outlines = OUTLINES1;

SQL>select count(*) from emp;

SQL>alter session set use_stored_oulines= OUTLINES2;

SQL>select count(*) from emp;

.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:

SQL>variable a varchar2(100);

SQL>select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD

SQL>variable a varchar2(400);

SQL>select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

.DESCRIBE_MISMATCH—The typecheck 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

USER1: select count(*) from table@remote_db

USER2: select count(*) from table@remote_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_msimatch

.SQL_REDIRECT_MISMATCH—sql redirection mismatch

.MV_QUERY_GEN_MISMATCH—materialized view query generation

.USER_BIND_PEEK_MISMATCH—user bind peek mismatch

.TYPCHK_DEP_MISMATCH—cursor has typecheck 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 - plsql compiler switches mismatch

.CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch

.STB_OBJECT_MISMATCH - STB object different (now exists)

.ROW_SHIP_MISMATCH - row shipping capability mismatch

.PQ_SLAVE_MISMATCH - PQ slave mismatch

Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i 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

.OPTIMIZER_MODE_MISMATCH - optimizer mode mismatch

.PX_MISMATCH - parallel query mismatch

If running 11.1.0.6 and RAC see Bug:7352775. Check that if (on each instance) parallel_instance_groups is set then instance_groups is set to the same.

.MV_STALEOBJ_MISMATCH - mv 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

Could not be shared because a bind variable size was smaller than the new value beiing 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

There is no longer  ROW_LEVEL_SEC_MISMATCH in 11.2.

可以进一步跟踪

 在oracle10g及以上版本中可以使用cursortrace来查找游标不能被共享的原因.