oracle stalestats_ORACLE高版本问题

关于ORACLE高版本的问题，到底啥是高版本？网上好多帖子上来一堆SQL，搞的晕晕的，一时没搞明白，今天在网上看到David博客做了详解，终于搞明白了，再此感谢David

一：ORACLE高版本到底是怎么个意思呢？？？？

Oracle里的所有SQL 语句都是隐式共享的。 当用户提交了一个SQL语句，RDBMS 会对这个SQL 语句进行hash

运算，然后产生一个hash 值， 再拿这个hash 值与shared pool 里已经存在的SQL 进行hash

值比较，如果相同，就重用之前SQL 的cursor，如果没有，就需要进行硬解析

在硬解析时，Oracle 至少会产生一个parent cursor 和 child cursor， parent cursor

用来保存hash value,child cursor 用来保存SQL语句的metadata。 parent cursor和child cursor都是shared

cursor，它们都是以library cache object handle的方式存在library

cache里。

注意：

parent cursor和child cursor都是library

cache object，它们的结构是一摸一样的.  这个可以通过dump library

cache 来确认。

看下library cache 的一个管理结构图，这几张图是从DSI 里找到的。

Oracle通过Hash buckets来存放相关的对象，如cursor，那么Oracle会把cursor

放到某个具体的buckets里，由Oracle自己的算法来操作。 在每个bucket里面，会有很多的object，Oracle

通过list 的方式将每个bucket里的对象连接起来。

每个Hash bucket 指向一个object Handle，handle 又指向其他的handle，每个Handle

又指向对应的Library Cache Object。 如下图所示。

Library Cache Object是由一些独立的heap所组成， Library

cache handle指向Library cache Object，其实handle是指向第一个heap,这个heap

我们就称之为heap 0。Heap 0记录了指向其他heap的指针信息。

heap0 代表的是parent cursor，它保存的是sql 语句的hash value，同时heap 0 的child

table里保存所有child cursor 的Data Heap地址。 在每个Child

cursor 对应的handle 的Heap 0中的Data Blocks指向的heap

6保存了SQL 对应的执行计划。 可能有些朋友到这就有些不想看了，坚持住，这些图看懂更好，看不懂也不影响理解，重点在下面这句话，：

当SQL 语句第一次执行时，在硬解析时，会创建parent cursor

和child cursor。 这2个是必须的。这2个过程是比较费资源的,也是硬解析里主要做的事,当再次执行这个SQL时，Oracle

会先对SQL语句进行hash 运算，产生一个hash 值，然后用这个HASH 值到buckets里去查找，hash value

存放在parent cursor里。 如果找到了，就去检查child cursor。 如果可以重用这个child

cursor，那么就直接调用cursor里的执行计划。 如果不可重用，就会创建一个新的child cursor。 这个child cursor 的个数，就是version

count。 同parent cursor 对应的child cursor 越多，version count

就越高这就是ORACLE高版本问题的最直观描述

如：

select sql_id,version_count

from v$sqlarea order by 2 desc ;

select address,child_address,sql_text

from v$sql where sql_id='70th7d08hqjf7';

v$sql中通过child_number,hash_value,address来确定一个子游标，而v$sqlarea通过address和hash_value就可以确定一个父游标；而从10g过后，通过sql_id就能确定一个游标

二：为什么child scursor为不同呢？我们下面进行分析

通过如下方法我们来查看具体不能共享的原因：

(1)查看parent

cursor 的hash value 和address：

如：

SQL>selectsql_text,

hash_value,address from v$sqlarea where sql_text like

'selectcount(*) from emp%';

SQL_TEXT HASH_VALUE ADDRESS

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

select count(*) from emp 4085390015 0000000386BC2E58

(2)检查child

cursor

在Oracle 9i 使用如下语句：

SQL>select

* fromv$sql_shared_cursor where kglhdpar = '0000000386BC2E58'

在Oracle 10g 使用如下语句：

SQL>select

* fromv$sql_shared_cursor where address = '0000000386BC2E58'

ADDRESS KGLHDPAR U S O O S L S E B P I S

TA 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 NN 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 NN N N

N N N

通过这个查询结果显示该parent cursor 有2个child

cursor。 显示结果中为Y的就是不能共享的原因。 在这个示例是是因为AUTH_CHECK_MISMATCH

