诊断Oracle数据库Hanging问题Word文档下载推荐.docx

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

2、语句操作?1） 如果是由于指定SQL语句导致数据库挂起，先执行带有timed_statistics参数TKPROF输出报告以及SQL语句执行计划，然后就需要分SQL语句类型来分析了：2） 如果是select语句，那么这个SQL语句应该是需要被调整，如果是一个非常复杂SQL语句，那么尝试是否可以中断。3） 如果是一个并行查询语句，可以参考监控当前并行查询运行状况脚本获得并行查询执行计划。可能是空间事务竞争，如果在Alert日志文件中出现ORA-1575错误，那么请将临时表空间参数pct_increase设置为0以便禁止SMON进程接合连续extents，因此减少查询slaves竞争。同时将数据文

3、件尽量分散到不同磁盘上去，减少磁盘I/O竞争，适当增加sort_area_size大小可能会减少并行度。4） 如果是DML语句，那么可能是由于锁导致，需要去获取v$lock输出信息，关于锁信息可以参考返回锁信息脚本。查看DML语句对象上是否有限制或者触发器，有可能产生级联锁问题。把索引建立在相关外键列上，这样会改变在父表上锁行为。5） 如果是DDL语句，可能是一个数据字典相关问题。如果是create index语句则可能是一个空间事务竞争问题。调整I/O是一个比较好方法，分布式I/O，分开索引和数据存放空间，并行执行都是比较有用方法，还可以设置初始化参数pre_page_sga为true。指定

4、数据库对象?在指定对象能是否能做任何操作?做一个select count（*）是否有问题?如果只是update该对象存在问题，那么可能锁了，可以从上面3）、4）中脚本获取锁信息。是否预先分配好了空间给这个对象?如果是，那么将提高HWM并且导致全表扫描，以至于让数据库看起来像是“挂起”了。全表扫描总是会扫描HWM，即使表只存在很少数据。解决方案就是尽量避免预分配extents除非马上要执行一个大并行插入或者常规装载。千万不要在直接装载时候预分配extents。如果对象是一个表，那么可以尝试ANALYZE TABLE VALIDATE STRUCTURE CASCADE;是否有报错，如果有报错，意

5、味着表或者表上索引存在坏块了。如果没有报错，那么继续尝试下面SQL语句得到相应信息：块级上空间信息，一个高chain out，也可能是问题一部分。SELECT * FROM sys.dba dba_tables WHERE table_name = ;如果你有很多更新和删除操作，那么一个不适合索引也会造成问题，下面SQL语句能帮你得到相关索引信息：SELECT i.*FROM sys.index_stats i, sys.dba_indexes dWHERE i.name = d.index_nameAND d.table_name = 如果是一个视图，那么需要查看视图建立在表信息：SELEC

6、T textFROM sys.dba_viewsWHERE view_name = VIEWNAME大规模更新操作（例如使用SQLLDR，IMPORT或者批处理操作）?这些操作上表上存在有哪些索引?是否这些更新操作是在数据库高峰时期运行?是否在Alert文件中存在有checkpoint not complete错误信息?如果有表明重做日志文件太小了，需要调整它们。是否表空间被置于在热备模式下?（v$backup）如果表空间处于热备模式，那么产生日志”records”而不是“vectors”,在一个大更新操作中，就可能导致相当多竞争和性能下降。如果是一个SQLLDR操作，是否使用了传统路径方式?

7、是否使用了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中获取更多关于具体竞争相关信息。指定包，存储过程

8、或者PRO*C应用?首先需要查看这些包，存储过程或者PRO*C具体内容，其中哪个语句一直在执行?去掉这个语句后相应程序是否能运行正常?如果是存储过程，那么可以利用DBMS_ALERT查看那里开始挂起了。如果是PRO*C程序，那么可以使用tkprof来识别”parsing”是否是瓶颈?如果是，那么可以使用预编译参数hold_cursor和release_cursor来调整。如果是一个包，那么尝试是否能单独执行每个存储过程?查看是否包和存储过程被刷新出了共享池，如果是，可以尝试把这些包和存储过程pin在共享池中。FROM v$db_object_cache WHERE name = NAME仅仅是

9、远程访问?是否可以执行select * from dualdb_link?是否能够连接到远程机器上执行本地操作?是否是在做一个分布式更新操作?初始化参数distributed_lock_timeout设置了多少?是否正在刷新快照?是否使用了对称复制?尝试做一个tkprof输出得到相应执行计划，执行计划中如果标明是REMOTE，那么就是远程执行操作。如果在一个远程机器上join两张表，那么请尝试在本地节点上生成join视图之后，查询这个视图。在sql操作中设置ARRAYSIZE，多使用pl/sql而不是单独sql语句，使用显性游标这些都可以减少网络负载。使用第三方应用软件操作是否能在sqlplu

10、s中重现问题?如果不可以重现，那么就需要联系第三方应用软件供应商寻求帮助数据关闭/启动过程中出现挂起关闭使用什么参数?数据库是否crash了?如果是数据库启动挂起并且非正常关闭，但是在Alert日志文件中没有任何错误，那么可能只是一个正常实例恢复，如果在Alert文件中出现内部错误，系统错误，那么请尝试正常关闭数据库然后启动。下面是一个正常实例恢复时候在Alert日志文件中列出相关信息：Starting ORACLE instance （normal）Starting up ORACLE RDBMS Version: 10.2.0.1.0.System parameters with non-

