SEQUENCE的cache_size过小导致Library cache lock
昨天下午马上下班，测试人员跑过来说应用程序在导入2W条条目数据，页面上一直显示导入中，OEM上显示在导入数据的这段时间，CPU使用明显要高，我过去看了下，不但CPU使用率比往常高，而且很明显有个锁等待信息，看了下对应的ADDM报告，没想到的竟然是Library cache lock，由于马上下班，客户一直催着下班，只好把AWR和ADDM信息带回家分析，而且客户下班，所有客户机断电，现场都保留不下。以下是16：30-17：00（正常情况下）和17：00-17：30（出现Library cache lock）部分AWR对比信息。
导入2W条条目数据的业务流程是应用程序将excel信息解析后的数据INSERT到数据库的某张表里，然后2W次UPDATE将导入的数据和另一张表里的数据关联，下面是ADDM的部分信息。
查找结果 3: 共享池闩锁数
受影响的是 .29 个活动会话, 占总活动的 18.9%。
------------------------------
与共享池相关的闩锁争用将消耗大量数据库时间。
等待 "library cache lock" 的数据库时间总计为 18%。
建议案 1: 应用程序分析
估计的收益为 .29 个活动会话, 占总活动的 18.9%。
-------------------------------
操作
使用指定的阻塞会话或模块调查闩锁争用的原因。
原理
具有 ID 322 和序列号 23659 (在实例号 1 中) 的会话是
构成此建议案中的优化建议的 24% 的阻塞会话。
原理
具有 ID 635 和序列号 19559 (在实例号 1 中) 的会话是
构成此建议案中的优化建议的 15% 的阻塞会话。
原理
具有 ID 680 和序列号 52553 (在实例号 1 中) 的会话是
构成此建议案中的优化建议的 15% 的阻塞会话。
原理
具有 ID 681 和序列号 46651 (在实例号 1 中) 的会话是
构成此建议案中的优化建议的 15% 的阻塞会话。
导致查找结果的故障现象:
------------
等待类 "并发" 消耗了大量数据库时间。
受影响的是 .29 个活动会话, 占总活动的 18.9%。
由于SID和SERIAL#对应的信息已经无法查到，继续看ADDM报告的信息。
查找结果 2: 软语法分析
受影响的是 1.1 个活动会话, 占总活动的 71.63%。
-------------------------------
对 SQL 语句的软语法分析消耗了大量数据库时间。
建议案 1: 应用程序分析
估计的收益为 1.1 个活动会话, 占总活动的 71.63%。
--------------------------------
操作
研究应用程序逻辑, 使经常使用的游标保持打开。请注意,
可以通过游标关闭调用或会话断开连接来关闭游标。
建议案 2: 数据库配置
估计的收益为 1.1 个活动会话, 占总活动的 71.63%。
--------------------------------
操作
考虑通过增加 "session_cached_cursors" 参数的值来
增加会话游标高速缓存大小。
原理
分析期间, 参数 "session_cached_cursors" 的值为 "50"。
由于程序没有使用绑定变量，而数据库的CURSOR_SHARING设置为SIMILAR，导致session_cached_cursors值不够，今天早上到办公室，导入数据操作已经完成，将session_cached_cursors改为3000，应该足够大了，测试人员又导入2W条数据，ADDM又提示session_cached_cursors不够大，查看session_cached_cursors相关信息，但是应该和应用程序没有使用绑定变量有关，一般执行3次的SQL会被放到session_cached_cursors里，这一般不会导致Library cache lock。
SQL> select SQL_ID,SQL_TEXT from v$open_cursor; SQL_ID SQL_TEXT ------------- ---------------------------------- g6gu1n3x0h1h4 insert into t_file_info_raw ...... g6gu1n3x0h1h4 insert into t_file_info_raw ...... g6gu1n3x0h1h4 insert into t_file_info_raw ...... g6gu1n3x0h1h4 insert into t_file_info_raw ...... 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle# 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle# 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle# 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle# ......
由于数据库CURSOR_SHARING设置为SIMILAR，而应用程序没有使用绑定变量，导致SQL_ID相同的SQL也发生大量的解析，现在已经将CURSOR_SHARING改回了EXACT，也在进一步协调开发人员必须使用绑定变量。
在查看v$open_cursor视图信息是发现大量的SYS用户执行的update seq$数据字典操作引起我的注意，都怪我基础知识太差，看到这些信息还较不准问题所在，没办法，给老杨打电话求助，向老杨请教Library cache lock的相关知识，老杨说出现Library cache lock一般是和数据字典竞争有关，我告诉他大量的SYS用户在更新seq$数据字典后，老杨叫我查下程序INSERT时用的SEQUENCE的cache_size是多大，并告诉我这种大量的插入数据，SEQUENCE的cache_size要设置的大一点，开发人员说程序在导入数据时的确有用到SEQUENCE，并告诉我SEQUENCE的名字，查看SEQUENCE的信息。
SQL> select SEQUENCE_NAME, CACHE_SIZE from USER_SEQUENCES where SEQUENCE_NAME =‘S_ALL_INFO_RAW’; SEQUENCE_NAME CACHE_SIZE -------------- ---------- S_ALL_INFO_RAW 10
由于程序导入2W条数据，而SEQUENCE的CACHE_SIZE是10，也就是说SYS用户需要维护2000次seq$数据字典，引起seq$数据字典争用，导致了Library cache lock事件。
将名字为S_ALL_INFO_RAW的SEQUENCE的CACHE改为100。
SQL> alter sequence S_ALL_INFO_RAW cache 100; 序列已更改。
测试人员导入2W条和10W条数据，目前还未出现问题，ORACLE基础知识真的很重要，工作经验也很重要，其实我已经查询到了很多有用的信息，但是没有联系到关键点，还是太嫩，经验和基础知识都不够啊。
SEQUENCE的cache_size过小导致Library cache lock：等您坐沙发呢！
发表评论
