诊断Oracle数据库Hanging问题(1)

将讨论如下的诊断步骤：

1) 描述清楚出现的现象问题

2) 寻找具体错误

3) 收集操作系统级别上的数据

4) 获取systemstate和hanganalyze的dump

5) 获取STATPACK的输出报告

6) 获取PROCESSSTATE的dump

注：可能很多时候没有必要关闭数据库来停止hanging，建议如果要关闭数据库之前获取这些诊断信息以便找出错误的原因所在。

下面就来具体讨论如何诊断数据库Hanging问题。

描述清楚出现的现象问题：

先弄清楚运行的数据库版本，需要完整的版本号，例如9.2.0.4。

确定当前数据库是否是真的hanging还是处于活动状态但是运行的非常慢？检查下在Alert文件中是否还有日志切换，检查当前的CPU，I/O，内存的利用率。

查看数据库hanging的开始时间，持续了多长时间？数据库hanging是否是突然发生还是由于增加的活动事务导致性能的逐步降低？当前有多少的连接用户？最近的系统负载是否是在上升？ 

是否在初始化参数文件中设置了任何event？数据库当前正在做什么类型的事务？数据库的数据量多大？

数据库是运行在集群环境吗？如果是集群数据库，那么关闭其他实例就留下一个实例，问题是否还持续存在？这里讨论的某些解决方法适用于集群数据库，但是大部分的方法不适合。例如，一个不大的buffer cache通常对于集群数据库来说意味着较好的性能。关于集群数据库的大部分hanging的问题这里不做讨论，其中包括PCM锁问题，pinging，空间管理问题，节点间并行查询调优，共享磁盘或者虚拟共享磁盘问题，网络问题，DLM问题等。

数据库是运行在MTS环境下吗？如果取消MTS，是否问题持续存在？是否使用了Oracle的应用或者工具？最近是否升级了数据库，应用，工具或者操作系统，硬件？问题发生的频率？是否能够重现问题？

是否整个数据库都被hanging？ 

所有的实例？所有的连接？所有的操作？所有的节点？ 

首先确认是否能够执行查询select * from dual?日志文件多久切换一次？如果在Alert日志中有归档相关的错误信息，那么可以着手解决归档错误问题，因为归档问题经常会挂起数据库。例如：归档目的地空间满了，或者数据库处于归档模式下但是ARCH进程被停止了。一般可以先以sysdba权限连接到数据库中，执行ARCHIVE LOG LIST，查看数据库是否归档模式，是否启用了自动归档，一般如果没有启用自动归档，就很容易挂起数据库了，这个时候通常的做法就是把数据库改成自动归档模式或者是非归档模式。 

一个指定的SQL语句操作？

1) 如果是由于指定的SQL语句导致数据库挂起，先执行带有timed_statistics参数的TKPROF输出报告以及SQL语句的执行计划，然后就需要分SQL语句类型来分析了：

2) 如果是select语句，那么这个SQL语句应该是需要被调整，如果是一个非常复杂的SQL语句，那么尝试是否可以中断。

3) 如果是一个并行查询语句，可以参考监控当前并行查询运行状况脚本获得并行查询的执行计划。可能是空间事务竞争，如果在Alert日志文件中出现ORA-1575错误，那么请将临时表空间的参数pct_increase设置为0以便禁止SMON进程接合连续的extents，因此减少查询slaves的竞争。同时将数据文件尽量分散到不同的磁盘上去，减少磁盘I/O的竞争，适当增加sort_area_size的大小可能会‘减少’并行度。

4) 如果是DML语句，那么可能是由于锁导致的，需要去获取v$lock的输出信息，关于锁的信息可以参考返回锁信息脚本。查看DML语句的对象上是否有限制或者触发器，有可能产生级联锁问题。把索引建立在相关的外键列上，这样会改变在父表上的锁行为。

5) 如果是DDL语句，可能是一个数据字典的相关问题。如果是create index语句则可能是一个空间事务竞争问题。调整I/O是一个比较好的方法，分布式I/O，分开索引和数据的存放空间，并行执行都是比较有用的方法，还可以设置初始化参数pre_page_sga为true。

指定的数据库对象？

在指定对象能是否能做任何操作？做一个select count(*)是否有问题？如果只是update该对象存在问题，那么可能锁了，可以从上面3)、4)中的脚本获取锁的信息。

是否预先分配好了空间给这个对象？如果是，那么将提高HWM并且导致全表扫描，以至于让数据库看起来像是“挂起”了。全表扫描总是会扫描HWM，即使表只存在很少的数据。解决方案就是尽量避免预分配extents除非马上要执行一个大的并行插入或者常规的装载。千万不要在直接装载的时候预分配extents。

如果对象是一个表，那么可以尝试

ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE; 

是否有报错，如果有报错，意味着表或者表上的索引存在坏块了。如果没有报错，那么继续尝试下面的SQL语句得到相应的的信息：

块级上的空间信息，一个高的chain out，也可能是问题的一部分。

SELECT * 

FROM sys.dba dba_tables 

WHERE table_name = '<TABLENAME>'; 

如果你有很多的更新和删除操作，那么一个不适合的索引也会造成问题，下面的SQL语句能帮你得到相关的索引信息：

SELECT i.*

FROM sys.index_stats i, sys.dba_indexes d

WHERE i.name = d.index_name

AND d.table_name = '<TABLENAME>';SELECT i.*

FROM sys.index_stats i, sys.dba_indexes d

WHERE i.name = d.index_name

AND d.table_name = '<TABLENAME>';

SELECT text

FROM sys.dba_views

WHERE view_name = '<VIEWNAME>';