转 如何诊断和解决high version count 10.2.0.4 and 11.2.0.4

转自

http://blog.csdn.net/notbaron/article/details/50927492

 

在Oracle 10g以上的版本，High version count可谓是一个臭名昭著的问题。Hight version count不仅仅产生的原因多种多样，并且会导致各种令人头痛的问题，轻导致数据库的性能急剧下降，CPU利用率剧增，重则导致数据库挂起，触发ORA-04031或者其它bug导致宕机。

什么是verion count，什么是high？

在弄清楚诊断和解决这个问题之前，首先需要清楚什么是version count，什么是high？换而言之就是产生version count的原因，多高的version count才算high。

一个SQL 第一次执行时，会进行硬解析，同时创建parent cursor 和child cursor。

当再次执行这个SQL时，那么首先会对SQL 语句进行特殊的hash 运算，对应生成一个hash value。Hash value存放在parent cursor中，然后会用这个hash value到paranet cursor的bucket中匹配，如果相同的hash value 已存在parent cursor里，则继续遍历这个child cursor，如果可重用，那么就沿用child cursor的信息，如果不能重用，就会重新生成一个新的child cursor。

一个parent cursor下child cursor 的总数，就是这个SQL的version count。

事实上，我们很难去准确定义一个high version count的值，只能根据不同的系统来判断是否为high verison count。在AWR报告中，默认verion count超过20的SQL就会显示在order by version count一栏中。根据经验version count如果超过100，可能就需要引起注意了。

我们可以通过查看v$sqlarea视图的loaded_versions来判断当前这个SQL的version count是多少，然后再通过address的值来查询v$sql_shared_cursor视图看那些字段的返回值为Y，Y代表mismatch。Mismatch是引起产生version count的直接原因。通常我们可以综合：

v$sqlarea, v$sql_shared_cursor, v$sql_bind_metadata, v$sql_bind_captures来诊断这类问题，但是手工去查这些表往往过于繁琐，  Abel Macias 开发了一个小工具叫做version_rpt，这个工具可以用来诊断导致是那个模块出现mismatch，从而导致了high version count。我们可以到High SQL Version Counts – Script to determine reason(s) (DOC ID 438755.1)上下载这个小工具。

Cursortrace和cursordump

当然有时候我们会遇到某些SQL的$sql_shared_cursor所有的字段的结果都为N，但是其version count还是很高的情况，那么这些查询这些视图就不管用了，主要的原因是存在部分bug，可能导致v$sql_shared_cursor的信息不准确。例如：

Bug 12539487 – gv$sql_shared_cursor may not show all reasons to not share a cursor (Doc ID 12539487.8)

所以在10g以上版本可以使用cursortrace来查找high version count的原因。

可以使用以下方式打开cursortrace：

SQL>alter system set events

'immediate trace name cursortrace level 577, address <hash_value>';

其中可以使用三个level，level 1为577， level 2为578， level 3为580（实际上还有其它level，只是没有文档记载）

如需关闭cursortrace，则可以使用以下方式进行关闭：

SQL>alter system set events

'immediate trace name cursortrace level 2147483648, address 1';

或者使用以下方式关闭：

SQL>alter session set events 'immediate trace name cursortrace level 128 , address <address>';

当然也可以通过oradebug的方式来进行cursortrace，这里就不详述了。

注意在10.2.0.4以下版本存在Bug 5555371导致cursortrace无法彻底关闭的情况，最终导致其trace文件不停的增长，从而可能导致oracle文件系统被撑爆的现象。

在实际帮助客户处理查找high version count的原因时，我们发现即使在10.2.0.5以上的版本也可能会出现cursortrace无法彻底关闭的现象。

如果数据库版本在10.2.0.4 以下，生产系统上不建议使用cursortrace ，

生产环境在10.2.0.4 以上版本，建议谨慎使用cursortrace 。例如注意观察，如果无法关闭则通过修改MAX_DUMP_FILE_SIZE 参数限制cursortrace trace 文件的最大大小，或者写一个crontab 的job 定期清理其trace 文件。

在11g中引入cursordump，我们可以使用如下方式进行cursor dump：

alter system set events 'immediate trace name cursordump level 16'

这种方式收集的信息比较全：例如它可以采集到部分别的方式无法看到的px_mismatch以及它会进一步展开optimizer_mismatch的信息等。

在10gR2版本中， 我们更倾向于使用processstate dump和errorstack的信息来替代cursordump，因为processstate dump中也存在cursordump的信息：

先找到high version count SQL对应的spid，然后执行以下SQL：

SQL>oradebug setospid

SQL>oradebug ulimit

SQL>oradebug dump processstate 10

SQL>oradebug dump errorstack 3

一些可能导致问题的SQL和配置

我们通常可以使用AWR和ASH来找出这些可能存在问题的SQL。根据以往的经验，最容易导致出现version count的SQL包括以下类型：（只是列出最常见的写法，并不是很全）

1.       Insert 语句使用绑定变量

Insert into table(column1, column2, column128, …) values (:1,  :2, :3, … :128, …)

尤其是对于某些表字段类型特别多的，并且把这个表所有字段都写到insert语句里面的表现得尤为明显。

