标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,670)
- DB2 (22)
- MySQL (73)
- Oracle (1,532)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (91)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
标签归档:V$Wait_Chains
V$Wait_Chains定位阻塞源头
从11.1开始,查询会话阻塞,V$Wait_Chains是一个非常好的视图,通过他可以快速找到阻塞源头.进行一个简单的测试
测试表create table t1 as select * from dba_objects;
会话1
SQL> delete from t1 where object_id>100 and object_id<10000; 9712 rows deleted.
会话2
SQL> delete from t1 where object_id<200; ---hang住
会话3
SQL> delete from t1 where object_id>88 and object_id<150; --hang住
会话4进行查询分析
SQL> set lines 150 SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID BLOCKER_OSID SUBSTR(WAIT_EVENT_TEXT,1,30) ---------- ----------- ------------ ------------------------- ------------------------- ---------------------------------- 1 0 438 17072 17005 enq: TX - row lock contention 1 1 483 17005 16930 enq: TX - row lock contention 1 2 505 16930 SQL*Net message from client SQL> set pages 1000 SQL> set lines 120 SQL> set heading off SQL> column w_proc format a50 tru SQL> column instance format a20 tru SQL> column inst format a28 tru SQL> column wait_event format a50 tru SQL> column p1 format a16 tru SQL> column p2 format a16 tru SQL> column p3 format a15 tru SQL> column Seconds format a50 tru SQL> column sincelw format a50 tru SQL> column blocker_proc format a50 tru SQL> column waiters format a50 tru SQL> column chain_signature format a100 wra SQL> column blocker_chain format a100 wra SQL> SQL> SELECT * 2 FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 3 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)|| 4 ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters, 5 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3, 6 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 7 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, 8 '<none>',blocker_chain_id) blocker_chain 9 FROM v$wait_chains wc, 10 v$instance i 11 WHERE wc.instance = i.instance_number (+) 12 AND ( num_waiters > 0 13 OR ( blocker_osid IS NOT NULL 14 AND in_wait_secs > 10 ) ) 15 ORDER BY chain_id, 16 num_waiters DESC) 17 WHERE ROWNUM < 101; Current Process: 16930 SID orcl INST #: 1 Blocking Process: <none> from Instance Number of waiters: 2 Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0 Seconds in Wait: 140 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> Current Process: 17005 SID orcl INST #: 1 Blocking Process: 16930 from Instance 1 Number of waiters: 1 Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 524290 P3: 8984 Seconds in Wait: 119 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> Current Process: 17072 SID orcl INST #: 1 Blocking Process: 17005 from Instance 1 Number of waiters: 0 Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 458768 P3: 8720 Seconds in Wait: 74 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> SQL> set pages 1000 SQL> set lines 120 SQL> set heading off SQL> column w_proc format a50 tru SQL> column instance format a20 tru SQL> column inst format a28 tru SQL> column wait_event format a50 tru SQL> column p1 format a16 tru SQL> column p2 format a16 tru SQL> column p3 format a15 tru SQL> column Seconds format a50 tru SQL> column sincelw format a50 tru SQL> column blocker_proc format a50 tru SQL> column fblocker_proc format a50 tru SQL> column waiters format a50 tru SQL> column chain_signature format a100 wra SQL> column blocker_chain format a100 wra SQL> SELECT * 2 FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 3 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)|| 4 ' from Instance '||blocker_instance BLOCKER_PROC, 5 'Number of waiters: '||num_waiters waiters, 6 'Final Blocking Process: '||decode(p.spid,null,'<none>', 7 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 8 'Program: '||p.program image, 9 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3, 10 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 11 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, 12 '<none>',blocker_chain_id) blocker_chain 13 FROM v$wait_chains wc, 14 gv$session s, 15 gv$session bs, 16 gv$instance i, 17 gv$process p 18 WHERE wc.instance = i.instance_number (+) 19 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+) 20 and wc.sess_serial# = s.serial# (+)) 21 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+)) 22 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+)) 23 AND ( num_waiters > 0 24 OR ( blocker_osid IS NOT NULL 25 AND in_wait_secs > 10 ) ) 26 ORDER BY chain_id, 27 num_waiters DESC) 28 WHERE ROWNUM < 101; Current Process: 16930 SID orcl INST #: 1 Blocking Process: <none> from Instance Number of waiters: 2 Final Blocking Process: <none> from Instance Program: Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0 Seconds in Wait: 177 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> Current Process: 17005 SID orcl INST #: 1 Blocking Process: 16930 from Instance 1 Number of waiters: 1 Final Blocking Process: 16930 from Instance 1 Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3) Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 524290 P3: 8984 Seconds in Wait: 155 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> Current Process: 17072 SID orcl INST #: 1 Blocking Process: 17005 from Instance 1 Number of waiters: 0 Final Blocking Process: 16930 from Instance 1 Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3) Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 458768 P3: 8720 Seconds in Wait: 110 Seconds Since Last Wait: Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c ontention' Blocking Wait Chain: <none> SQL> col SES for a15 SQL> set linesize 150 SQL> WITH BLOCKED AS 2 (SELECT * 3 FROM (SELECT INSTANCE, 4 SID, 5 SESS_SERIAL#, 6 BLOCKER_INSTANCE, 7 BLOCKER_SID, 8 BLOCKER_SESS_SERIAL#, 9 LEVEL LV, 10 NUM_WAITERS, 11 BLOCKER_CHAIN_ID 12 FROM V$WAIT_CHAINS 13 CONNECT BY PRIOR SID = BLOCKER_SID 14 AND PRIOR SESS_SERIAL# = BLOCKER_SESS_SERIAL# 15 AND PRIOR INSTANCE = BLOCKER_INSTANCE 16 START WITH BLOCKER_IS_VALID = 'FALSE') 17 WHERE NUM_WAITERS > 0 18 OR BLOCKER_SID IS NOT NULL) 19 SELECT INSTANCE, 20 LPAD(' ', 2 * (LV - 1)) || B.SID SES, 21 B.SESS_SERIAL#, 22 B.BLOCKER_INSTANCE, 23 B.BLOCKER_SID, 24 B.BLOCKER_SESS_SERIAL# 25 FROM BLOCKED B ; INSTANCE SES SESS_SERIAL# BLOCKER_INSTANCE BLOCKER_SID BLOCKER_SESS_SERIAL# ---------- --------------- ------------ ---------------- ----------- -------------------- 1 42 1819 1 32 52659 1 42 1819 1 39 39865 1 32 52659
能够快速的定位到阻塞会话的源头,以及阻塞的级联关系