郭一军,网名guoyJoe,尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com
全部博文(72)
2014年(72)
分类: Oracle
2014-06-19 10:34:55
锁是Oracle管理共享数据库资源并发访问并防止并发数据库事务之间“相互干涉”的核心机制之一。
当应用系统复杂丶业务量大时,经常会出现应用进程之前的锁等待现象,影响系统正常运行。当数据库出现锁等待的情况下快速定位阻塞进程,分析阻塞原因。
数据库的锁有多种类型,每种不同的类型对业务的影响是不一样的,大致可分为以下三类:
DML锁
DDL锁
内部锁与LATCH锁
下面给出每种不同类型的锁的定位分析过程,各种锁处理流程如下:
1丶 TX锁与行锁
TX锁不是行锁,一个事务不管修改了多少行,都只会有一个TX锁。TX锁算是行锁的代表,行锁上发生了等待,会表现为TX锁的等待。行锁是属于事务的,事务开始,行锁产生,事务结束,行锁也被释放。
2丶 行级锁对应用的影响比较小,一般只会影响部份业务或某个特殊的进程。
3丶 模拟重现TX锁
(1)会话181执行如下操作:
gyj@OCM>select distinct sid from v$mystat;
SID
----------
181
gyj@OCM>update t1 set name =\'gyj111\' where id=1;
1 row updated.
修改t1表中的id=1这行记录,不提交。
(2)会话179执行如下操作:
gyj@OCM>select distinct sid from v$mystat;
SID
----------
179
gyj@OCM>update t1 set name =\'gyj111\' where id=1;
修改t1表id=1这行记录,这里被阻塞
(3)使用以下脚本查找数据库中的行级锁信息:
gyj@OCM> col username for a10 gyj@OCM> col program for a25 gyj@OCM> col sid for 9999 gyj@OCM> col SERIAL# for 9999 gyj@OCM> col BLOCKING_INSTANCE for 99 gyj@OCM> 2 from v$session where event=\'enq: TX - row lock contention\'; SID SERIAL# USERNAME PROGRAM STATUS SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION ----- ------- ---------- ------------------------- -------- ------------- ----------------- ---------------- 179 241 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE gxzu79ffmrp35 1 181 |
(4)输出结果说明:
SID: TX锁等待的SID号
SERIAL#: TX锁等待进程的序列号
USERNAME: 数据库用户
PROGRAM: 进程程序名
STATUS: 进程状态
SQL_ID: 正在执行的SQL语句
BLOCING_INSTANCE :阻塞进程所在的实例
BLOCING_SESSION: 阻塞进程SID号
可以得到锁等待进程信息,及阻塞者的SID (注意在RAC中通过blocking_session得到的SID需要减去1,才是实际的SID) 。
4丶 查找阻塞者进程信息
gyj@OCM> col event for a30 gyj@OCM> select sid,serial#,username,program,status,sql_id,event from v$session where sid=\'&sid\'; Enter value for sid: 181 old 1: select sid,serial#,username,program,status,sql_id,event from v$session where sid=\'&sid\' new 1: select sid,serial#,username,program,status,sql_id,event from v$session where sid=\'181\' SID SERIAL# USERNAME PROGRAM STATUS SQL_ID EVENT ----- ------- ---------- ------------------------- -------- ------------- ------------------------------ 181 236 GYJ sqlplus@ocm (TNS V1-V3) INACTIVE SQL*Net message from client |
输出结果说明:
SID:阻塞进程的SID号
SERIAL#:阻塞进程的序列号
USERNAME:数据库用户
PROGRAM:进程程序名
STATUS:进程状态
SQL_ID:正在执行的SQL语句
EVENT:阻塞进程的等待事件
上述进程的的STATUS为INACTIVE,参考处理流程,直接跳到第7步执行。
5丶 如果阻塞者进程为ACTIVE状态,查找阻塞者进程正在执行的SQL语句
gyj@OCM> \'gxzu79ffmrp35\';
SQL_TEXT
----------------------------------------------------------------
update t1 setname=\'aaaaaaa\' where id=1
6丶 如果阻塞者进程为ACTIVE状态,查找阻塞者进程SQL语句执行计划
gyj@OCM> select * from table(dbms_xplan.display_cursor(‘gxzu79ffmrp35’));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f9mwduaxs47kk, child number 0
-------------------------------------
update t1 setname=\'aaaaaaa\' where id=1
Plan hash value:2927627013
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | T1 | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement(level=2)
23 rows selected.
7丶 分析阻塞原因
TX锁阻塞原因一般有两种,一种是阻塞进程没有及时提交事务;另一种是阻塞进程SQL语句执行缓慢。对于第一种情况可找到相关进程结束进程事务,释放锁资源。第二种情况通过分析SQL语句执行计划,找到SQL执行缓慢的原因,提供优化建议,要求开发人员优化SQL语句。
8丶 若情况紧急,可与项目经理和开发人员确认后终止阻塞者进程,释放锁资源。
9丶 TM锁这里就不详细说了网上资料很多。
引用DSI405中的图
1丶 对象锁等待,一般发生在重编译存储过程等对象维护时,相关对象的library cache pin等待。发生该种锁等待时,长时间无法完成存储过程编译。
2丶 使用下面的脚本查到对象锁等待的进程信息
selectsid,serial#,username,program,status,sql_id from v$session where event like\'library cache%\';
输出结果如下:
SID SERIAL# USERNAME PROGRAM STATUS SQL_ID EVENT
---------- ---------------------------------------- -------------------------------------------------------- -----------------------------------------------------------------------------
6577 456 GYJ sqlplus@ocm(TNS V1-V3) INACTIVE 1h3aq2wzhn5n6 SQL*Net message from client
输出结果说明:
SID:阻塞进程的SID号
SERIAL#:阻塞进程的序列号
USERNAME:数据库用户
PROGRAM:进程程序名
STATUS:进程状态
SQL_ID:正在执行的SQL语句
3丶 使用下面语句查找阻塞进程信息
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, \'None\', 1, \'Null\', 2, \'Share\', 3, \'Exclusive\',
\'Unknown\') mode_held,
decode(w.kgllkreq, 0, \'None\', 1, \'Null\', 2, \'Share\', 3, \'Exclusive\',
\'Unknown\') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllkxw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH)
输出结果如下:
WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL
20 45 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleep to system grant execute on p_sleep to system
输出结果说明:
WAITING_SESSION:被阻塞进程号
HOLDING_SESSION:阻塞进程号
LOCK_OR_PIN:锁类型
OBJECT_OWNER:被锁对象属主
OBJECT_NAME:被锁对象名
TYPE:锁定类别
MODE_HELD:阻塞模式
MODE_REQUESTED:请求模式
WAIT_SQL:被阻塞进程正在执行的SQL
HOLD_SQL:阻塞进程正在执行的SQL
4丶 查找阻塞者进程信息
select sid,serial#,username,program,status,sql_id,eventfrom v$session where sid=’&sid’;
5丶 查找阻塞者进程正在执行的SQL语句
select sql_text from v$sqltextwhere sql_id=’&sql_id’ order by piece;
6丶 查找阻塞者进程SQL语句执行计划
select * fromtable(dbms_xplan.display_cursor(‘&sql_id’));
7丶 分析阻塞原因
对象锁阻塞一般在使用PL/SQL DEV等工具进程相关对象操作时容易引发该种锁等待。正常的业务事务不会导致该种等待事件发生。
8丶 若情况紧急,可与项目经理和开发人员确认后终止阻塞者进程,释放锁资源。
引用DSI405中的图
1丶 数据库级别的LATCH锁,往往是由于性能不良的应用程序进程,长时间持有相关LATCH不释放引起。引如热点块问题,数据库运行异常缓慢,数据库HANG住等问题。
2丶 对于热点块问题,使用下面语句查找热点块竞争的进程信息,并将相关信息提交开发人员,要求分析避免数据的热点访问
selectsid,serial#,username,program,status,sql_id from v$session where event like\'%cache buffers chains%\';
输出结果如下所示:
SID SERIAL# USERNAME PROGRAM STATUS SQL_ID
3054 41415 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE 1h3aq2wzhn5n6
7029 51613 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE 1h3aq2wzhn5n6
3064 16713 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE 1h3aq2wzhn5n6
6089 30813 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE 1h3aq2wzhn5n6
3055 50213 GYJ sqlplus@ocm (TNS V1-V3) ACTIVE 1h3aq2wzhn5n6
输出结果说明:
SID:阻塞进程的SID号
SERIAL#:阻塞进程的序列号
USERNAME:数据库用户
PROGRAM:进程程序名
STATUS:进程状态
SQL_ID:正在执行的SQL语句
3丶 检查数据库latch锁等待进程数,如果数据超过50个,并不断增加,则可能导致数据库异常缓慢甚至hang住。用下面语句检查latch等待进程数。
sys@OCM>select sid,serial#,username,program,status,sql_id from v$session where eventlike \'%latch%\';
SID SERIAL# USERNAME PROGRAM STATUS SQL_ID
-------------------- ------------------------------ -------------------------------------------------------- -------------
181 2 GYJ sqlplus@ocm (TNSV1-V3) ACTIVE 1h3aq2wzhn5n6
输出结果说明:
SID:阻塞进程的SID号
SERIAL#:阻塞进程的序列号
USERNAME:数据库用户
PROGRAM:进程程序名
STATUS:进程状态
SQL_ID:正在执行的SQL语句
4丶 取得数据库hang analyze trace
sqlplus\'/as sysdba\'
oradebugsetmypid
oradebugsetinst all;
oradebug-g def hanganalyze 3
5丶 分析数据库hang analyze trace 信息,找出阻塞进程的源头
6丶 如果数据库还能查v$session进程信息,则查找出源头进程的相关信息及SQL语句。
selectb.spid,a.sid,a.serial#,a.program,a.machine,a.sql_id,a.event,a.PREV_SQL_ID fromv$session a,v$process b wherea.paddr=b.addr and a.sid=&sid;
7丶 用下面的方法对源头进程做DUMP。
$sqlplus \'/ as sysdba\'
SQL>oradebug setospid xxx (操作系统进程ID)
SQL>oradebug unlimit
SQL>oradebug dump processstate 10
SQL>exit
DUMP出来的文件在USER_DUMP_DEST所指向的目录,将相关文件取出,并提交ORACLE分析。
8丶 与项目经理和开发人员确认后,中止源头进程。数据库可恢复正常。
9丶 分析源头进程信息,获得阻塞原因,如SQL语句执行缓慢,进程未及时结束事务等。并提供优化建议,提给开发人员改进。
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM:
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
DSI&Core Search(QQ群):127149411