2.       select into 语句使用绑定变量

select a, b, c, … into :1,:2,:3 from table1

3.       使用insert … returning 语句

4.       使用一个很长的inlist ，并且里面都是绑定变量

select * from table where column1 in (1,  :2, :3, … :128, …)

5.       SQL 非常长并且里面带有多个绑定变量

如果一个SQL语句特别长，并且使用了绑定变量，那么这样的SQL更倾向于出现high version count。

6.       Dblink 调用SQL 语句，最好不要使用绑定变量

Bug 12320556  High version count for child cursors referencing a remote object due to AUTH_CHECK_MISMATCH

这一类语句其实从应用的层面都比较好改写，改写的思路如下：

1. 对于insert into 或者select into可以考虑不要使用绑定变量。
2. 对于一个字段很多的表，在insert的时候不要把所有字段都列出来
3. 尽量不要使用insert into这样生僻的写法
4. 对于inlist 应该控制其内部绑定变量的个数，如果无法控制，则可以将这些变量存入到一张临时表中，去掉inlist，要用的时候再从临时表获取。
5. 尽量不要写特别长的SQL语句，这种SQL不仅易读性差难于维护，并且很容易导致shared pool的一些争用。
6. 尽量避免在dblink调用的SQL语句中使用绑定变量。

在配置方面， cursor_sharing和Adaptive cursor sharing的设置不当容易导致high version count的问题。

请不要使用cursor_sharing=similar ，这句话再怎么强调都不过分。

ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting (Doc ID 1169017.1)  

在10gR2以上版本，cursor_sharing设置为similar可能会导致各种各样的bug，其中之一就是可能会导致出现大量的不可共享的child cursor，从而引发出high version count的问题。

Oracle在12c已经不支持cursor_sharing=similar, 在11.2.0.3版本，cursor_sharing设置为similar与设置为force的效果相同。

另外cursor_sharing最好设置为exact，在没有经过充分的测试下，不要将其设置为force。因为设置为force同样有一定的几率可能导致high version count。

参见： High Version Count with CURSOR_SHARING = SIMILAR or FORCE (Doc ID 261020.1)

如果SQL 语句的共享性很差，首先要做的应该是对SQL 进行调整，而不是调整cursor_sharing 参数。

在11g中引入的adaptive cursor sharing（ACS）特性。在没有经过充分测试之前，请关闭此特性，这个特性很容易导致high version count的问题。

Bug 12334286  High version counts with CURSOR_SHARING=FORCE (BIND_MISMATCH and INCOMP_LTRL_MISMATCH)

Bug 7213010 – Adaptive cursor sharing generates lots of child cursors

Bug 8491399 – Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype

常见的bug和workaround

在10gR2版本中存在一个臭名昭著并且隐匿得很深的Bug，它们曾经折磨过无数DBA：

Patch 6795880: BATCH JOBS HANG WAITING ON ‘KKSFBC CHILD COMPLETION’

Bug 8575528  Missing entries in V$MUTEX_SLEEP.location

这两个bug本质是同一个问题，只是因为前一个问题修复不彻底，反而导致了后一个问题。它存在的缺陷在于如果high version count的SQL，那么去查找child cursor的过程中效率会非常低，从而导致kksSearchChildList/ kqlfMutexClnFetch这些过程会导致挂起。数据库的等待事件上表现为大量的latch，mutex等待，典型的有latch: library cache lock, kksfbc child completion, cursor: mutex pin S, Latch: Row Cache Objects， library cache: mutex X。而这些等待事件基本都是平时难得一见的奇观。

最终的结果往往有两种：

1. 数据库一直挂起必须手工重启；
2. 数据库挂起一段时间，然后恢复
3. 系统资源耗竭导致宕机
4. 触发出其它的bug例如某ORA-600 [kkssearchchildlist*]或者ORA-07445[kkssearchchildlist*]导致数据库宕机

这个问题号称在10.2.0.5已经修复，但是这个问题在10.2.0.5版本上依然很常见。主要原因有两点：

1. 10.2.0.5虽然包含了修复这个问题的代码，但是默认情况下是不生效的，需要用户手工将”_cursor_features_enabled” = 10。
2. 即使设置了”_cursor_features_enabled” = 10，依然还有遇到KKSFBC CHILD COMPLETION的概率，根本原因还是在于child cursor过多，这一系列的函数调用过程依然过于低效。

遗憾的是如果在10.2.0.5以上版本碰到这个问题，除了按照上文的要求调整SQL或者升级到11gR2以外，从数据库现有的手段几乎无解。

另外一个常见的问题是数据表义使用varchar类的变长字符串类型，而应用可能传入的字符串长度为个位数，也可能传入的字符串长度为四位数。换而言之，就是应用程序传入变量的长度范围过大，导致bind mismatch，最终child cursor不能共享从而重新进行hard parse，这样的结果就导致child cursor的急剧膨胀。

根据

This is due to the bind buffer mismatch of the current child cursor. If oracle is unable to bind the current value to the existing child cursors bind buffer, oracle upgrades the existing child cursor with a high bind buffer. This will force the query to do a hard parse and a new child cursor will be created. The previous one will be marked ‘dont use’. 

