近期遇到一套windowsX64+Oracle11.2.0.1版本数据库，遇到一条使用了6个绑定变量值的插入sql出现了5000多个sql version,正常情况下此sql在早上8-9点要执行300-500次左右，当应用程序反应慢时，查看此sql version达到5000多；用户前一段时间在应用反应慢时，即通过重启数据库来解决此问题(用户并没有查找原因，单纯的用重启大法来试一次的)，只是近期此问题出现频率较高，达到了隔一天需要重启一次。用户很着急，需要找到root cause并提出可靠的解决方案。    

    1.alert日志分析重启信息

接到此case后，首先远程获取了数据库的 alert日志，通过日志可以分析出近期数据库重启的时间点。通过搜索Starting ORACLE instance关键字，可以发现近期有如下重启记录：

05/02重启记录
Tue May 02 09:23:11 2017
Starting ORACLE instance (normal)
05/04重启记录
Thu May 04 09:13:36 2017
Starting ORACLE instance (normal)
04/27重启记录
Thu Apr 27 08:59:26 2017
Starting ORACLE instance (normal)

    2.重启前AWR/ASH报告分析

根据重启记录，来获取重启前的AWR/ASH报告信息，来辅助分析当时数据库的性能问题。

在数据库AWR中主要分析了多次重启前AWR中的如下信息，发现均是SQL_ID为22j335m33haq6的SQL在当时出现SQL版本多达5000多个，执行次数为0，同时占用较多的cpu time.与正常时刻同时段AWR对比，此SQL应该执行500次左右，与应用软件确认，此SQL确实是业务需要的，无法执行会导致业务执行不下去。对比重启后当天多个时间段的AWR，可以发现此SQL的version count不断上涨，上午重启的数据库，11-12点AWR中大约为1000多个version count，到16-17点已经增加到3000多个；基本确定是此问题导致应用反应慢，从而重启数据库。如下为AWR中的部分信息，相关库名、表名已经做处理。

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

TEST1

1234563

test1

1

02-5月 -17 09:05

11.2.0.1.0

NO

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

TEST

Microsoft Windows x86 64-bit

16

8

1

127.91

 

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

4594

04-5月 -17 08:00:08

134

9.0

End Snap:

4595

04-5月 -17 09:00:22

176

12.6

Elapsed:

 

60.23 (mins)

 

 

DB Time:

 

66.89 (mins)

 

 

SQL ordered by CPU Time部分信息：

CPU Time (s)

Executions

CPU per Exec (s)

%Total

Elapsed Time (s)

%CPU

%IO

SQL Id

SQL Module

SQL Text

167.59

 

 

6.76

175.16

95.68

 

22j335m33haq6

 

insert into TAB1...

121.43

7

17.35

4.90

143.38

84.69

0.00

0cg9rcdhth5g1

ORACLE.EXE

SELECT "A1".",...

116.39

1

116.39

4.69

360.43

32.29

0.00

3acc5vt9hy5an

 

DECLARE job BINARY_INTEGER := ...

SQL ordered by Sharable Memory部分信息：

Sharable Mem (b)

Executions

% Total

SQL Id

SQL Module

SQL Text

107,972,000

 

1.09

22j335m33haq6

 

insert into TAB1...

94,107,328

 

0.95

934hswduksnzr

 

update TAB set B2,...

SQL ordered by Version Count部分信息：

Version Count

Executions

SQL Id

SQL Module

SQL Text

5,518

 

22j335m33haq6

 

insert into TAB1...

3,406

 

134q4yp2jdqh7

 

insert into tab2...

1,466

 

510d5hzgjw63d

 

update TAB3 set RE...

1,369

 

4vs91dcv7u1p6

 

insert into sys.aud$( sessioni...

1,341

 

d3cj1bqhzcfym

 

update tab4 set...

1,060

 

f711myt0q6cma

 

insert into sys.aud$( sessioni...

 

 3.解决方法：

1.建议协调应用软件开发商调整此INSERT SQL，改为不使用绑定变量插入数据(使用绑定变量的insert语句确实有较大概率出现SQL多版本的问题)，来缓解此问题。
2.设置定时任务，每天在业务运行前刷新一次shared_pool(如早上6点)，来缓解此问题。
3.当前数据库版本为11.2.0.1版本，这是11gR2的基础版本，存在着较多的软件bug等，建议升级到11.2.0.4版本。

这里最终使用的是定时刷共享池的方法，设置了定时任务每天早上6点刷共享池，同时多版本的SQL还有审计记录的语句也一并关闭audit参数等设置，目前已经正常运行一周多。

 

4.SQL多版本问题参考文档：

SQL多版本相对来说比较复杂，特别是在11.2.0.1版本，新特性与bug交织，如自适应游标共享等，不太方便问题排查，一般简单粗暴方法就是定时刷共享池(高并发生产环境需慎重做好评估)或找出容易出现多版本的语句由应用软件来配合做一些修改，当然在11gr2还有一个隐含参数_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(Bug 10187168)，同时需要设置的相关参数在文档文档296377.1中也有说明，如有需要可以进行设置。
同时MOS上提供了判断SQL多版本原因的脚本，可以快速、全面的收集SQL出现多版本的原因(cursor不能共享的原因，上一篇blog里就在写cursor不能共享的事情)。

SQL多版本问排查可以参考如下MOS文档：
Troubleshooting: High Version Count Issues (文档 ID 296377.1)
SQL 版本数过高 – 原因判断脚本 (文档 ID 1985045.1)