UNDO相关问题总结（二）

这一次主题为单个session占用大量undo，导致数据库性能急剧下降的问题总结。关于“undo表空间不足的问题”详见我上一篇总结。

问题现象

• 数据库表现为“latch: undo global data”或者“wait for a undo record”等其它undo相关的等待事件
• CPU资源急剧上升，可能达到100%
• 业务运行缓慢

问题原因

• session A在事务中对某张表（表T）进行DML操作，导致这张表上的回滚信息（前映像数据）超过一定阀值（一般100M以上，高并发下可能50M就算大量undo占用）。同一时刻（此时SESSION A的事务还未commit），大量并发连接（session B,C,D......等等）需要访问表T（访问的表数据块包含被session A修改的快），这种情况下由于需要完成读一致性要求，其它并发session 需要读取对应的回滚段构造CR块，此时SQL语句的执行效率会立马下降，数据库表现为“latch: undo global data”或者“wait for a undo record”等其它undo相关的等待事件，CPU资源急剧上升，可能达到100%，业务基本无法响应。

引申现象

1. 数据库中出现大量latch: undo global data”或者“wait for a undo record”相关的等待，但在当前实例中查询每个session的undo使用情况时，未发现有某个session占用大量undo。这种情况可能是由于其它实例的某个session使用大量undo导致，因此，需要所有实例上进行查询。
2. 数据库中出现大量latch: undo global data”或者“wait for a undo record”相关的等待，但是在数据库的所有实例上查询，未发现有某个session占用大量undo，这种情况可能是由于占用大量undo的session事务已经commit，但是SQL语句效率下降的时间点是发生在事务未commit的时间段，主机cpu资源100%，由于排队效应，数据库整体运行效率低效，即使导致产生问题的事务提交完毕后，整个数据库也无法马上恢复正常，需要等待一段时间才能恢复正常（等待的时间未知，某些重要的系统，可能无法接受长时间的等待）

解决方法（仅供参考）

1. 查询数据库所有实例中每个session的undo使用情况（也可以首先整体查询一下undo表空间的整体使用情况），具体SQL语句详见我上一篇博客“UNDO相关问题总结（一）”
2. 如果查询结果中未发现有session正在占用大量undo，如上“引申现象中的2”部分，说明造成问题的session已经commit，可以等待一段时间，数据库会慢慢恢复正常。如果无法接受等待，可以考虑kill掉undo相关等待的连接，释放CPU资源。
3. 如果查询结果中发现有session使用了大量undo（具体多少得看业务并发量，一般100M以上），首先得判断session当前正在进行的事务完成进度。如果即将完成，可以考虑等待事务完成提交后，数据库慢慢恢复正常，见步骤2。如果事务完成进度小于60%，可以考虑kill掉该session。判断事务完成进度的可以根据步骤1查询结构中的machine,program,module等信息定位具体的连接信息，然后询问开发人员、语句执行人员（如果该语句为人为执行）、查询v$session_longops也能有帮助。
4. kill掉使用大量undo的连接后，由于回滚kill掉的事务需要时间（特别是问题发生后，CPU资源100%，数据库的任何操作都会特别缓慢，平时5分钟能完成的回滚，可能在问题时间段1个小时都无法完成），数据库性能恶劣的问题会持续很长一段时间，业务也极有可能根本不能接受这么长时间的无法响应。因此，后续解决问题的关键转化为怎么让事务回滚的更快。
5. 评估回滚事务的完成进度，如下语句：

select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",

         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" 

  from v$fast_start_transactions; 

     过一段时间间隔后，再次使用上面的语句查询事务的回滚进度，可以预估完成回滚大概时间。

也可以使用如下语句查询回滚进度：

declare

l_start number;

l_end    number;

begin

  select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=194 and KTUXESLT=17; 

  dbms_lock.sleep(60);

  select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=194 and KTUXESLT=17;

  dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2));

end;

/

加快事务回滚的解决方法，可划分为三类（仅供参考）

数据库可以重启，可以考虑的解决办法

1. 设置回滚并行度为HIGH
2. 增加_cleanup_rollback_entries参数值（默认为100）
3. 重启数据库，回滚完成后，开放给业务使用。

可以停止相关应用，可以考虑的解决办法

1. 停止相关应用（涉及到这种表的查询），停止应用后，如果连接未断开，需要kill掉这些连接。
2. 设置回滚并行度为HIGH（可能smon已经在回滚，无法调整并行度，此时需要先禁用smon回滚，然后设置并行度，再恢复smon回滚）
3. 回滚完成后，启动相关应用

数据库不能重启，相关应用也不能停止，可以考虑的解决办法

1. rename正在回滚的表，同时新建一张空表替换（注意考虑索引，外键，依赖性等等），将那些等待的undo的session kill掉，释放CPU资源，最后将数据回插到空表（数据量大的情况下需分批插入和commit，避免undo量过大导致重复这类问题），这个解决方法的局限性在于应用可否接受短暂的空表。

备注：rename正在回滚的表后，该表不会存在访问，同时后续访问该表的SQL（被替换为空表了）也不会存在效率问题，CPU资源很快能回归正常，因此问题时间段回滚速度特别慢的操作，现在也能很快完成。

常见讨论

• 设置回滚并行度参数（fast_start_parallel_rollback），在主机资源100%的情况，无论是增加或者是减少并发回滚进程，回滚的速度都会特别缓慢（资源争用等原因），因此无法及时解决此类问题。
• 禁用smon回滚讨论（ oradebug event 10513 trace name context forever, level 2），由于问题发生在读一致性导致SQL效率低效，与smon是否回滚事务没有根本联系，因此也无法解决此类问题。
• 增加_cleanup_rollback_entries参数值的讨论（一般增加为400-800），由于该参数需要重启数据库生效，因此也无法及时解决此类问题。

总结

1. 一般OLTP系统，不应该存在单个session使用过高undo的情况，如果有，需要调整和优化。如果是人为执行，可能需要进行宣贯和培训；
2. 针对这类情况的预防，可以考虑部署session  undo的使用量监控；
3. OLAP系统，由于主要应用与分析和统计，单个session使用大量undo属于正常现象，而且由于不会有大量并发访问，因此也就不会产生上面讨论的问题

还准备总结一篇undo段损坏问题的解决，也是undo相关问题总结系列的最后一篇。