这个问题可以通过设置固定的字符串buffer的长度来减少其对应的version count。通过level为4000的10503事件来达到此目的，注意这里的4000为字符串buffer的长度。在SQL中可变字符串varchar2最大的长度为4000。

SQL>alter system set events '10503 trace name context forever, level 4000';

还有一个非常有杀伤力的Bug为

Bug 8981059  High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking

这个Bug几乎影响了所有的10gR2的版本。

这个bug典型的cursor dump信息如下：

KKSCS sharing succeed xsc=1106ac818 childno=3370 reason=BND

Checking for already pinned child. fbcflg 1

Object is invalid

No valid child pinned

Parent 70000065e35bb50(70000065f702598) ready for search

kksSearchChildList outside while loop

kksCheckCursor: next child is #3370

kksCheckCursor: pinning child #3370 in shared mode 70000065e35b960

0000065f41e8f0

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=NFP

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=SQT

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=OPT

...

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=BDM

...

KSCS sharing failed xsc=1106ac818 childno=3370 reason=UBP

kksUnlockChild: releasing child

Failed sharing : 8000000

kksCheckCursor: next child is #3402

kksCheckCursor: pinning child #3402 in shared mode 70000065e35b960

0000065e615f88

KKSCS sharing succeed xsc=1106ac818 childno=3402 reason=NFP

KKSCS sharing succeed xsc=1106ac818 childno=3402 reason=SQT

在堆栈信息中还会找到kksfbc以及 kksLoadChild这样的函数。

这个问题实际上是因为绑定变量窥测导致的child cursor不能共享。并且在某些情况下可能导致查询出来的结果是错误的，也就是我们所说的wrong result。

通常这一类问题在10.2.0.5以上版本可以通过关闭绑定变量窥测来规避：

SQL>alter system set "_optim_peek_user_binds"=false

• 监控version count特别高的语句，如果到了特定的阈值，就将其从shared pool中踢出去，在10.2.0.4和10.2.0.5通过以下方式清除特定的SQL：

SQL>alter session set events '5614566 trace name context forever';

SQL>exec dbms_shared_pool.purge('&address, &hash_value', c);

前面event 5614566设置的目的是为了规避Bug 5614566导致的使用dbms_shared_pool.purge 无法将parent cursor清除出shared pool的问题。

虽然Bug 5614566在10.2.0.5已经修复，但是在某些情况下，依然会遇到dbms_shared_pool.purge的情况。

• 使用dbms_shared_pool.keep这个包将特定high version count的SQL进行keep。

此建议为Bug 10157392  High version counts for SQL with binds (BIND_MISMATCH)给出的一个workaround，这种方法大多数情况都不起作用。

• 定期的对shared pool进行flush。

在某些负载比较低的系统中，可以考虑使用这种方法来防治high version count的问题。在业务繁忙的系统中刷新shared pool存在很大的风险，以来大量被刷出去的SQL需要重新进行硬解析，有可能会导致CPU短时间内的迅速增加。还有就是某些SQL被刷出shared pool以后重新解析其执行计划可能会发生变化，由此容易造成性能的不稳定。

11g的cursor特性的增强

在10gR2中，child cursor的最大上限为32768。Child cursor的数量如果超过了32768，那么这个session就会抛出ORA-600 [17059]的错误，然后这个session就会crash，在10.2.0.4和10.2.0.5上有一个增强补丁：Bug 8946311  Increase max children before reporting ORA-600 [17059]。如果应用了这个patch，那么child cursor的上限就可以提高到65535了。

但是允许child cursor的上限到一个非常大的值并不是什么好主意，只能暂时缓解因为high version count而导致的ORA-00600的错误。但是同时也会引发新的问题，例如最典型的是因为child cursor过多，导致了单个SQL语句占用的shared pool的空间非常大，最终导致ORA-004031.

到了11g，Oracle已经充分意识到high version count是一个很严重的问题，尽管mismatch是客观存在的，但是可以通过其它的一些手段限制child cursor的数量。

Enhancement Request Bug 10187168 : OBSOLETE PARENT CURSORS IF VERSION COUNT EXCEEDS A THRESHOLD   引入了一个隐含参数叫_cursor_obsolete_threshold，此参数用来限制单个parent cursor下child cursor的数量，默认值为100。如果child cursor的数量超过了这个阈值就会cursor obsolescence的特性，也就是说这个时候parent cursor会被废弃，并且同时重新创建一个新的parent cursor。 这样虽然mismatch会继续存在，但是一劳永逸的解决的high version count的问题。这个patch已经集成到11.2.0.3版本。如果低于11.2.0.3版本，除了需要应用这个patch，同时需要设置一下参数：

11.2.0.1：

SQL> alter system set "_cursor_features_enabled"=34 scope=spfile;

SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;

11.2.0.2：

SQL> alter system set "_cursor_features_enabled"=1026 scope=spfile;

SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;

小结

High version count由于产生原因纷繁多样，并且产生的结果表现形式各不相同，再加上其复杂的诊断方法，往往令很多DBA都束手无策，这不能不说是早期产品设计上的一个缺陷。好在从11gR2开始，通过_cursor_obsolete_threshold这个特性，我们很快可以和high version count说再见了。但是探索的过程永远比结果重要，我们收获到的不仅仅是知识，而是一种钻研的精神。

 

 

