1.问题描叙

记录一次hard parse引起enq: TX - row lock contention的处理过程

2. AWR报告

DB time:所有前台会话在数据库中花费的时间总和,不包括Oracle后台进程消耗的时间
Elapsed:采样事件周期
DB time远大于 Elapsed time，说明遭遇了严重的性能问题。
notice:在解释 AWR 报告时尽早弄清楚数据库负载极其重要。

2.1 load profile

Hard parses占到parses的37%权重

2.2 Instance Efficiency Percentages(Target 100%)

parse CPU to Parse Elapsed % 真正在cpu上做功时间只有整个分析时间48.07%，说明其他时间在等待什么

2.3 Time Model Statistics

Hard parse elapsed time占到db time的15%

2.4 Instance Activity Stats

parse count(hard)/parse count(total)=0.37,也说明了系统中存在严重的hard parse

2.5 Top 5 Timed Events

存在严重的enq: TX - row lock contention等待

继续深挖下去，enq: TX - row lock contention是被大量的latch:shared pool与latch:library cache阻塞

SELECT SESSION_ID,EVENT FROM dba_hist_active_sess_history  WHERE SESSION_ID IN 
(select BLOCKING_SESSION
from dba_hist_active_sess_history where  to_char(sample_time,'YYYY-MM-DD HH24') BETWEEN '2023-07-31 07' AND '2023-07-31 10' AND EVENT='enq: TX - row lock contention')

latch:shared pool,这个latch 用于保护free chunk链,
latch:library cache,这是一个hash bucket 链闩锁,当sql执行时，首先会被hash,然后获得对应bucket的latch:libary cache,于对应bucket中查找是否存在同样的hash value,如果存在，释放latch,重用sql及执行计划，否则进行hard parse，hard parse需要创建parent cursor与child cursor,这些都需要从share pool获取free chunk,这也是latch:shared pool出现的原因
所以，可以合理怀疑，enq: TX - row lock contention是由于大量的hard parse所牵累

2.6 查询hard parse

先使用简单的sql获取parse_calls=1 and executions=1的sql语句

SELECT sql_id,executions,version_count,SQL_FULLTEXT  FROM V$SQLAREA WHERE PARSE_CALLS=1 AND EXECUTIONS=1 

发现很多只执行1次使用常量的update语句，这也是为何enq: TX - row lock contention大量出现的原因，并且在繁忙的系统中由于update 语句没有绑定变量，导致hard parse出现，每次update时都要等待hard parse完成，而这个update 又阻塞了其他的update,最后形成恶性循环

3. 解决方案：

3.1.修改程式，使用绑定变量

3.2.使用cursor_sharing=force,强制相似sql使用绑定变量

这里有必要对cursor_sharing的值做简单介绍：
–EXACT - 这是默认值，sql字符完全匹配才能共享sql
–FORCE - 所有条件都转换为绑定变量（WONUM=‘1234’ 和 WONUM LIKE ‘123%’ 均被转换）。
–SIMILAR - 仅将等价条件转换为绑定变量（转换 WONUM=‘1234’，但不转换 WONUM LIKE ‘123%’）。
另外，引用外网的一段解释作参考
cursor_sharing=similar: “Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.”

cursor_sharing=FORCE: “Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.”
So basically it means that the SQL statement will be shared even though literals are different as long as it does not affect the execution plan of the statement. This is the case for SIMILAR setting.

The cursor_sharing=SIMILAR parameter has been deprecated in 11gR2 because it has been found that the use of this parameter could potentially have a lot of performance implications related to the number of child cursors created for the single parent.

In versions prior to 11g R2, the was a limit on the number of child cursors which can be associated with a single parent. It was 1024 and once this number was crossed the parent was marked obsolete and this invalidated it was well as all associated child cursors.

But not having this upper limit was being found to have caused a lot of CPU usage and waits on mutexes and library cache locks in 11gR2 caused by searching the library cache for matching cursors and it was felt that having many child cursors all associated with one parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT

基于上述原因，系统维护人员讨论后，决定先采用方案二

SQL> show parameter cursor_sharing;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL> alter system set cursor_sharing=force scope=both;
System altered.

验证

再次查询v$sqlarea,executions已经由原来的1次，变成了上万次，说明sql已经获得公用

select sql_id,address,executions,SQL_FULLTEXT 
from v$sqlarea where SQL_FULLTEXT like 'UPDATE MK_TMP_EM_DAYWK ED  SET WORK_LV58%'

上图中的sql如下，注意红色部分,已经由原来的常量变成了绑定变量
UPDATE MK_TMP_EM_DAYWK ED SET WORK_LV58 = FUN_WH_ADJ(NVL(WORK_LV58,:“SYS_B_0”)+ CASE WHEN NVL(WORK_LV38,:“SYS_B_1”) BETWEEN :“SYS_B_2” AND :“SYS_B_3” THEN :“SYS_B_4” ELSE :“SYS_B_5” END ) WHERE FACT_NO = :“SYS_B_6” AND CARD_DATE = :“SYS_B_7” AND WK_TYPE = :“SYS_B_8”