模拟阻塞会话实例
环境:
SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
会话1:(删除sys.dept表中的一行数据,但不做commit或者rollback操作)
user:sys
SQL> set linesize 150
SQL> set pagesize 150
SQL> select * from dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> delete from dept where empno=7369;
1 row deleted.
SQL>
会话2: 重新打开一个会话,删除同一行记录,记过被HOLDING
user:sys
[oracle@rac1 ~]:orcl1> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 1 13:05:54 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> delete from dept where empno=7369;
会话3:(做hanganalyze)
user:sys
[oracle@rac1 ~]:orcl1> sqlplus "/as sysdba"
SQL>
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
查看/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc文件信息
Trace file /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/ora11g
System name: Linux
Node name: rac1
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 8377, image: oracle@rac1 (TNS V1-V3)
*** 2014-12-01 13:07:07.479
*** SESSION ID:(143.3379) 2014-12-01 13:07:07.479
*** CLIENT ID:() 2014-12-01 13:07:07.479
*** SERVICE NAME:(SYS$USERS) 2014-12-01 13:07:07.479
*** MODULE NAME:(sqlplus@rac1 (TNS V1-V3)) 2014-12-01 13:07:07.479
*** ACTION NAME:() 2014-12-01 13:07:07.479
Processing Oradebug command 'SETMYPID'
*** 2014-12-01 13:07:07.517
Oradebug command 'SETMYPID' console output:
*** 2014-12-01 13:07:17.737
Processing Oradebug command 'unlimit'
*** 2014-12-01 13:07:17.737
Oradebug command 'unlimit' console output:
*** 2014-12-01 13:07:41.056
Processing Oradebug command 'hanganalyze 3'
*** 2014-12-01 13:07:45.793
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): orcl.orcl1
oradebug_node_dump_level: 3
analysis initiated by oradebug
os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [ 13:07:45 ]
NOTE: scheduling delay has not been sampled for 0.715229 secs 0.000000 secs from [ 13:07:41 - 13:07:46 ], 5 sec avg
0.000000 secs from [ 13:06:46 - 13:07:46 ], 1 min avg
0.000000 secs from [ 13:02:46 - 13:07:46 ], 5 min avg
vktm time drift history
05:00:00.267 HR time stalled at 1417381211181168
05:00:00.451 HR stall ended at 1417381211365752 drift 184584 us
05:00:00.622 LR time stalled at 1417381200
05:00:01.624 LR stall ended at 1417381201 drift 1 us
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl1)
os id: 8176
process id: 36, oracle@rac1 (TNS V1-V3)
session id: 14
session serial #: 3121
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x60019
p3: 'sequence'=0x4b9
time in wait: 1 min 14 sec
timeout after: never
wait id: 9
blocking: 0 sessions
current sql: delete from dept where empno=7369
short stack: ksedsts()+465 wait history:
* time between current wait and wait #1: 0.018035 sec
1. event: 'SQL*Net message from client'
time waited: 32.086352 sec
wait id: 8 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000001 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 7 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000040 sec
3. event: 'SQL*Net message from client'
time waited: 0.000140 sec
wait id: 6 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (orcl.orcl1)
os id: 6501
process id: 48, oracle@rac1 (TNS V1-V3)
session id: 15
session serial #: 327
}
which is waiting for 'SQL*Net message from client' with wait info:
{
p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
time in wait: 1 min 27 sec
timeout after: never
wait id: 444
blocking: 1 session
current sql:
short stack: ksedsts()+465 wait history:
* time between current wait and wait #1: 0.000034 sec
1. event: 'SQL*Net message to client'
time waited: 0.000003 sec
wait id: 443 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.012503 sec
2. event: 'gc current grant 2-way'
time waited: 0.000480 sec
wait id: 442 p1: ''=0x1
p2: ''=0x17131
p3: ''=0x2010001
* time between wait #2 and #3: 0.000147 sec
3. event: 'KJC: Wait for msg sends to complete'
time waited: 0.000011 sec
wait id: 441 p1: 'msg'=0x8e881890
p2: 'dest|rcvr'=0x10000
p3: 'mtype'=0xc
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
===============================================================================
Extra information that will be dumped at higher levels:
[level 4] : 1 node dumps -- [LEAF] [LEAF_NW]
[level 5] : 1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[13]/1/14/3121/0x905f70e0/8176/NLEAF/[14]
[14]/1/15/327/0x905f4000/6501/LEAF/
*** 2014-12-01 13:07:45.867
===============================================================================
END OF HANG ANALYSIS
===============================================================================
*** 2014-12-01 13:07:45.867
===============================================================================
HANG ANALYSIS DUMPS:
oradebug_node_dump_level: 3
===============================================================================
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[13]/1/14/3121/0x905f70e0/8176/NLEAF/[14]
[14]/1/15/327/0x905f4000/6501/LEAF/
No processes qualify for dumping.
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================
*** 2014-12-01 13:07:45.868
Oradebug command 'hanganalyze 3' console output:
Hang Analysis in /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
解决办法:
SQL> select * from dba_blockers;
HOLDING_SESSION
---------------
15
SQL> select waiting_session,holding_session from dba_waiters;
WAITING_SESSION HOLDING_SESSION
--------------- ---------------
14 15
会话14被会话15阻塞了
SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=15);
OBJECT_ID SESSION_ID
---------- ----------
87546 14
87546 15
阻塞对象号 87546
SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects where OBJECT_ID = 87546;
OWNER
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------
SYS
DEPT
SQL>
阻塞对象sys.dept
SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||
''';',
a.object_id,
a.session_id,
b.object_name,
c.sid
FROM v$locked_object a, dba_objects b, v$session c
WHERE a.object_id = b.object_id
AND a.SESSION_ID = c.sid(+)
--AND schemaname = 'Unmi'
ORDER BY logon_time;
SQL> SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||
2 ''';',
3 a.object_id,
4 a.session_id,
5 b.object_name,
6 c.sid
7 FROM v$locked_object a, dba_objects b, v$session c
8 WHERE a.object_id = b.object_id
9 AND a.SESSION_ID = c.sid(+)
10 --AND schemaname = 'Unmi'
11 ORDER BY logon_time;
'ALTERSYSTEMKILLSESSION'''||C.SID||''||','||C.SERIAL#||''';'
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_ID SESSION_ID
---------- ----------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SID
----------
alter system kill session '15,327';
87546 15
DEPT
15
alter system kill session '14,3121';
87546 14
DEPT
14
kill:
SQL> alter system kill session '15,327';
环境:
SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
会话1:(删除sys.dept表中的一行数据,但不做commit或者rollback操作)
user:sys
SQL> set linesize 150
SQL> set pagesize 150
SQL> select * from dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> delete from dept where empno=7369;
1 row deleted.
SQL>
会话2: 重新打开一个会话,删除同一行记录,记过被HOLDING
user:sys
[oracle@rac1 ~]:orcl1> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 1 13:05:54 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> delete from dept where empno=7369;
会话3:(做hanganalyze)
user:sys
[oracle@rac1 ~]:orcl1> sqlplus "/as sysdba"
SQL>
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
查看/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc文件信息
Trace file /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/ora11g
System name: Linux
Node name: rac1
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 8377, image: oracle@rac1 (TNS V1-V3)
*** 2014-12-01 13:07:07.479
*** SESSION ID:(143.3379) 2014-12-01 13:07:07.479
*** CLIENT ID:() 2014-12-01 13:07:07.479
*** SERVICE NAME:(SYS$USERS) 2014-12-01 13:07:07.479
*** MODULE NAME:(sqlplus@rac1 (TNS V1-V3)) 2014-12-01 13:07:07.479
*** ACTION NAME:() 2014-12-01 13:07:07.479
Processing Oradebug command 'SETMYPID'
*** 2014-12-01 13:07:07.517
Oradebug command 'SETMYPID' console output:
*** 2014-12-01 13:07:17.737
Processing Oradebug command 'unlimit'
*** 2014-12-01 13:07:17.737
Oradebug command 'unlimit' console output:
*** 2014-12-01 13:07:41.056
Processing Oradebug command 'hanganalyze 3'
*** 2014-12-01 13:07:45.793
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): orcl.orcl1
oradebug_node_dump_level: 3
analysis initiated by oradebug
os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [ 13:07:45 ]
NOTE: scheduling delay has not been sampled for 0.715229 secs 0.000000 secs from [ 13:07:41 - 13:07:46 ], 5 sec avg
0.000000 secs from [ 13:06:46 - 13:07:46 ], 1 min avg
0.000000 secs from [ 13:02:46 - 13:07:46 ], 5 min avg
vktm time drift history
05:00:00.267 HR time stalled at 1417381211181168
05:00:00.451 HR stall ended at 1417381211365752 drift 184584 us
05:00:00.622 LR time stalled at 1417381200
05:00:01.624 LR stall ended at 1417381201 drift 1 us
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl1)
os id: 8176
process id: 36, oracle@rac1 (TNS V1-V3)
session id: 14
session serial #: 3121
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x60019
p3: 'sequence'=0x4b9
time in wait: 1 min 14 sec
timeout after: never
wait id: 9
blocking: 0 sessions
current sql: delete from dept where empno=7369
short stack: ksedsts()+465 wait history:
* time between current wait and wait #1: 0.018035 sec
1. event: 'SQL*Net message from client'
time waited: 32.086352 sec
wait id: 8 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000001 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 7 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000040 sec
3. event: 'SQL*Net message from client'
time waited: 0.000140 sec
wait id: 6 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (orcl.orcl1)
os id: 6501
process id: 48, oracle@rac1 (TNS V1-V3)
session id: 15
session serial #: 327
}
which is waiting for 'SQL*Net message from client' with wait info:
{
p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
time in wait: 1 min 27 sec
timeout after: never
wait id: 444
blocking: 1 session
current sql:
short stack: ksedsts()+465 wait history:
* time between current wait and wait #1: 0.000034 sec
1. event: 'SQL*Net message to client'
time waited: 0.000003 sec
wait id: 443 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.012503 sec
2. event: 'gc current grant 2-way'
time waited: 0.000480 sec
wait id: 442 p1: ''=0x1
p2: ''=0x17131
p3: ''=0x2010001
* time between wait #2 and #3: 0.000147 sec
3. event: 'KJC: Wait for msg sends to complete'
time waited: 0.000011 sec
wait id: 441 p1: 'msg'=0x8e881890
p2: 'dest|rcvr'=0x10000
p3: 'mtype'=0xc
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
===============================================================================
Extra information that will be dumped at higher levels:
[level 4] : 1 node dumps -- [LEAF] [LEAF_NW]
[level 5] : 1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[13]/1/14/3121/0x905f70e0/8176/NLEAF/[14]
[14]/1/15/327/0x905f4000/6501/LEAF/
*** 2014-12-01 13:07:45.867
===============================================================================
END OF HANG ANALYSIS
===============================================================================
*** 2014-12-01 13:07:45.867
===============================================================================
HANG ANALYSIS DUMPS:
oradebug_node_dump_level: 3
===============================================================================
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[13]/1/14/3121/0x905f70e0/8176/NLEAF/[14]
[14]/1/15/327/0x905f4000/6501/LEAF/
No processes qualify for dumping.
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================
*** 2014-12-01 13:07:45.868
Oradebug command 'hanganalyze 3' console output:
Hang Analysis in /app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_8377.trc
解决办法:
SQL> select * from dba_blockers;
HOLDING_SESSION
---------------
15
SQL> select waiting_session,holding_session from dba_waiters;
WAITING_SESSION HOLDING_SESSION
--------------- ---------------
14 15
会话14被会话15阻塞了
SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=15);
OBJECT_ID SESSION_ID
---------- ----------
87546 14
87546 15
阻塞对象号 87546
SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects where OBJECT_ID = 87546;
OWNER
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------
SYS
DEPT
SQL>
阻塞对象sys.dept
SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||
''';',
a.object_id,
a.session_id,
b.object_name,
c.sid
FROM v$locked_object a, dba_objects b, v$session c
WHERE a.object_id = b.object_id
AND a.SESSION_ID = c.sid(+)
--AND schemaname = 'Unmi'
ORDER BY logon_time;
SQL> SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||
2 ''';',
3 a.object_id,
4 a.session_id,
5 b.object_name,
6 c.sid
7 FROM v$locked_object a, dba_objects b, v$session c
8 WHERE a.object_id = b.object_id
9 AND a.SESSION_ID = c.sid(+)
10 --AND schemaname = 'Unmi'
11 ORDER BY logon_time;
'ALTERSYSTEMKILLSESSION'''||C.SID||''||','||C.SERIAL#||''';'
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_ID SESSION_ID
---------- ----------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SID
----------
alter system kill session '15,327';
87546 15
DEPT
15
alter system kill session '14,3121';
87546 14
DEPT
14
kill:
SQL> alter system kill session '15,327';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-1352679/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29500582/viewspace-1352679/