诊断Oracle数据库Hanging问题

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

将讨论如下的诊断步骤：1) 描述清楚出现的现象问题2) 寻找具体错误3) 收集操作系统级别上的数据4) 获取systemstate和hanganalyze的dump5) 获取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 VALIDATE STRUCTURE CASCADE;是否有报错，如果有报错，意味着表或者表上的索引存在坏块了。

如果没有报错，那么继续尝试下面的SQL语句得到相应的的信息：块级上的空间信息，一个高的chain out，也可能是问题的一部分。

SELECT *FROM sys.dba dba_tablesWHERE table_name = '<TABLENAME>';如果你有很多的更新和删除操作，那么一个不适合的索引也会造成问题，下面的SQL语句能帮你得到相关的索引信息：SELECT i.*FROM sys.index_stats i, sys.dba_indexes dWHERE = d.index_nameAND d.table_name = '<TABLENAME>';SELECT i.*FROM sys.index_stats i, sys.dba_indexes dWHERE = d.index_nameAND d.table_name = '<TABLENAME>';如果是一个视图，那么需要查看视图建立在的表的信息：SELECT textFROM sys.dba_viewsWHERE view_name = '<VIEWNAME>';大规模的更新操作(例如使用SQLLDR，IMPORT或者批处理操作)?这些操作上的表上存在有哪些索引?是否这些更新操作是在数据库高峰时期运行的?是否在Alert文件中存在有"checkpoint not complete"的错误信息?如果有表明重做日志文件太小了，需要调整它们。

是否表空间被置于在热备模式下?(v$backup)如果表空间处于热备模式，那么产生日志”records”而不是“vectors”,在一个大的更新操作中，就可能导致相当多的竞争和性能下降。

如果是一个SQLLDR操作，是否使用了传统路径方式?是否使用了REPLACE选项?(推荐使用TRUNCATE 选项)在SQLLDR的控制文件中是否有sql functions?是否采用了readbuffers，bindsize，rows，parallele方式?如果是一个IMPORT操作，是否使用了commit=y，indexes=y，constraints=y这些参数?是否增大了buffer?如果在update期间，有很多的用户在操作，那么容易造成资源竞争，导致系统变慢。

回滚段，redo latches, i/o和数据缓冲区都可能成为竞争的区域。

我们可以从V$session_wait以及statpack中获取更多关于具体竞争的相关信息。

指定的包，存储过程或者PRO*C应用?首先需要查看这些包，存储过程或者PRO*C的具体内容，其中的哪个语句一直在执行?去掉这个语句后相应的程序是否能运行正常?如果是存储过程，那么可以利用DBMS_ALERT查看那里开始挂起了。

如果是PRO*C程序，那么可以使用tkprof来识别”parsing”是否是瓶颈?如果是，那么可以使用预编译参数hold_cursor和release_cursor来调整。

如果是一个包，那么尝试是否能单独执行每个存储过程?查看是否包和存储过程被刷新出了共享池，如果是，可以尝试把这些包和存储过程pin在共享池中。

SELECT *FROM v$db_object_cacheWHERE name = '<NAME>';仅仅是远程访问?是否可以执行select * from dual@db_link?是否能够连接到远程的机器上执行本地的操作?是否是在做一个分布式的更新操作?初始化参数distributed_lock_timeout设置了多少?是否正在刷新快照?是否使用了对称复制?尝试做一个tkprof输出得到相应的执行计划，执行计划中如果标明是REMOTE的，那么就是远程执行的操作。

如果在一个远程的机器上join两张表，那么请尝试在本地节点上生成join视图之后，查询这个视图。

在sql操作中设置ARRAYSIZE，多使用pl/sql而不是单独的sql语句，使用显性游标这些都可以减少网络的负载。

使用第三方应用软件的操作是否能在sqlplus中重现问题?如果不可以重现，那么就需要联系第三方应用软件供应商寻求帮助数据关闭/启动过程中出现挂起关闭使用的什么参数?数据库是否crash了?如果是数据库启动挂起并且非正常关闭，但是在Alert日志文件中没有任何的错误，那么可能只是一个正常的实例恢复，如果在Alert文件中出现内部错误，系统错误，那么请尝试正常的关闭数据库然后启动。

下面是一个正常实例恢复的时候在Alert日志文件中列出的相关信息：Starting ORACLE instance (normal)…………………Starting up ORACLE RDBMS Version: 10.2.0.1.0.System parameters with non-default values:……………………Beginning crash recovery of 1 threadsStarted redo scanCompleted redo scan120 redo blocks read, 46 data blocks need recoveryRecovery of Online Redo Log: Thread 1 Group 2 Seq 143 Reading mem 0Completed redo applicationCompleted crash recovery atThread 1: logseq 143, block 4358, scn 51269946 data blocks read, 46 data blocks written, 120 redo blocks readSMON: enabling cache recoverySMON: enabling tx recoveryCompleted: ALTER DATABASE OPEN如果正常的关闭或者immediate关闭挂起，那么意味着Oracle正在等待激活的会话退出。