检查oracle会话数,Check_oracle_health 之阻塞会话数

Check_oracle_health 之阻塞会话数

sql语句：

SELECT COUNT(*) FROM DBA_BLOCKERS;

或

select INST_ID,

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

CTIME,

BLOCK,

DECODE(BLOCK, 0, '', 'blocker') blocker,

DECODE(request, 0, '', 'waiter') waiter

from gv$lock

where (ID1, ID2, TYPE) in

(select ID1, ID2, TYPE from gv$lock where request > 0)

order by blocker;

执行结果示例如下：

INST_ID     SID TYPE        ID1        ID2 LMODE REQUEST      CTIME BLOCK BLOCKER        WAITER

------- ------- ---- ---------- ---------- ----- ------- ---------- ----- -------------- ------------

1      70 TX        65539    2630730     6       0       2520     1 blocker

1      86 TX        65539    2630730     0       4       1654     0                waiter

稍等一会输入/、回车，再次执行上面的SQL，如阻塞情况不变，则确认SID =70的会话是阻塞会话

分析处理：

使用sql developer执行了更新记录操作后，既没有commit也没有rollback,网络就断开了，造成表或记录被锁住，待到超时后才会被解开，那样都会造成应用操作被阻塞。

可以以Oralce管理员权限用户登录Oracle数据，查询到被锁的对象，然后杀除指定的会话。

用下面的语句查询被锁的对象，可以带上更多约束条件，如schemaname等更精确的匹配。

SELECT a.object_id, a.session_id, b.object_name, c.sid, c.serial#

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;

杀Seesion的SQL语句语法如下：

alter system kill session 'sid, serial#'

如上面查出来的一条记录的sid是53, serial#为663,就执行以下的语句

1.alter system kill session '53,663'

如果要一次性杀死多个会话，一个一个填写sid和serial#十分的繁琐，应该在查询被锁对象的同时拼凑出多条的杀会话语句，以分号分隔，一起复制下来，然后就可以批量的执行了。

拼凑kill语句的方式如下，下面加了一个过滤条件和一个排序，杀除真正关心的表，并且着重注意超时时间过长的会话。

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;

Oracle中查找阻塞与被阻塞SID的方法

在Oracle中,会经常遇到阻塞与被阻塞的情况.

查找阻塞与被阻塞的方法主要有下面几种:

一.通过查找v$lock和v$locked_object

这是最常用的也是最直接的方法

SQL> select sid,block from v$lock where block=1;

SID      BLOCK

---------- ----------

1571          1

1856          1

2274          1

1005          1

SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=1571);

OBJECT_ID SESSION_ID

---------- ----------

74301          8

74301        577

74301        720

74301        856

74301        863

74301       1005

74301       1280

74301       1571

74301       1856

74301       2274

74301       2421

OBJECT_ID SESSION_ID

---------- ----------

74301       4122

12 rows selected.

由上面第一条语句可以看出,SID=252的session阻塞了其他的session

由第二条语句可以得出,SID=252的session阻塞了SID=269的session

二.通过查找dba_waiters和dba_blockers

SQL>  select * from dba_blockers;

HOLDING_SESSION

---------------

4122

856

1571

2274

1005

1856

6 rows selected.

SQL>

比如 4122 属于阻塞会话，阻塞了被的会话

SQL>  select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION

--------------- ---------------

4122             856

2421             856

856             856

863            1005

4122            1571

2421            1571

856            1571

8            1856

720            2274

4122            4122

2421            4122

WAITING_SESSION HOLDING_SESSION

--------------- ---------------

856            4122

12 rows selected.

SQL>

4122 阻塞的会话是856，856属于holding会话

三.在Oracle 10G中可以通过v$session中的blocking_session字段查找

SQL> select sid,blocking_session from v$session where blocking_session is not null;

SID BLOCKING_SESSION

---------- ----------------

8             1856

720             2274

856             1571

863             1005

2421             1571

4122             1571

6 rows selected.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1352680/，如需转载，请注明出处，否则将追究法律责任。