###############sample:

问题

数据库版本是10.2.0.4 数据库负载很低，5%左右，数据库等待事件正常，

做了一些检查，发现数据库有一些SQL 有很多的SQL 有很多的versiou_count ,10.0.2.4 会有一些BUG 导致version count 过高。j进而导致 libaray change 问题 

1.检查数据库共享sql 情况，SQL 如下：

select sql_id,version_count,sql_text from v$sqlarea where version_count >200;

->1 8n5bcvc2mwjmj 237 insert into

(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:
40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56)

 

2.检查SQL的不能共享的原因，主要是bind_mismatch 不一致

select * from v$sql_shared_cursor where sql_id='8n5bcvc2mwjmj'

1 8n5bcvc2mwjmj C0000006C458E180 C0000006C68E5458 228 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
2 8n5bcvc2mwjmj C0000006C458E180 C000000555C98B70 229 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 N N N N N N N N N N N N N N N N
N N N N N N
3 8n5bcvc2mwjmj C0000006C458E180 C0000006A8ABF2C0 230 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
4 8n5bcvc2mwjmj C0000006C458E180 C0000005C5787B08 231 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
5 8n5bcvc2mwjmj C0000006C458E180 C000000507BC45A8 232 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
6 8n5bcvc2mwjmj C0000006C458E180 C00000062728C9A8 233 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 N N N N N N N N N N N N N N N N
Y N N N N N
7 8n5bcvc2mwjmj C0000006C458E180 C0000006DED66D38 234 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
8 8n5bcvc2mwjmj C0000006C458E180 C000000627282A38 235 N N N N N N N N N
N N N N N Y 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 N
N N N N N N
9 8n5bcvc2mwjmj C0000006C458E180 C000000653E0BDD8 236 N N N N N N N N N
N N N N N Y 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 N
N N N N N N

Y
代表bind_mismatch

 

 

###############################

建议如下：

建议1：

数据库本身是10.2.0.4 的db ,这个版本存在很多未知的问题，强烈建议尽快升级数据库11G。

 

建议2。如何修复BIND_MISMATCH 。可以看看是程序SQL 是否存在以下现象。并且进行调整。

现象1：定义的SQL的字段长度不一样，如下

**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

 

现象2：

最容易导致出现version count的SQL包括以下类型：

Insert 语句使用绑定变量

Insert into table(column1, column2, column128, …) values (:1, :2, :3, … :128, …)

尤其是对于某些表字段类型特别多的，并且把这个表所有字段都写到insert语句里面的表现得尤为明显。

SQL 非常长并且里面带有多个绑定变量

如果一个SQL语句特别长，并且使用了绑定变量，那么这样的SQL更倾向于出现high version count。

 

 

解决方法：

这一类语句其实从应用的层面都比较好改写，改写的思路如下：

对于insert into 或者select into可以考虑不要使用绑定变量。
对于一个字段很多的表，在insert的时候不要把所有字段都列出来

这2种方案都需要经过在测试环境进行测试，才能在生产环境部署。

 

 

 

 

 

 

 

 

################Bug 7122093 : LIBRARY CACHE CHILD LATCH HELD FOR EXCESSIVE TIME IF SCAN X$KGLDP

dr: 7122093 10.2.0.3 RDBMS 10.2.0.3 LIB CACHE PRODID-5 PORTID-23
Abstract: LIBRARY CACHE CHILD LATCH HELD FOR EXCESSIVE TIME IF SCAN X$KGLDP

*** 05/23/08 01:57 pm ***
Logged out of bug 7116507 for clarity
The customer issue involves a third party monitoring tool.
The test here is simplified just to show the underlying problem.
=========================
PROBLEM:
Under certain conditions a scan of the X$KGLDP fixed table
can cause very long latch hold times of a child library cache latch
causing an instance wide hang.
In particular if a cursor has a high version_count in V$SQLAREA
ie; has a lot of child cursors, and if it depends on a lot of
other objects.

####################
Bug 7122093 - 'latch: library cache' contention caused by queries on V$ views. (文档 ID 7122093.8)

Description

Querying [G]V$ views based on X$KGLDP can hold library cache child latches for
too long potentially causing 'latch: library cache' contention.

 

 

###############################
Troubleshooting: High Version Count Issues (文档 ID 296377.1)

**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

 

###ORACLE 10G

http://blog.csdn.net/notbaron/article/details/50927492

 

在10gR2以上版本，cursor_sharing设置为similar可能会导致各种各样的bug，其中之一就是可能会导致出现大量的不可共享的child cursor，从而引发出high version
count的问题。

Oracle在12c已经不支持cursor_sharing=similar, 在11.2.0.3版本，cursor_sharing设置为similar与设置为force的效果相同。

另外cursor_sharing最好设置为exact，在没有经过充分的测试下，不要将其设置为force。因为设置为force同样有一定的几率可能导致high version count。

参见： High Version Count with CURSOR_SHARING = SIMILAR or FORCE (Doc ID 261020.1)

如果SQL 语句的共享性很差，首先要做的应该是对SQL 进行调整，而不是调整cursor_sharing 参数。