和TRANSLATION_MISMATCH

这些U S O O S L S E B P I S TA B D L T R I I

R L I O S M U T N F 代表什么意思呢？下面是字母对应的含义：

(1)UNBOUND_CURSOR

Theexisting child cursor was not fully built (in other words, it

was notoptimized)

(2)SQL_TYPE_MISMATCH

The SQL type does not match the existing

childcursor

(3)OPTIMIZER_MISMATCH

Theoptimizer environment does not match the existing child

cursor.

For example:

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

Theoptimizer mode has changed and therefore the existing child

cannot be reused,Thesame applies with events - if I turned on

tracing with 10046 than I would getthe OPTIMIZER_MISMATCH again and

a 3rd child.

(4)OUTLINE_MISMATCH

Theoutlines do not match the existing child

cursor，If my user had created stored outlines previously for this

commandand they were stored in seperate categories (say "OUTLINES1"

and"OUTLINES2") running:

for example：

SQL>alter session setuse_stored_outlines =

OUTLINES1;

SQL>select count(*) from emp;

SQL>alter session set use_stored_oulines=

OUTLINES2;

SQL>select count(*) from emp;

--> Would create a 2ndchild as the outline used

is different than the first run.

(5)STATS_ROW_MISMATCH

Theexisting statistics do not match the existing child cursor，Check

that 10046/sql_trace is notset on all sessions as this can cause

this.

(6)LITERAL_MISMATCH

Non-data literal values do not match theexisting

child cursor

(7)SEC_DEPTH_MISMATCH

Security level does not match the existingchild

cursor

(8)EXPLAIN_PLAN_CURSOR

The child cursor is an explain plan cursor

andshould not be shared，Explain plan statements will generate a new

child by default - themismatch will be this.

(9)BUFFERED_DML_MISMATCH

Buffered DML does not match the existing

childcursor

(10)PDML_ENV_MISMATCH

PDMLenvironment does not match the existing child cursor

(11)INST_DRTLD_MISMATCH

Insert direct load does not match the

existingchild cursor

(12)SLAVE_QC_MISMATCH

Theexisting child cursor is a slave cursor and the new one was

issued by thecoordinator ,or, the existing child cursor was issued

by the coordinator andthe new one is a slave cursor.

(13)TYPECHECK_MISMATCH

Theexisting child cursor is not fully optimized

(14)AUTH_CHECK_MISMATCH

Authorization/translation check failed for

theexisting child

cursor， The user does not havepermission to access the object in any

previous version of the cursor. Atypical example would be where

each user has it's own copy of a table

(15)BIND_MISMATCH

Thebind metadata does not match the existing child cursor。

For example:

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

(The bind 'a' has now changed in definition)

(16)DESCRIBE_MISMATCH

Thetypecheck heap is not present during the describe for the child

cursor

(17)LANGUAGE_MISMATCH

Thelanguage handle does not match the existing child cursor

(18)TRANSLATION_MISMATCH

The base objects of the existing child cursordo

not match

The definition of the objectdoes not match any current version.

Usually this is indicative of the sameissue as

"AUTH_CHECK_MISMATCH" where the object is different

(19)ROW_LEVEL_SEC_MISMATCH

The row level security policies do not

match

(20)INSUFF_PRIVS

Insufficient privileges on objects referencedby

the existing child cursor

(21)INSUFF_PRIVS_REM

Insufficient privileges on remote

objectsreferenced by the existing child cursor

(22)REMOTE_TRANS_MISMATCH

The remote base objects of the existing

childcursor 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 adifferent object

altogether.

(23)LOGMINER_SESSION_MISMATCH

(24)INCOMP_LTRL_MISMATCH

(25)OVERLAP_TIME_MISMATCH

error_on_overlap_time mismatch

(26)SQL_REDIRECT_MISMATCH

sql redirection mismatch

(27)MV_QUERY_GEN_MISMATCH

materialized view query generation

(28)USER_BIND_PEEK_MISMATCH

user bind peek mismatch

(28)TYPCHK_DEP_MISMATCH

cursor has typecheck dependencies

(29)NO_TRIGGER_MISMATCH

no trigger mismatch

(30)FLASHBACK_CURSOR

No cursor sharing for flashback

(31)ANYDATA_TRANSFORMATION

anydata transformation change

