当一个数据库出现Hang的问题时从数据库中收集信息来诊断挂志的根本原因是非常有用的.数据库Hang的原因往往是孤立的可以使用收集来的诊断信息来解决.另外如果不能解决可以用获得的信息来避免这个问题的再次重现.
解决方法
诊断数据库Hang需要什么信息
数据库Hang的特点是一些进程正在等待另一些进程的完成.通常有一个或多个阻塞进程被困或者正在努力工作但不是迅速的释放资源.为了诊断需要以下信息:
1.Hanganalyze and Systemstate Dumps
2.数据库性能的awr/statspack快照
3.及时的RDA
Hanganalyze and Systemstate Dumps
Hang分析和系统状态转储提供了在一个特定时间点的数据库中的进程信息.Hang分析提供了在Hang链表中所有进程的信息,系统状态提供了数据库中所有进程的信息.当查看一个潜在的Hang情况时你需要判断是否一个进程被因或动行缓慢.通过在两个连续的时间间隔内收集这些转储信息如果进程被困这些跟踪信息可以用于将来的诊断可能帮助你提供一些解决方法.Hang分析用来总结和确认数据库是真的Hang还是只是缓慢并提供了一致性快照,系统状态转储显示了数据库中每一个进程正在做什么
收集Hang分析和系统状态转储信息
登录系统
使用sql*plus以sysdba身份来登录
sqlplus ‘/ as sysdba’
如果连接时出现问题在oracle10gr2中可以使用sqlplus的”preliminary connection’
sqlplus -prelim ‘/ as sysdba’
注意:从oracle 11.2.0.2开始Hang分析在sqlplus的’preliminary connection’连接下将不会生成输出因为它要会请求一个进程的状态对象和一个会话状态对象.如果正试图分析跟踪会输出:
HANG ANALYSIS:
ERROR: Can not perform hang analysis dump without a process state object and a session state object.
( process=(nil), sess=(nil) )
非rac环境收集Hang分析和系统状态的收集命令
有些时候数据库可能只是非常的慢而不是真正的Hang.因此建议收集级别为2的Hang分析和系统状态转储来判断这些进程是正在执行还是已经停止执行
持起分析
sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug hanganalyze 3 -- Wait one minute before getting the second hanganalyze oradebug hanganalyze 3 oradebug tracefile_name exit
系统转储
sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug dump systemstate 266 oradebug dump systemstate 266 oradebug tracefile_name exit
rac环境收集Hang分析和系统状态的收集命令
如果在你的系统中没有应用相关的补丁程序使用级别为266或267的系统状态转储会有2个bug.因此在没有应用这些补丁收集这些级别的转储是不明智的选择
补丁信息如下:
Document 11800959.8 Bug 11800959 – A SYSTEMSTATE dump with level >= 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS – can hang/crash instances
Document 11827088.8 Bug 11827088 – Latch ‘gc element’ contention, LMHB terminates the instance
在修正bug 11800959和bug 11827088的情况下对于rac环境惧订Hang分析和系统状态的收集命令如下:
sqlplus '/ as sysdba' oradebug setorapname reco oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 266 oradebug -g all dump systemstate 266 exit
在没有修正bug 11800959和bug 11827088的情况下对于rac环境惧订Hang分析和系统状态的收集命令如下:
sqlplus '/ as sysdba' oradebug setorapname reco oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 258 oradebug -g all dump systemstate 258 exit
在rac环境中会在每一个实例的跟踪文件中创建所有实例的转储信息
对Hang分析和系统状态转储的级别说明
Hang分析级别
level 3(级别3):在oracle11g之前level 3对Hang链表中的相关进程也会收集一个简短的堆栈信息
系统状态转储级别
level 258(级别258)是一个快速的选择但是会丢失一些锁的元数据信息
level 267(级别267)它包含了理解成本所需要的额外的缓冲区缓存/锁元数据信息
其它的方法
如果不能连接到系统时如何收集系统状态转储信息
通常有两种方法来在系统Hang不能连接时来生成系统状态转储信息
1.alter session set events ‘immediate trace name SYSTEMSTATE level 10’;
2.$ sqlplus
connect sys/passwd as sysdba
oradebug setospid
oradebug dump systemstate 10
(注意:在oradebug中不能使用任何半冒号,如果你的数据库是比oracle9i还老的版本你将需要使用svrmgrl来连接到内部)
当你使用这两种方法中的一种时,要确保在两次转储时内部连接断开.这种方法生成的转储将在你的user_dump_dest目录中是分开的ora_
在非常严重的情况下不能使用svrmgrl或sqlplus进行连接执行这些必要的命令.在这种情况下仍然有一个后门方法使用调试器比如你的系统有dbx的话可以用dbx来生成系统状态转储信息.被连接到的转储核心进程可能会被杀死所以不能连接到一个oracle后台进程.dbx的语法如下:
dbx -a PID (where PID = any oracle shadow process)
dbx() print ksudss(10)
…return value printed here
dbx() detach
首先你需要找到一个影子进程
(jy) % ps -ef |grep sqlplus osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger osupport 94130 84332 1 12:11:20 pts/3 0:00 grep sqlplus (jy) % ps -ef |grep 78526 osupport 28348 78526 0 12:11:05 - 0:00 oracles734 (DESCRIPTION=(LOCAL osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger osupport 94132 84332 1 12:11:38 pts/3 0:00 grep 78526
这样将会连接到影子进程PID 28348上.当返回提示符时输入ksudss(10)命令和detach:
(jy) % dbx -a 28348 Waiting to attach to process 28348 ... Successfully attached to oracle. warning: Directory containing oracle could not be determined. Apply 'use' command to initialize source path. Type 'help' for help. reading symbolic information ... stopped in read at 0xd016fdf0 0xd016fdf0 (read+0x114) 80410014 lwz r2,0x14(r1) (dbx) print ksudss(10) 2 (dbx) detach
在user_dump_dest目录中你将会通过跟踪的pid找到一个系统状态转储文件\
(jy) % ls -lrt *28348* -rw-r----- 1 osupport dba 46922 Oct 10 12:12 ora_28348.trc core_28348: total 72 -rw-r--r-- 1 osupport dba 16567 Oct 10 12:12 core drwxr-xr-x 7 osupport dba 12288 Oct 10 12:12 ../ drwxr-x--- 2 osupport dba 512 Oct 10 12:12 ./
在跟踪文件中你将会找到常用的头信息.在oracle7.3.4并行操作系统中在这后面紧跟的是锁信息然后才是系统转储信息.
在oracle8并行操作系统中和非并行操作系统和oracle7.3.4非并行操作系统的系统状态信息是紧跟头信息.
在转储文件中头信息如下:
Dump file /oracle/mpp/734/rdbms/log/ora_28348.trc Oracle7 Server Release 7.3.4.4.1 - Production With the distributed, replication, parallel query, Parallel Server and Spatial Data options PL/SQL Release 2.3.4.4.1 - Production ORACLE_HOME = /oracle/mpp/734 System name: AIX Node name: saki Release: 3 Version: 4 Machine: 000089914C00 Instance name: s734 Redo thread mounted by this instance: 2 Oracle process number: 0 Unix process pid: 28348, image: ksinfy: nfytype = 0x5 ksinfy: calling scggra(&se) scggra: SCG_PROCESS_LOCKING not defined scggra: calling lk_group_attach() ksinfy: returning *** SESSION ID:(12.15) 2000.10.10.12.11.06.000 ksqcmi: get or convert ksqcmi: get or convert *** 2000.10.10.12.12.08.000 =================================================== SYSTEM STATE .....
确保在这个文件中有一个end of system state.可以对它使用grep或在vi中搜索.如果没有那么这个跟踪文件是不过完整.
可能是因为init.ora文件中的max_dump_file的大小太小了.
对于oracle10g及以后的版本:
在有些情况下不连接到实例是允许的(在有些ora-20的情况下,对于oracle10.1.x,对于sqlplus有一个新选项来允许访问实例来生成跟踪文件)sqlplus -prelim / as sysdba
例如
export ORACLE_SID=PROD ## Replace PROD with the SID you want to trace sqlplus -prelim / as sysdba oradebug setmypid oradebug unlimit; oradebug dump systemstate 10
在rac系统中,Hang分析,系统转储和其它一些rac信息可以使用racdiag.sql脚本来进行收集:
-- NAME: RACDIAG.SQL -- SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON -- ------------------------------------------------------------------------ -- AUTHOR: -- Michael Polaski - Oracle Support Services -- Copyright 2002, Oracle Corporation -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is intended to provide a user friendly guide to troubleshoot -- RAC hung sessions or slow performance scenerios. The script includes -- information to gather a variety of important debug information to determine -- the cause of a RAC session level hang. The script will create a file -- called racdiag_.out in your local directory while dumping hang analyze -- dumps in the user_dump_dest(s) and background_dump_dest(s) on all nodes. -- -- ------------------------------------------------------------------------ -- DISCLAIMER: -- This script is provided for educational purposes only. It is NOT -- supported by Oracle World Wide Technical Support. -- The script has been tested and appears to work as intended. -- You should always run new scripts on a test instance initially. -- ------------------------------------------------------------------------ -- Script output is as follows: set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'Mondd_hhmi') timecol, '.out' spool_extension from sys.dual; column output new_value dbname select value || '_' output from v$parameter where name = 'db_name'; spool racdiag_&&dbname&×tamp&&suffix set lines 200 set pagesize 35 set trim on set trims on alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS'; alter session set timed_statistics = true; set feedback on select to_char(sysdate) time from dual; set numwidth 5 column host_name format a20 tru select inst_id, instance_name, host_name, version, status, startup_time from gv$instance order by inst_id; set echo on -- WAIT CHAINS -- 11.x+ Only (This will not work in < v11 -- See Note 1428210.1 for instructions on interpreting. set pages 1000 set lines 120 set heading off column w_proc format a50 tru column instance format a20 tru column inst format a28 tru column wait_event format a50 tru column p1 format a16 tru column p2 format a16 tru column p3 format a15 tru column Seconds format a50 tru column sincelw format a50 tru column blocker_proc format a50 tru column waiters format a50 tru column chain_signature format a100 wra column blocker_chain format a100 wra SELECT * FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, ' ',blocker_chain_id) blocker_chain FROM v$wait_chains wc, v$instance i WHERE wc.instance = i.instance_number (+) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; -- Taking Hang Analyze dumps -- This may take a little while... oradebug setmypid oradebug unlimit oradebug -g all hanganalyze 3 -- This part may take the longest, you can monitor bdump or udump to see if -- the file is being generated. oradebug -g all dump systemstate 258 -- WAITING SESSIONS: -- The entries that are shown at the top are the sessions that have -- waited the longest amount of time that are waiting for non-idle wait -- events (event column). You can research and find out what the wait -- event indicates (along with its parameters) by checking the Oracle -- Server Reference Manual or look for any known issues or documentation -- by searching Metalink for the event name in the search bar. Example -- (include single quotes): [ 'buffer busy due to global cache' ]. -- Metalink and/or the Server Reference Manual should return some useful -- information on each type of wait event. The inst_id column shows the -- instance where the session resides and the SID is the unique identifier -- for the session (gv$session). The p1, p2, and p3 columns will show -- event specific information that may be important to debug the problem. -- To find out what the p1, p2, and p3 indicates see the next section. -- Items with wait_time of anything other than 0 indicate we do not know -- how long these sessions have been waiting. -- set numwidth 15 set heading on column state format a7 tru column event format a25 tru column last_sql format a40 tru select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds, sw.p1, sw.p2, sw.p3, sa.sql_text last_sql from gv$session_wait sw, gv$session s, gv$sqlarea sa where sw.event not in ('rdbms ipc message','smon timer','pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status','slave wait','wakeup time manager') and sw.seconds_in_wait > 0 and (sw.inst_id = s.inst_id and sw.sid = s.sid) and (s.inst_id = sa.inst_id and s.sql_address = sa.address) order by seconds desc; -- EVENT PARAMETER LOOKUP: -- This section will give a description of the parameter names of the -- events seen in the last section. p1test is the parameter value for -- p1 in the WAITING SESSIONS section while p2text is the parameter -- value for p3 and p3 text is the parameter value for p3. The -- parameter values in the first section can be helpful for debugging -- the wait event. -- column event format a30 tru column p1text format a25 tru column p2text format a25 tru column p3text format a25 tru select distinct event, p1text, p2text, p3text from gv$session_wait sw where sw.event not in ('rdbms ipc message','smon timer','pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status','slave wait','wakeup time manager') and seconds_in_wait > 0 order by event; -- GES LOCK BLOCKERS: -- This section will show us any sessions that are holding locks that -- are blocking other users. The inst_id will show us the instance that -- the session resides on while the sid will be a unique identifier for -- the session. The grant_level will show us how the GES lock is granted to -- the user. The request_level will show us what status we are trying to -- obtain. The lockstate column will show us what status the lock is in. -- The last column shows how long this session has been waiting. -- set numwidth 5 column state format a16 tru; column event format a30 tru; select dl.inst_id, s.sid, p.spid, dl.resource_name1, decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 'KJUSEREX','Exclusive',request_level) as grant_level, decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 'KJUSEREX','Exclusive',request_level) as request_level, decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening', 'KJUSERCA','Canceling','KJUSERCV','Converting') as state, s.sid, sw.event, sw.seconds_in_wait sec from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw where blocker = 1 and (dl.inst_id = p.inst_id and dl.pid = p.spid) and (p.inst_id = s.inst_id and p.addr = s.paddr) and (s.inst_id = sw.inst_id and s.sid = sw.sid) order by sw.seconds_in_wait desc; -- GES LOCK WAITERS: -- This section will show us any sessions that are waiting for locks that -- are blocked by other users. The inst_id will show us the instance that -- the session resides on while the sid will be a unique identifier for -- the session. The grant_level will show us how the GES lock is granted to -- the user. The request_level will show us what status we are trying to -- obtain. The lockstate column will show us what status the lock is in. -- The last column shows how long this session has been waiting. -- set numwidth 5 column state format a16 tru; column event format a30 tru; select dl.inst_id, s.sid, p.spid, dl.resource_name1, decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 'KJUSEREX','Exclusive',request_level) as grant_level, decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 'KJUSEREX','Exclusive',request_level) as request_level, decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening', 'KJUSERCA','Cancelling','KJUSERCV','Converting') as state, s.sid, sw.event, sw.seconds_in_wait sec from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw where blocked = 1 and (dl.inst_id = p.inst_id and dl.pid = p.spid) and (p.inst_id = s.inst_id and p.addr = s.paddr) and (s.inst_id = sw.inst_id and s.sid = sw.sid) order by sw.seconds_in_wait desc; -- LOCAL ENQUEUES: -- This section will show us if there are any local enqueues. The inst_id will -- show us the instance that the session resides on while the sid will be a -- unique identifier for. The addr column will show the lock address. The type -- will show the lock type. The id1 and id2 columns will show specific -- parameters for the lock type. -- set numwidth 12 column event format a12 tru select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2, decode(l.block,0,'blocked',1,'blocking',2,'global') block, sw.event, sw.seconds_in_wait sec from gv$lock l, gv$session_wait sw where (l.sid = sw.sid and l.inst_id = sw.inst_id) and l.block in (0,1) order by l.type, l.inst_id, l.sid; -- LATCH HOLDERS: -- If there is latch contention or 'latch free' wait events in the WAITING -- SESSIONS section we will need to find out which proceseses are holding -- latches. The inst_id will show us the instance that the session resides -- on while the sid will be a unique identifier for. The username column -- will show the session's username. The os_user column will show the os -- user that the user logged in as. The name column will show us the type -- of latch being waited on. You can search Metalink for the latch name in -- the search bar. Example (include single quotes): -- [ 'library cache' latch ]. Metalink should return some useful information -- on the type of latch. -- set numwidth 5 select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name from gv$latchholder lh, gv$session s, gv$process p where (lh.sid = s.sid and lh.inst_id = s.inst_id) and (s.inst_id = p.inst_id and s.paddr = p.addr) order by lh.inst_id, s.sid; -- LATCH STATS: -- This view will show us latches with less than optimal hit ratios -- The inst_id will show us the instance for the particular latch. The -- latch_name column will show us the type of latch. You can search Metalink -- for the latch name in the search bar. Example (include single quotes): -- [ 'library cache' latch ]. Metalink should return some useful information -- on the type of latch. The hit_ratio shows the percentage of time we -- successfully acquired the latch. -- column latch_name format a30 tru select inst_id, name latch_name, round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio, round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS" from gv$latch where round((gets-misses)/decode(gets,0,1,gets),3) < .99 and gets != 0 order by round((gets-misses)/decode(gets,0,1,gets),3); -- No Wait Latches: -- select inst_id, name latch_name, round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio, round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS" from gv$latch where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99 and immediate_gets + immediate_misses > 0 order by round((immediate_gets/(immediate_gets+immediate_misses)), 3); -- GLOBAL CACHE CR PERFORMANCE -- This shows the average latency of a consistent block request. -- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds -- depending on your system configuration and volume, is the average -- latency of a consistent-read request round-trip from the requesting -- instance to the holding instance and back to the requesting instance. If -- your CPU has limited idle time and your system typically processes -- long-running queries, then the latency may be higher. However, it is -- possible to have an average latency of less than one millisecond with -- User-mode IPC. Latency can be influenced by a high value for the -- DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process -- can issue more than one request for a block depending on the setting of -- this parameter. Correspondingly, the requesting process may wait longer. -- Also check interconnect badwidth, OS tcp settings, and OS udp settings if -- AVG CR BLOCK RECEIVE TIME is high. -- set numwidth 20 column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9 select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED", b1.value "GCS CR BLOCK RECEIVE TIME", ((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)" from gv$sysstat b1, gv$sysstat b2 where b1.name = 'global cache cr block receive time' and b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id or b1.name = 'gc cr block receive time' and b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ; -- GLOBAL CACHE LOCK PERFORMANCE -- This shows the average global enqueue get time. -- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the -- elapsed time for a get includes the allocation and initialization of a -- new global enqueue. If the average global enqueue get (global cache -- get time) or average global enqueue conversion times are excessive, -- then your system may be experiencing timeouts. See the 'WAITING SESSIONS', -- 'GES LOCK BLOCKERS', GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' -- sections if the AVG GLOBAL LOCK GET TIME is high. -- set numwidth 20 column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9 select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS", b3.value "GLOBAL LOCK GET TIME", (b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)" from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3 where b1.name = 'global lock sync gets' and b2.name = 'global lock async gets' and b3.name = 'global lock get time' and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id or b1.name = 'global enqueue gets sync' and b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time' and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id; -- RESOURCE USAGE -- This section will show how much of our resources we have used. -- set numwidth 8 select inst_id, resource_name, current_utilization, max_utilization, initial_allocation from gv$resource_limit where max_utilization > 0 order by inst_id, resource_name; -- DLM TRAFFIC INFORMATION -- This section shows how many tickets are available in the DLM. If the -- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which -- could cause a DLM hang. Make sure that you also have enough TCKT_AVAIL. -- set numwidth 10 select * from gv$dlm_traffic_controller order by TCKT_AVAIL; -- DLM MISC -- set numwidth 10 select * from gv$dlm_misc; -- LOCK CONVERSION DETAIL: -- This view shows the types of lock conversion being done on each instance. -- select * from gv$lock_activity; -- INITIALIZATION PARAMETERS: -- Non-default init parameters for each node. -- set numwidth 5 column name format a30 tru column value format a50 wra column description format a60 tru select inst_id, name, value, description from gv$parameter where isdefault = 'FALSE' order by inst_id, name; -- TOP 10 WAIT EVENTS ON SYSTEM -- This view will provide a summary of the top wait events in the db. -- set numwidth 10 column event format a25 tru select inst_id, event, time_waited, total_waits, total_timeouts from (select inst_id, event, time_waited, total_waits, total_timeouts from gv$system_event where event not in ('rdbms ipc message','smon timer', 'pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status','slave wait','wakeup time manager') order by time_waited desc) where rownum < 11 order by time_waited desc; -- SESSION/PROCESS REFERENCE: -- This section is very important for most of the above sections to find out -- which user/os_user/process is identified to which session/process. -- set numwidth 7 column event format a30 tru column program format a25 tru column username format a15 tru select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username, p.username os_user, sw.event, sw.seconds_in_wait sec from gv$process p, gv$session s, gv$session_wait sw where (p.inst_id = s.inst_id and p.addr = s.paddr) and (s.inst_id = sw.inst_id and s.sid = sw.sid) order by p.inst_id, s.sid; -- SYSTEM STATISTICS: -- All System Stats with values of > 0. These can be referenced in the -- Server Reference Manual -- set numwidth 5 column name format a60 tru column value format 9999999999999999999999999 select inst_id, name, value from gv$sysstat where value > 0 order by inst_id, name; -- CURRENT SQL FOR WAITING SESSIONS: -- Current SQL for any session in the WAITING SESSIONS list -- set numwidth 5 column sql format a80 wra select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql from gv$session_wait sw, gv$session s, gv$sqlarea sa where sw.sid = s.sid (+) and sw.inst_id = s.inst_id (+) and s.sql_address = sa.address and sw.event not in ('rdbms ipc message','smon timer','pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status','slave wait','wakeup time manager') and sw.seconds_in_wait > 0 order by sw.seconds_in_wait desc; -- WAIT CHAINS -- 11.x+ Only (This will not work in < v11 -- See Note 1428210.1 for instructions on interpreting. set pages 1000 set lines 120 set heading off column w_proc format a50 tru column instance format a20 tru column inst format a28 tru column wait_event format a50 tru column p1 format a16 tru column p2 format a16 tru column p3 format a15 tru column seconds format a50 tru column sincelw format a50 tru column blocker_proc format a50 tru column waiters format a50 tru column chain_signature format a100 wra column blocker_chain format a100 wra SELECT * FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,' ',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, ' ',blocker_chain_id) blocker_chain FROM v$wait_chains wc, v$instance i WHERE wc.instance = i.instance_number (+) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; -- Taking Hang Analyze dumps -- This may take a little while... oradebug setmypid oradebug unlimit oradebug -g all hanganalyze 3 -- This part may take the longest, you can monitor bdump or udump to see -- if the file is being generated. oradebug -g all dump systemstate 258 set echo off select to_char(sysdate) time from dual; spool off -- --------------------------------------------------------------------------- Prompt; Prompt racdiag output files have been written to:; Prompt; host pwd Prompt alert log and trace files are located in:; column host_name format a12 tru column name format a20 tru column value format a60 tru select distinct i.host_name, p.name, p.value from gv$instance i, gv$parameter p where p.inst_id = i.inst_id (+) and p.name like '%_dump_dest' and p.name != 'core_dump_dest';
v$wait_chains
从oracle11gr1开始,dia0后台进程开始收集Hang分析信息并存储在内存中的"hang analysis cache"中.它会每3秒钟收集一次本地的Hang分析和第10秒钟收集一次全局(rac)Hang分析信息.这些信息在出现Hang时提供快速查看Hang链表的方法.
存储在"hang analysiz cache"中的数据对于诊断数据库竞争和Hang是非常有效的
有许多数据库功能可以利用Hang分析缓存:Hang Management, Resource Manager Idle Blocker Kill,
SQL Tune Hang Avoidance和PMON清除以及外部工具象Procwatcher
下面是oracle11gr2中v$wait_chains视图的描述:
SQL> desc v$wait_chains Name Null Type ----------------------------------------- -------- ---------------------- CHAIN_ID NUMBER CHAIN_IS_CYCLE VARCHAR2(5) CHAIN_SIGNATURE VARCHAR2(801) CHAIN_SIGNATURE_HASH NUMBER INSTANCE NUMBER OSID VARCHAR2(25) PID NUMBER SID NUMBER SESS_SERIAL# NUMBER BLOCKER_IS_VALID VARCHAR2(5) BLOCKER_INSTANCE NUMBER BLOCKER_OSID VARCHAR2(25) BLOCKER_PID NUMBER BLOCKER_SID NUMBER BLOCKER_SESS_SERIAL# NUMBER BLOCKER_CHAIN_ID NUMBER IN_WAIT VARCHAR2(5) TIME_SINCE_LAST_WAIT_SECS NUMBER WAIT_ID NUMBER WAIT_EVENT NUMBER WAIT_EVENT_TEXT VARCHAR2(64) P1 NUMBER P1_TEXT VARCHAR2(64) P2 NUMBER P2_TEXT VARCHAR2(64) P3 NUMBER P3_TEXT VARCHAR2(64) IN_WAIT_SECS NUMBER TIME_REMAINING_SECS NUMBER NUM_WAITERS NUMBER ROW_WAIT_OBJ# NUMBER ROW_WAIT_FILE# NUMBER ROW_WAIT_BLOCK# NUMBER ROW_WAIT_ROW# NUMBER
注意:v$wait_chains等价于gv$视图可能在rac环境中报告多个实例
使用sql来查询基本信息
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; 2 CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID BLOCKER_OSID SUBSTR(WAIT_EVENT_TEXT,1,30) ---------- ----------- ------------ -------------- ------------------------- ----------------------------- 1 0 10198 21045 21044 enq: TX - row lock contention 1 1 10214 21044 SQL*Net message from client
查询top 100 wait chain processs
set pages 1000 set lines 120 set heading off column w_proc format a50 tru column instance format a20 tru column inst format a28 tru column wait_event format a50 tru column p1 format a16 tru column p2 format a16 tru column p3 format a15 tru column Seconds format a50 tru column sincelw format a50 tru column blocker_proc format a50 tru column waiters format a50 tru column chain_signature format a100 wra column blocker_chain format a100 wra SELECT * FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, ' ',blocker_chain_id) blocker_chain FROM v$wait_chains wc, v$instance i WHERE wc.instance = i.instance_number (+) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; Current Process:21549 SID RAC1 INST #: 1 Blocking Process: from Instance Number of waiters:1 Wait Event:SQL*Net message from client P1: 1650815232 P2: 1 P3:0 Seconds in Wait:36 Seconds Since Last Wait: Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention' Blocking Wait Chain: Current Process:25627 SID RAC1 INST #: 1 Blocking Process:21549 from Instance 1 Number of waiters:0 Wait Event:enq: TX - row lock contention P1:1415053318 P2: 524316 P3:50784 Seconds in Wait:22 Seconds Since Last Wait: Wait Chain:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention' Blocking Wait Chain:
ospid 25627正等待一个TX lock正被ospid 21549所阻塞
ospid 21549正空闲等待'SQL*Net message from client'
在oracle11gr2中的最终阻塞会话
在oracle11gr2中可能将v$session.final_blocking_session看作是最终的阻塞者.最终的阻会话/进程在top等待链表上.
这些会话/进程可能是造成问题的原因.
set pages 1000 set lines 120 set heading off column w_proc format a50 tru column instance format a20 tru column inst format a28 tru column wait_event format a50 tru column p1 format a16 tru column p2 format a16 tru column p3 format a15 tru column Seconds format a50 tru column sincelw format a50 tru column blocker_proc format a50 tru column fblocker_proc format a50 tru column waiters format a50 tru column chain_signature format a100 wra column blocker_chain format a100 wra SELECT * FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC, 'Number of waiters: '||num_waiters waiters, 'Final Blocking Process: '||decode(p.spid,null,' ', p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 'Program: '||p.program image, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, ' ',blocker_chain_id) blocker_chain FROM v$wait_chains wc, gv$session s, gv$session bs, gv$instance i, gv$process p WHERE wc.instance = i.instance_number (+) AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+) and wc.sess_serial# = s.serial# (+)) AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+)) AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+)) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; Current Process:2309 SID RAC1 INST #: 1 Blocking Process: from Instance Number of waiters:2 Wait Event:SQL*Net message from client P1: 1650815232 P2: 1 P3:0 Seconds in Wait:157 Seconds Since Last Wait: Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention' Blocking Wait Chain: Current Process:2395 SID RAC1 INST #: 1 Blocking Process:2309 from Instance 1 Number of waiters:0 Final Block Process:2309 from Instance 1 Program: oracle@racdbe1.us.oracle.com (TNS V1-V3) Wait Event:enq: TX - contention P1:1415053318 P2: 524316 P3:50784 Seconds in Wait:139 Seconds Since Last Wait: Wait Chain:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention' Blocking Wait Chain:
B.对数据库性能生成一个awr/statspack快照
C.收集最新的RDA
最新的RDA提供了大量额外关于数据库配置和性能度量的信息可以用来检测可能影响性能的热点的后台进程问题
有时数据库不是真正的被hang住可是只是'spinning' cpu.可以使用以下方法来检查服务器是hang还是spin如果一个操作执行的时间比期待的时间长或者这个操作损害了其它操作的性能时那么最好是检查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,wait_time from V$session_wait order by sid /
上面的查询最少应该执行三次并比较其它查询结果
列意思
sid-- 会话的系统标识符
seq#--序列号.当一个特定会话的等待一个新的事件时这个数字会增加.它能告诉你一个会话是否正在执行
evnet--会话正在等待的或最后等待的操作
p1,p2,p3--它们代表不同的等待值
wait_time--0指示这个会话正在等待的事件.非0指示这个会话最后等待的事件和会话正使用CPU
例如:
SID EVENT SEQ# P1 P2 P3 WTime ---- ------------------------------ ------ ----------- ----------- ----- ------ 1 pmon timer 335 300 0 0 0 2 rdbms ipc message 779 300 0 0 0 6 smon timer 74 300 0 0 0 9 Null event 347 0 300 0 0 16 SQL*Net message from client 1064 1650815315 1 0 -1
如果脚本查询的结果显示正在等待一个enqueue等待事件那么你将需要检查与你hang会话相关的锁信息
column sid format 990 column type format a2 column id1 format 9999999990 column id2 format 9999999990 column Lmode format 990 column request format 990 select * from v$lock /
Spinning
在spin的情况下事件通常来说是静态的且会话不会是正在等待一个事件--而是在等待cpu(注意在极少数情况下,这个事件依赖于执行spin的代码也可能不会静态的.如果会自豪感是spin它将严重使用cpu和内存资源.
对于一个spin的情况重要的是要检测会话正处于spinning的代码.从事件的一些迹象说明通常需要对一个进程生成几次的错误堆栈信息用来分析:
connect sys/sys as sysdba
oradebug setospid
oradebug unlimit
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
这里的spid是操作系统标识符可以从v$process视图是得到.
Hanging
在正常的情况下在v$session_wait视图中的值应该是用每个会话执行的不同操作来替换.
在hang住的情况下对于一个或一组特定会话的所有系统事件将会是保持静态状态且进程不会消耗任何cpu和内存资源.鉴于会话现在没有请求锁定任何资源这就叫hang
在这种情况下可对实例转储系统状态来获得一些更详细更有用的信息.
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL XX';
在oralce9.2.0.6或oracle10.1.0.4或在oracle10g中最高的版本的中这里的xx是266.执行上面的命令在你的user_dump_dest目录中会生成系统状态跟踪文件.
通过下面的查询可以得到问题进程的进程ID
SELECT pid FROM v$process
WHERE addr =
(SELECT paddr FROM v$session
WHERE sid = sid_of_problem_session);
系统状态转储文件包含了每一个进程的信息.可以通过搜索'PROCESS