在11g中引入的adaptive cursor sharing（ACS）特性。在没有经过充分测试之前，请关闭此特性，这个特性很容易导致high version count的问题。

Bug 12334286 High version counts with CURSOR_SHARING=FORCE (BIND_MISMATCH and INCOMP_LTRL_MISMATCH)

Bug 7213010 – Adaptive cursor sharing generates lots of child cursors

Bug 8491399 – Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype

常见的bug和workaround

在10gR2版本中存在一个臭名昭著并且隐匿得很深的Bug，它们曾经折磨过无数DBA：

Patch 6795880: BATCH JOBS HANG WAITING ON ‘KKSFBC CHILD COMPLETION’

Bug 8575528 Missing entries in V$MUTEX_SLEEP.location

这两个bug本质是同一个问题，只是因为前一个问题修复不彻底，反而导致了后一个问题。它存在的缺陷在于如果high version count的SQL，那么去查找child cursor的
过程中效率会非常低，从而导致kksSearchChildList/ kqlfMutexClnFetch这些过程会导致挂起。数据库的等待事件上表现为大量的latch，mutex等待，典型的有latch:
library cache lock, kksfbc child completion, cursor: mutex pin S, Latch: Row Cache Objects， library cache: mutex X。而这些等待事件基本都是平时难得一
见的奇观。

最终的结果往往有两种：

数据库一直挂起必须手工重启；
数据库挂起一段时间，然后恢复
系统资源耗竭导致宕机
触发出其它的bug例如某ORA-600 [kkssearchchildlist*]或者ORA-07445[kkssearchchildlist*]导致数据库宕机
这个问题号称在10.2.0.5已经修复，但是这个问题在10.2.0.5版本上依然很常见。主要原因有两点：

10.2.0.5虽然包含了修复这个问题的代码，但是默认情况下是不生效的，需要用户手工将”_cursor_features_enabled” = 10。
即使设置了”_cursor_features_enabled” = 10，依然还有遇到KKSFBC CHILD COMPLETION的概率，根本原因还是在于child cursor过多，这一系列的函数调用过程依然
过于低效。
遗憾的是如果在10.2.0.5以上版本碰到这个问题，除了按照上文的要求调整SQL或者升级到11gR2以外，从数据库现有的手段几乎无解。

另外一个常见的问题是数据表义使用varchar类的变长字符串类型，而应用可能传入的字符串长度为个位数，也可能传入的字符串长度为四位数。换而言之，就是应用程序
传入变量的长度范围过大，导致bind mismatch，最终child cursor不能共享从而重新进行hard parse，这样的结果就导致child cursor的急剧膨胀。

根据

This is due to the bind buffer mismatch of the current child cursor. If oracle is unable to bind the current value to the existing child cursors bind
buffer, oracle upgrades the existing child cursor with a high bind buffer. This will force the query to do a hard parse and a new child cursor will
be created. The previous one will be marked ‘dont use’.

这个问题可以通过设置固定的字符串buffer的长度来减少其对应的version count。通过level为4000的10503事件来达到此目的，注意这里的4000为字符串buffer的长度。
在SQL中可变字符串varchar2最大的长度为4000。

SQL>alter system set events '10503 trace name context forever, level 4000';
还有一个非常有杀伤力的Bug为

Bug 8981059 High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking

这个Bug几乎影响了所有的10gR2的版本。

这个bug典型的cursor dump信息如下：

KKSCS sharing succeed xsc=1106ac818 childno=3370 reason=BND

Checking for already pinned child. fbcflg 1

Object is invalid

No valid child pinned

Parent 70000065e35bb50(70000065f702598) ready for search

kksSearchChildList outside while loop

kksCheckCursor: next child is #3370

kksCheckCursor: pinning child #3370 in shared mode 70000065e35b960

0000065f41e8f0

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=NFP

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=SQT

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=OPT

...

KSCS sharing succeed xsc=1106ac818 childno=3370 reason=BDM

...

KSCS sharing failed xsc=1106ac818 childno=3370 reason=UBP

kksUnlockChild: releasing child

Failed sharing : 8000000

kksCheckCursor: next child is #3402

kksCheckCursor: pinning child #3402 in shared mode 70000065e35b960

0000065e615f88

KKSCS sharing succeed xsc=1106ac818 childno=3402 reason=NFP

KKSCS sharing succeed xsc=1106ac818 childno=3402 reason=SQT
在堆栈信息中还会找到kksfbc以及 kksLoadChild这样的函数。

这个问题实际上是因为绑定变量窥测导致的child cursor不能共享。并且在某些情况下可能导致查询出来的结果是错误的，也就是我们所说的wrong result。

通常这一类问题在10.2.0.5以上版本可以通过关闭绑定变量窥测来规避：

SQL>alter system set "_optim_peek_user_binds"=false
监控version count特别高的语句，如果到了特定的阈值，就将其从shared pool中踢出去，在10.2.0.4和10.2.0.5通过以下方式清除特定的SQL：
SQL>alter session set events '5614566 trace name context forever';

