诊断Oracle数据库Hanging问题Word文档下载推荐.docx
《诊断Oracle数据库Hanging问题Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《诊断Oracle数据库Hanging问题Word文档下载推荐.docx（10页珍藏版）》请在冰豆网上搜索。
是否能够重现问题?
是否整个数据库都被hanging?
所有实例?
所有连接?
所有操作?
所有节点?
首先确认是否能够执行查询select*fromdual?
日志文件多久切换一次?
如果在Alert日志中有归档相关错误信息，那么可以着手解决归档错误问题，因为归档问题经常会挂起数据库。
例如：
归档目地空间满了，或者数据库处于归档模式下但是ARCH进程被停止了。
一般可以先以sysdba权限连接到数据库中，执行ARCHIVELOGLIST，查看数据库是否归档模式，是否启用了自动归档，一般如果没有启用自动归档，就很容易挂起数据库了，这个时候通常做法就是把数据库改成自动归档模式或者是非归档模式
一个指定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语句，可能是一个数据字典相关问题。
如果是createindex语句则可能是一个空间事务竞争问题。
调整I/O是一个比较好方法，分布式I/O，分开索引和数据存放空间，并行执行都是比较有用方法，还可以设置初始化参数pre_page_sga为true。
指定数据库对象?
在指定对象能是否能做任何操作?
做一个selectcount（*）是否有问题?
如果只是update该对象存在问题，那么可能锁了，可以从上面3）、4）中脚本获取锁信息。
是否预先分配好了空间给这个对象?
如果是，那么将提高HWM并且导致全表扫描，以至于让数据库看起来像是“挂起”了。
全表扫描总是会扫描HWM，即使表只存在很少数据。
解决方案就是尽量避免预分配extents除非马上要执行一个大并行插入或者常规装载。
千万不要在直接装载时候预分配extents。
如果对象是一个表，那么可以尝试
ANALYZETABLEVALIDATESTRUCTURECASCADE;
是否有报错，如果有报错，意味着表或者表上索引存在坏块了。
如果没有报错，那么继续尝试下面SQL语句得到相应信息：
块级上空间信息，一个高chainout，也可能是问题一部分。
SELECT*
FROMsys.dbadba_tables
WHEREtable_name='
<
TABLENAME>
'
;
如果你有很多更新和删除操作，那么一个不适合索引也会造成问题，下面SQL语句能帮你得到相关索引信息：
SELECTi.*
FROMsys.index_statsi,sys.dba_indexesd
WHEREi.name=d.index_name
ANDd.table_name='
如果是一个视图，那么需要查看视图建立在表信息：
SELECTtext
FROMsys.dba_views
WHEREview_name='
VIEWNAME>
大规模更新操作（例如使用SQLLDR，IMPORT或者批处理操作）?
这些操作上表上存在有哪些索引?
是否这些更新操作是在数据库高峰时期运行?
是否在Alert文件中存在有"
checkpointnotcomplete"
错误信息?
如果有表明重做日志文件太小了，需要调整它们。
是否表空间被置于在热备模式下?
（v$backup）如果表空间处于热备模式，那么产生日志”records”而不是“vectors”,在一个大更新操作中，就可能导致相当多竞争和性能下降。
如果是一个SQLLDR操作，是否使用了传统路径方式?
是否使用了REPLACE选项?
（推荐使用TRUNCATE选项）在SQLLDR控制文件中是否有sqlfunctions?
是否采用了readbuffers，bindsize，rows，parallele方式?
如果是一个IMPORT操作，是否使用了commit=y，indexes=y，constraints=y这些参数?
是否增大了buffer?
如果在update期间，有很多用户在操作，那么容易造成资源竞争，导致系统变慢。
回滚段，redolatches,i/o和数据缓冲区都可能成为竞争区域。
我们可以从V$session_wait以及statpack中获取更多关于具体竞争相关信息。
指定包，存储过程或者PRO*C应用?
首先需要查看这些包，存储过程或者PRO*C具体内容，其中哪个语句一直在执行?
去掉这个语句后相应程序是否能运行正常?
如果是存储过程，那么可以利用DBMS_ALERT查看那里开始挂起了。
如果是PRO*C程序，那么可以使用tkprof来识别”parsing”是否是瓶颈?
如果是，那么可以使用预编译参数
hold_cursor和release_cursor来调整。
如果是一个包，那么尝试是否能单独执行每个存储过程?
查看是否包和存储过程被刷新出了共享池，如果是，可以尝试把这些包和存储过程pin在共享池中。
FROMv$db_object_cache
WHEREname='
NAME>
仅仅是远程访问?
是否可以执行select*fromdual@db_link?
是否能够连接到远程机器上执行本地操作?
是否是在做一个分布式更新操作?
初始化参数distributed_lock_timeout设置了多少?
是否正在刷新快照?
是否使用了对称复制?
尝试做一个tkprof输出得到相应执行计划，执行计划中如果标明是REMOTE，那么就是远程执行操作。
如果在一个远程机器上join两张表，那么请尝试在本地节点上生成join视图之后，查询这个视图。
在sql操作中设置ARRAYSIZE，多使用pl/sql而不是单独sql语句，使用显性游标这些都可以减少网络负载。
使用第三方应用软件操作
是否能在sqlplus中重现问题?
如果不可以重现，那么就需要联系第三方应用软件供应商寻求帮助
数据关闭/启动过程中出现挂起
关闭使用什么参数?
数据库是否crash了?
如果是数据库启动挂起并且非正常关闭，但是在Alert日志文件中没有任何错误，那么可能只是一个正常实例恢复，如果在Alert文件中出现内部错误，系统错误，那么请尝试正常关闭数据库然后启动。
下面是一个正常实例恢复时候在Alert日志文件中列出相关信息：
StartingORACLEinstance（normal）
…………………
StartingupORACLERDBMSVersion:
10.2.0.1.0.
Systemparameterswithnon-defaultvalues:
……………………
Beginningcrashrecoveryof1threads
Startedredoscan
Completedredoscan
120redoblocksread,46datablocksneedrecovery
RecoveryofOnlineRedoLog:
Thread1Group2Seq143Readingmem0
Completedredoapplication
Completedcrashrecoveryat
Thread1:
logseq143,block4358,scn512699
46datablocksread,46datablockswritten,120redoblocksread
SMON:
enablingcacherecovery
enablingtxrecovery
Completed:
ALTERDATABASEOPEN
如果正常关闭或者immediate关闭挂起，那么意味着Oracle正在等待激活会话退出。
在Unix系统上，还可以寻找正在挂起启动或者关闭操作，然后tracepid。
寻找错误：
1）检查AlertSID.log告警日志文件看看是否存在错误信息，此告警日志文件具体路径位置可以由初始化参数中background_dump_dest中获得或者在sqlplus中执行showparameterdest获得。
2）检查上述目录中在数据库挂起时间生成跟踪文件。
查看里面错误信息，不用搜索整个跟踪文件，相关错误信息一般都是在文件最开始出现。
3）如果是远程访问问题，那么还需要检查sql*net跟踪目录下跟踪文件。
4）检查系统信息错误日志，在大多数Unix下都是在/var/adm目录下。
输出查看相关V$视图:
当数据库挂起时候，执行下面查询：
SPOOLv_views.log;
SELECT*
FROMv$parameter;
SELECTclass,value,name
FROMv$sysstat;
SELECTsid,id1,id2,type,lmode,request
FROMv$lock;
SELECTl.latch#,n.name,h.pid,l.gets,l.misses,
l.immediate_gets,l.immediate_misses,l.sleeps
FROMv$latchnamen,v$latchholderh,v$latchl
WHEREl.latch#=n.latch#
ANDl.addr=h.laddr（+）;
FROMv$session_wait
ORDERBYsid;
/*重复最后一个查询最少三遍，以确定哪个在重复等待*/
SPOOLOFF
如果是指定查询被挂起了，可以使用下面查询找出相应查询SQL语句：
通过操作系统上PID找出相应SQL语句SID：
SELECTs.sid,p.spid
FROMv$sessions,v$processp
WHEREs.paddr=p.addr
AND...<
p.spid=<
ospid>
orperhaps
s.sid=<
sidfromv$session>
>
然后通过SID找出相应SQL语句具体内容：
SELECTs.sid,s.status,q.sql_text
FROMv$sessions,v$sqltextq
WHEREs.sql_hash_value=q.hash_value
ANDs.sql_address=q.address
ANDs.sid=<
sid>
orderbyq.piece;
查询V$SESSION_WAIT视图看看当前等待事件
columnsidformat990
columnseq#format99990
columnwait_timeheading'
WTime'
format99990
columneventformata30
columnp1format9999999990
columnp2format9999999990
columnp3format9990
selectsid,event,seq#,p1,p2,p3,wait_timefromV$session_wait
wheresid=<
SID>
orderbysid;
查询当前挂起数据库SQL语句中lockwait设置是多少，如果非空，那么看看什么锁住了当前对象，是什么类型锁。
SELECTlockwait
FROMv$session
WHEREsid=<
colUsernameformatA15
colSidformat9990headingSID
colTypeformatA4
colLmodeformat990heading'
HELD'
colRequestformat990heading'
REQ'
colId1format9999990
colId2format9999990
selectSN.Username,M.Sid,M.Type,
DECODE（M.Lmode,0,'
None'
1,'
Null'
2,'
RowShare'
3,'
Row
Excl.'
4,'
Share'
5,'
S/RowExcl.'
6,'
Exclusive'
LTRIM（TO_CHAR（Lmode,'
990'
）））Lmode,
DECODE（M.Request,0,'
LTRIM（TO_CHAR（M.Request,'
）））Request,
M.Id1,M.Id2fromV$SESSIONSN,V$LOCKM
WHERE（SN.Sid=M.SidandM.Request!
=0）
or（SN.Sid=M.SidandM.Request=0andLmode!
=4and（id1,id2）
in（selectS.Id1,S.Id2fromV$LOCKSwhereRequest!
=0andS.Id1
=M.Id1andS.Id2=M.Id2））orderbyId1,Id2,M.Request
查询v$process视图中LATCHWAIT设置是多少?
如果这个值非空，那么继续查是谁保存了这个latch。
SELECTlatchwait
FROMv$process
WHEREspid=<
pid>
columnnameformata32heading'
LATCHNAME'
columnpidheading'
HOLDERPID'
selectc.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
fromv$latcha,v$latchholderb,v$latchnamec
wherea.addr=b.laddr（+）anda.latch#=c.latch#
andc.namelike'
&
latch_name%'
orderbya.latch#;
上述这些保存了锁和latch会话是否关闭了终端但是没有退出，这可能会导致一个影子进程继续保存那些资源，这样就需要杀掉相应进程，可以使用如下语句：
altersystemkillsession'
如果会话没有被挂起而只是运行缓慢，那么需要查看会话具体信息:
SELECTs.sid,s.value,t.name
FROMv$sesstats,v$statnamet
WHEREs.statistic#=t.statistic#
如果会话极度缓慢或者是被挂起了，那么需要查看会话等待信息：
FROMv$session_wait
wheresid=<
如果是个分布式事务，那么需要在各个节点上都运行如下SQL语句：
SELECT*FROMdba_2pc_pending;
SELECT*FROMpending_sessions$;
SELECT*FROMpending_sub_sessions$;
SELECT*FROMdba_2pc_neighbors;
如果是MTS服务器，那么可以查看一下当前dispatcher繁忙程度：
selectname,network,status,
（busy/（busy+idle））*100"
%oftimebusy"
fromv$dispatchers；
还可以查看V$SHARED_SERVERS视图获取相应信息：
selectname,status,requests,（busy/（busy+idle））*100"
fromv$shared_servers
收集操作系统相关信息：
1）简短描述你架构，包括CPU数量，磁盘数量。
是否使用了裸设备，使用了NFS文件系统，共享磁盘。
。
是否镜像了这些?
2）测量不同操作系统级别活动：
过量CPU或者I/O，页面，交换区等。
有许多工具可以监测这些，例如TOP。
Unix上工具：
SAR,VMSTAT,NETSTAT,TOP,TRUSS等
Vms上工具：
MONITOR,ANALYZE,PROCESS等
Windows上工具：
PerformanceMonitor,EventMonitor,Dr.Watson，qslice等
3）检查系统日志文件，在大多数Unix平台上日志文件都存在于/var/adm目录下。
获取SYSTEMSTATE和HANGANALYZEdump
这两个命令将在user_dump_dest目录下创建一个非常大跟踪文件，初始化参数文件中MAX_DUMP_参数确定了能够容纳最大跟踪文件大小。
使用Oradebug命令设置unlimit将能允许执行一个完全dump。
请确认整个数据库已经挂起或者即将挂起，并且在Alert告警日志文件中没有任何归档错误时候才可以做此操作。
注意：
当数据库是集群数据库时候，如果需要诊断挂起问题，则需要在每个节点上都执行systemstatedump操作，建议做3次左右，以便能够确定数据库或者进程是否是真挂起还是激活状态。
对于Oracle8.0.5.xto8.1.7.x版本：
$svrmgrl
svrmgr>
connectinternal
ALTERSESSIONSETEVENTS'
IMMEDIATETRACENAMEHANGANALYZELEVEL3'
wait90seconds
EXIT...thenreconnect
ALTERSESSIONSETMAX_DUMP_;
IMMEDIATETRACENAMESYSTEMSTATELEVEL10'
对于Oracle9.2.0.1或者更高版本：
$sqlplus/nolog
connect/assysdba
oradebugsetmypid
oradebugunlimit
oradebughanganalyze3
oradebugdumpsystemstate10
wait
升级会员 