(32)INCOMPLETE_CURSOR

incompletecursor，When bindlength is upgradeable (i.e. we found a

child cursor that matches everything elseexcept that the bind

length is not long enough). In this case, we mark the oldcursor is

not usable and build a new one. This means the version can

beignored.

(33)TOP_LEVEL_RPI_CURSOR

top level/rpi cursor，In a Parallel Query

invocation thisis expected behaviour (we purposely do not

share)

(34)DIFFERENT_LONG_LENGTH

different long length

(35)LOGICAL_STANDBY_APPLY

logical standby apply mismatch

(36)DIFF_CALL_DURN

different call duration

(37)BIND_UACS_DIFF

bind uacs mismatch

(38)PLSQL_CMP_SWITCHS_DIFF

plsql compiler switches mismatch

(39)CURSOR_PARTS_MISMATCH

cursor-partsexecuted mismatch

(40)STB_OBJECT_MISMATCH

STB object different (now exists)

(41)ROW_SHIP_MISMATCH

rowshipping capability mismatch

(42)PQ_SLAVE_MISMATCH

PQ slave mismatch，Check you want to be using PX

withthis reason code, as the problem could be caused by running

lots of small SQLstatements which do not really need PX. If you are

on < 11i you may behitting Bug:4367986。

(43)TOP_LEVEL_DDL_MISMATCH

top-level DDL cursor

(44)MULTI_PX_MISMATCH

multi-pxand slave-compiled cursor

(45)BIND_PEEKED_PQ_MISMATCH

bind-peeked PQ cursor

(46)MV_REWRITE_MISMATCH

MV rewrite cursor

(47)ROLL_INVALID_MISMATCH

rolling invalidation window exceeded，This is

caused by the rollinginvalidation capability in DBMS_STATS. The

child cannot be shared as it'sinvalidation window is exceeded.

See:Note:557661.1Rolling

Cursor Invalidations with DBMS_STATS in Oracle10g (Doc ID

557661.1)

(48)OPTIMIZER_MODE_MISMATCH

optimizermode mismatch

(49)PX_MISMATCH

parallelquery mismatch，If running11.1.0.6 and RAC see Bug:7352775.Check that if (on each instance)

parallel_instance_groups is set theninstance_groups is set to the

same.

(50)MV_STALEOBJ_MISMATCH

mv stale object mismatch

(51)FLASHBACK_TABLE_MISMATCH

flashbacktable mismatch

(52)LITREP_COMP_MISMATCH

literal replacement compilation mismatch

在Oracle

11g里又添加了如下原因：

(1)PLSQL_DEBUG - debugmismatch

Sessionhas debugging parameter plsql_debug set to true

(2)LOAD_OPTIMIZER_STATS

Load optimizer stats for cursor sharing

(3)ACL_MISMATCH

Check ACL mismatch

(4)FLASHBACK_ARCHIVE_MISMATCH

Flashback archive mismatch

(5)LOCK_USER_SCHEMA_FAILED

Failed to lock user and schema

(6)REMOTE_MAPPING_MISMATCH

Remote mapping mismatch

(7)LOAD_RUNTIME_HEAP_FAILED

Runtime heap mismatch

(8)HASH_MATCH_FAILED

Hash mismatch

三.

V$SQL_SHARED_CURSOR Report by SQLID or Hash Value 脚本

Oracle提供了一个脚本，可以通过sqlid或者hash value

来查看v$sql_shared_cursor的报告。脚本的具体内容，参考MOS： [ID 438755.1].

脚本必须使用SYS 用户来安装和运行，使用与9iR2 , 10gR1, 10gR2,11gR1, 11gR2。

脚本比较长，会作为附件，贴到最后。

运行脚本，如：

SQL>select b.* from v$sqlarea

a,table(version_rpt(a.sql_id)) b where loaded_versions

> =100;

-- Generate reports for all cursors with more than 100 versions

usingHASH_VALUE:

SQL>select b.* from v$sqlarea a

,table(version_rpt(null,a.hash_value)) bwhere

loaded_versions>=100;

-- Generate the report for cursor with sql_id cyzznbykb509s:

SQL>select * from

table(version_rpt('cyzznbykb509s'));

特别需要注意的是：cursor的不可重用，也可能与cursor_sharing参数值设置有关

详细请查看这两位朋友的博客：