SQL>exec dbms_shared_pool.purge('&address, &hash_value', c);
前面event 5614566设置的目的是为了规避Bug 5614566导致的使用dbms_shared_pool.purge 无法将parent cursor清除出shared pool的问题。

虽然Bug 5614566在10.2.0.5已经修复，但是在某些情况下，依然会遇到dbms_shared_pool.purge的情况。

使用dbms_shared_pool.keep这个包将特定high version count的SQL进行keep。
此建议为Bug 10157392 High version counts for SQL with binds (BIND_MISMATCH)给出的一个workaround，这种方法大多数情况都不起作用。

定期的对shared pool进行flush。
在某些负载比较低的系统中，可以考虑使用这种方法来防治high version count的问题。在业务繁忙的系统中刷新shared pool存在很大的风险，以来大量被刷出去的SQL
需要重新进行硬解析，有可能会导致CPU短时间内的迅速增加。还有就是某些SQL被刷出shared pool以后重新解析其执行计划可能会发生变化，由此容易造成性能的不稳定
。

 

 

 

#############sample 2

select distinct(bind_equiv_failure) from v$sql_shared_cursor where sql_id='6t2grcwkfpjnt'

select * from v$sql_shared_cursor where sql_id='6t2grcwkfpjnt'

 

SELECT sql_id,count(*) FROM v$sql_bind_capture group by sql_id having count(*) > 100

select sql_id,count(*) from v$sql group by sql_id having count(*) > 100

 

 

workaroud:

Disable Extended Cursor Sharing (ECS) and Adaptive Cursor Sharing (ACS) by setting:

alter system set "_optimizer_extended_cursor_sharing"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_adaptive_cursor_sharing"= false scope=both;

and restarting the instance afterwards. This is the workaround for bugs 15881004, 14033503 and 14772891. There is no performance impact.

 

 

 

Bug 7330694 : TOO MANY VERSION OF THE QUERIES, CAUSING SHARED_POOL CONTENTION

ALTER System SET EVENTS '10503 trace name context forever, level 4000';

 

 Extract the Values of Bind Variables used by all the SQL Cursors in a Stored Procedure from the Data Dictionary (文档 ID 1351206.1)

 

 

 ####sample 3

##监控SQL区使用量超过5G的shared_pool 的数据库
select * from V$SGASTAT where pool='shared pool' and name='SQLA' AND bytes/1024/1024/1024 > 5

###监控硬解析高于5000的非绑定变量的SQL
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 5000
order by 2;

### 如果监控出现SQL 该如何查出SQL具体内容

shared pool现有内存24G，还是产生大量硬解析，那就是说明不是因shared pool size设置不当所致，那就是非常有可能是由于程序代码所致。
5. 调研分析是那些代码导致hard parse，从awr的sql order by executions上看，最有可能是以下语句造成的：

step 1. 进一步确认，找出硬解析最多的SQL语句如下：
select to_char(force_matching_signature),count(*) hard_parses
from v$sqlarea
group by to_char(force_matching_signature)
having count(*)>5
order by 2 desc;

step 2:
select * from v$sqlarea
where force_matching_signature=7756258419218828704;

 

## 感谢  mlsama ，有的时候会force_matching_signature 显示科学数字，9.35311222726675E16，这个时候使用to_char 转换下即可

Oracle的number类型的字段值显示科学计数值

mlsama 2019-04-25 15:22:27 289 收藏
展开
最近做需求建了几张表，主键都是number类型的，在sqlplus中查询的时候，发现主键值进行了科学计数的显示，这看起来非常的奇怪。网上找了下方法，记录如下：

    原因：

         SQLPlus在显示number类型时，小于等于10位的精度显示的是很直观的形式，大于10位精度的则显示为科学计数的形式。

    解决方法：

        将number转成char类型，设置一下即可：tools->preferences->sql windows->number fields tochar,选中该选项
————————————————
版权声明：本文为CSDN博主「mlsama」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/mlsama/java/article/details/89516837

step 3:
整合step1/step 2后：
select b.*,a.* from v$sqlarea a,(
select force_matching_signature,count(*) hard_parses
from v$sqlarea
group by force_matching_signature
having count(*)>5) b
where A.FORCE_MATCHING_SIGNATURE=b.force_matching_signature
and b.force_matching_signature >0
order by 2 desc;

 

step 4:  重新整合step 1/step 2, 如果有多个SQL,可以使用如下语句整合

 

DECLARE
T VARCHAR2(50);
v_signature VARCHAR2(50);
v_no NUMBER(5);
v_sqltext VARCHAR2(2500);
cursor STSCUR is select force_matching_signature,count(*) from v$sqlarea group by force_matching_signature having count(*)>1000;
STSCUR_1 v$sqlarea%ROWTYPE;
BEGIN
--for ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
DBMS_OUTPUT.ENABLE (buffer_size=>null);
OPEN STSCUR;
LOOP
fetch STSCUR into v_signature,v_no;
EXIT WHEN STSCUR%NOTFOUND;
-- DBMS_OUTPUT.PUT_LINE(T);
--commit;
-- DBMS_OUTPUT.PUT_LINE(STSCUR_1.SQL_ID);
select sql_text into v_sqltext from v$sqlarea where force_matching_signature=v_signature and rownum < 2;
DBMS_OUTPUT.PUT_LINE(v_signature||','||v_no||','||v_sqltext);
--换行
dbms_output.new_line;
END LOOP;
CLOSE STSCUR;
END;

 