11、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 5126

12、9946 data blocks read, 46 data blocks written, 120 redo blocks readSMON: enabling cache recovery enabling tx recoveryCompleted: ALTER DATABASE OPEN 如果正常关闭或者immediate关闭挂起，那么意味着Oracle正在等待激活会话退出。在Unix系统上，还可以寻找正在挂起启动或者关闭操作，然后trace pid。寻找错误：1） 检查AlertSID.log告警日志文件看看是否存在错误信息，此告警日志文件具体路径位置可以由初始化参数中backgrou

13、nd_dump_dest中获得或者在sqlplus中执行show parameter dest获得。2） 检查上述目录中在数据库挂起时间生成跟踪文件。查看里面错误信息，不用搜索整个跟踪文件，相关错误信息一般都是在文件最开始出现。3） 如果是远程访问问题，那么还需要检查sql*net跟踪目录下跟踪文件。4） 检查系统信息错误日志，在大多数Unix下都是在/var/adm目录下。输出查看相关V$视图:当数据库挂起时候，执行下面查询：SPOOL v_views.log;SELECT *FROM v$parameter;SELECT class, value, nameFROM v$sysstat;S

14、ELECT sid, id1, id2, type, lmode, requestFROM v$lock;SELECT l.latch#, n.name, h.pid, l.gets, l.misses,l.immediate_gets, l.immediate_misses, l.sleepsFROM v$latchname n, v$latchholder h, v$latch lWHERE l.latch# = n.latch#AND l.addr = h.laddr（+）;FROM v$session_waitORDER BY sid;/* 重复最后一个查询最少三遍，以确定哪个在重复等

15、待*/ SPOOL OFF如果是指定查询被挂起了，可以使用下面查询找出相应查询SQL语句：通过操作系统上PID找出相应SQL语句SID：SELECT s.sid, p.spidFROM v$session s, v$process pWHERE s.paddr = p.addrAND . p.spid = or perhapss.sid = 然后通过SID找出相应SQL语句具体内容：SELECT s.sid, s.status, q.sql_textFROM v$session s, v$sqltext qWHERE s.sql_hash_value = q.hash_valueAND s.s

16、ql_address = q.addressAND s.sid = order by q.piece;查询V$SESSION_WAIT视图看看当前等待事件column sid format 990 column seq# format 99990 column wait_time heading WTime format 99990 column event format a30 column p1 format 9999999990 column p2 format 9999999990 column p3 format 9990 select sid,event,seq#,p1,p2,p3

17、,wait_time from V$session_wait where sid=order by sid;查询当前挂起数据库SQL语句中lockwait设置是多少，如果非空，那么看看什么锁住了当前对象，是什么类型锁。SELECT lockwaitFROM v$sessionWHERE sid = col Username format A15col Sid format 9990 heading SIDcol Type format A4col Lmode format 990 heading HELDcol Request format 990 heading REQcol Id1 for

18、mat 9999990col Id2 format 9999990select SN.Username, M.Sid, M.Type,DECODE（M.Lmode, 0, None, 1, Null, 2, Row Share, 3, RowExcl., 4, Share, 5, S/Row Excl., 6, Exclusive,LTRIM（TO_CHAR（Lmode,990） Lmode,DECODE（M.Request, 0, LTRIM（TO_CHAR（M.Request, ） Request,M.Id1, M.Id2 from V$SESSION SN, V$LOCK MWHERE

19、（SN.Sid = M.Sid and M.Request ! = 0）or （SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and （id1, id2）in （select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1= M.Id1 and S.Id2 = M.Id2） ） order by Id1, Id2, M.Request查询v$process视图中LATCHWAIT设置是多少?如果这个值非空，那么继续查是谁保存了这个latch。SELECT latchwaitFROM

20、 v$processWHERE spid = column name format a32 heading LATCH NAMEcolumn pid heading HOLDER PIDselect c.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses,b.pidfrom v$latch a, v$latchholder b, v$latchname cwhere a.addr = b.laddr（+） and a.latch# = c.latch#and c.name like &latch_na

21、me% order by a.latch#;上述这些保存了锁和latch会话是否关闭了终端但是没有退出，这可能会导致一个影子进程继续保存那些资源，这样就需要杀掉相应进程，可以使用如下语句：alter system kill session 如果会话没有被挂起而只是运行缓慢，那么需要查看会话具体信息:SELECT s.sid, s.value, t.nameFROM v$sesstat s, v$statname tWHERE s.statistic# = t.statistic#如果会话极度缓慢或者是被挂起了，那么需要查看会话等待信息：FROM v$session_wait where sid

22、 = connect internalALTER SESSION SET EVENTS IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3wait 90 secondsEXIT . then reconnectALTER SESSION SET MAX_DUMP_;IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10对于Oracle 9.2.0.1或者更高版本：$ sqlplus /nologconnect / as sysdbaoradebug setmypidoradebug unlimitoradebug hanganalyze 3oradebug dump systemstate 10wait