判断硬解析的重要指标force_matching_signature，当该指标具有相同值时，就是说明采用同一语句，只是条件值不同，在cursor_sharing=force的情况下是可以共享同一计划，在exact下不能，必须显式使用绑定变量方式。
旧办法：

 

col sqltext format a50
select dbms_lob.substr(sql_text,20,1 ) sqltext,count(*)
from dba_hist_sqltext
group by dbms_lob.substr(sql_text,20,1 )
having count(*)>=2
order by 2;
7. 找出问题语句，改造为绑定变量方式。
解决方法：
1. 找出问题的语句，改为绑定变量的方式：
2. 绑定变量的使用如下：
plsql:
execute immediate "select * from t1 where col1=:1" using v_col1;
Java方式：
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();

补充备注与shared pool使用相关的系统表：
select * From SYS.V_$SHARED_SERVER_MONITOR;

select * from SYS.V_$SHARED_POOL_RESERVED;

select * from SYS.V_$LIBRARYCACHE;

select * from SYS.V_$LIBRARY_CACHE_MEMORY;

select * from SYS.V_$SGASTAT where pool='shared pool'
order by 3 desc;

 

##############

将指定SQL_ID 的执行计划 flush出shared pool

2015年04月08日 09:22:26 Pdata 阅读数：470 标签： flush执行计划shared更多

个人分类： ORACLE

 

在调试某特定SQL时，为避免缓存带来的影响，常常需要先将原来 的缓存清空而需要使用下面命令：

SQL>ALTER SYSTEM FLUSH SHARED_POOL；

• 1

但是在生产系统中，使用上面命令对系统整体会有一个较大影响，如何将特定的SQL刷出缓存？

SQL>SELECT address,
           hash_value,
           executions,
           parse_calls
    FROM   v$sql
    WHERE  sql_id='0g9au25ax7mx8';

SQL>exec dbms_shared_pool.purge('0000001053A9EF20,1439944616','C');

######ref 

https://blog.csdn.net/tuning_optmization/article/details/46695257

oracle11g中SQL优化（SQL TUNING）新特性之Adaptive Cursor Sharing (ACS)

 

1.   ACS简介

Oracle Database 11g提供了Adaptive Cursor Sharing (ACS)功能，以克服以往不该共享的游标被共享的可能性。ACS使用两个新指标：sensitivity and bindawareness来实施该特点。

2.   ACS机制

2.1.  Adaptive Cursor Sharing Metadata：Oracle 11g也提供了三个新视图和动态视图V$SQL的两个新列来允许DBA来确定优化器是否已经确定一个SQL语句为一个ACS的候选，并且，也可以通过它们来观察优化器分类SQL语句用以共享的执行计划所使用的业务规则：

视图	描述
V$SQL	Two new columns are added: IS_BIND_SENSITIVE indicates if a SQL statement is bind-sensitive. If this column contains a value of (Y)es, it means that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity. Likewise, IS_BIND_AWARE indicates if the optimizer has also decided that the statement’s cursor is bind-aware after additional execution of the statement.
V$SQL_CS_HISTOGRAM	Distributes the frequency (within a three-bucket histogram) at which Oracle 11g used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.
V$SQL_CS_SELECTIVITY	Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. These values are also termed the cursor’s selectivity cube.
V$SQL_CS_STATISTICS	Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The PEEKED column will display a value of Y(es) if the bind set had been used to build the Adaptive Cursor.

表-1 ACS 视图

2.2.  Bind Sensitivity：当带有绑定变量的SQL语句首次被解析时，在优化器窥探了绑定变量的值，并确定了语句谓词的相关选择率后，把该游标标记为 bind-sensitive（绑定敏感的）。期间也保留了这些敏感测量值，以便今后带有同样变量、不同值的同样语句再次执行时进行比较，看一个已经存在的执行计划是否能被新绑定变量值的语句利用。

2.3.  Bind Awareness：一旦一个SQL语句的游标被标为 bind-sensitive，优化器也可以确定游标是bind-aware。通过检查随后执行的同样SQL语句绑定变量的值和所有匹配计划已被捕获的绑定变量的值，优化器完成这个步骤。如果优化器确定该语句能利用已存在的计划，那么，只需要更新游标执行柱状图来反应语句的执行即可。另外，如果绑定变量值足够不同，优化器也许决定创建一个新的子游标和执行计划。一旦这些发生，Oracle11g也把子游标的相关选择率存储到ACS元数据中。在游标随后的执行过程中，优化器比较存在的统计选择性数据和游标最近执行的统计数据，如果观察到大多数执行使用近似同样的选择性范围，那么，游标将会被标记为bind-aware。

当查询以一套超出一个已存在的bind-aware的游标绑定变量的选择率范围界限的不同的绑定变量值被执行时会发生什么呢？在该语句的硬解析期间，优化器也许仅仅决定扩展那个游标的选择率范围来包含新的变量值，通过产生一个合并两套绑定变量值的新游来做到这点。可见在必要的时候，也仅仅是增加了新游标。Oracle11g中，ACS特点默认被开启，且完全独立于CURSOR_SHARING参数。

2.4.  对SQLPlan Management (SPM)影响：最后，如果你了解古Oracle11g的SPM特点，你可能想知道ACS和SPM是否会相互影响，下面做一些简短的总结：

如果初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES已被设置为TRUE来开启自动计划捕获，那么，带绑定变量的一个SQL语句将被标作对应的能用和可接受的执行计划。

如果同样语句的第二个执行计划被创建——对ACS特点来说这并不新鲜——那么，计划仅仅被增加到语句的计划历史里，但并能被立刻使用，因为SPM要求新执行计划首先被验证为一个较好的计划。

不幸的是，这意味着一个好的计划也许被忽视掉，哪怕是其游标的选择性范围可能会导致一个较好的性能。绕过该问题的一个很好的办法是保持自动计划捕获为默认设置False，接着把所有library cahce里的子游标都捕获到SMB里去。这将会迫使ACS产生的游标的所有计划都被标为SQL PLAN BASELINES。

3.   限制

Oracle11gR2为止，ACS特性存在以下限制(当以下场景出现时，会导致ACS不会把游标标记为bind sensitive)：

Ø  扩展游标共享被关闭；

Ø  查询中没绑定变量；

Ø  某些参数被设置（例如：绑定变量窥探被置为flase）；

Ø  语句正使用并行查询时；

Ø  语句使用了hints；

Ø  Outlines正被使用；

Ø  查询为递归查询；

Ø  绑定变量数超过14；

4.   关闭及开启

Ø  ACS默认情况下是开启的，为了关闭ACS，我们需要修改以下三个参数：

alter system set"_optimizer_extended_cursor_sharing_rel"=none;

 altersystem set "_optimizer_extended_cursor_sharing"=none;

 altersystem set "_optimizer_adaptive_cursor_sharing"=false;

Ø  为了开启ACS，我们需要修改以下四个参数：

_optim_peek_user_binds=true（一定要开启绑定变量窥视）_optimizer_adaptive_cursor_sharing=TRUE（以下三个参数默认开启ACS）_optimizer_extended_cursor_sharing=UDO

_optimizer_extended_cursor_sharing_rel=SIMPLE

5.   注意：

Ø  相关hint：Oracle11g中有个新的 hint，当使用此hint时，即使把ACS特性关掉，ACS特性在语句级依然会生效，该hint的语法为：/*+ BIND_AWARE*/；

Ø  关于Outlines：在Outlines存在的场景下，无论在系统级启用还是语句级通过hint启用，ACS都会失效；

6.   结论：

Oracle Database 11g的新特点ACS功能为带绑定变量的SQL语句执行计划高效共享提供了一个简单的方法。由于只有当绑定变量值选择率必要时，ACS也会产生一个新的执行计划，因此，共享游标的数目会保持最小。

 

--------------------- 本文来自 lhdz_bj 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/tuning_optmization/article/details/46695257?utm_source=copy 

 

######ref 2  cat bind sql value

To see a colleague wrote a SQL bound variables are interesting, again to reprint:

--Find the last word values in SQL_ID
select * from v$sql where sql_id='0z25ncfbunafk'
--Bind variable transformation corresponding value
SELECT position,value_string
FROM TABLE(DBMS_SQLTUNE.EXTRACT_BINDS('BEDA0A20050052366ECE000307F0012003540CB9E2CFCBBDD3C8EBBACFCDACF0021604C32C6256F0012003540CB9E2CFCBBDD3C8EBBACFCDAC'));
--If there is no v$sql, can obtain the bind variable values by this method
select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 6).value_string
from sys.wrh$_sqlstat
where sql_id = '0z25ncfbunafk'
--To view the corresponding SQL 
SELECT * FROM SYS.WRH$_SQLSTAT WHERE SQL_ID='0z25ncfbunafk';
SELECT * FROM SYS.WRH$_SQL_BIND_METADATA WHERE SQL_ID='0z25ncfbunafk';
select t.sql_id,t.name,t.position,t.datatype_string,t.value_string,t.last_captured from v$sql_bind_capture t where sql_id='f78cpkf8cc003';

 

###no data in sql_bind:

he value for the bind :B that is included in the select list will not be populated in V$SQL_BIND_CAPTURE:

For Example:

 
SQL> var A number
SQL> var B varchar2(32)
SQL> exec :A := 7902;

PL/SQL procedure successfully completed.

SQL> exec :B := 'Emp No.';

PL/SQL procedure successfully completed.

SQL> select :B, empno from emp where empno=:A;

:B                                    EMPNO
-------------------------------- ----------
Emp No.                                7902

SQL> SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='0mutxd0a9c3r7';

NAME      POSITION      DATATYPE_STRING      VALUE_STRING
-----     --------      ---------------      ------------
:B        1             VARCHAR2(32)
:A        2             NUMBER               7902

CAUSE

This is expected behavior. Binds that are part of the SELECT list are not populated in V$SQL_BIND_CAPTURE since these values are not used by the optimizer when deciding on the execution plan (bind peeking) .

SOLUTION

 There is no solution since this behavior is expected.