ITPub博客

首页 > 数据库 > Oracle > Oracle 查询锁之间的依赖关系

Oracle 查询锁之间的依赖关系

Oracle 作者:kunlunzhiying 时间:2017-11-22 16:15:40 0 删除 编辑
 
  注释:
  该SQL可查询多会话,非select的DML操作,同时操作A表引起的锁 ..会话之间的关系~
  ....下文有该SQL用到的视图/字段的详细注释..
  ....若想显示其他字段可以按照自己需求增加 ..

SQL:
SELECT DISTINCT S.SID , /*会话的唯一标识,通常要对某个会话进行分析前,首先就需要获得该会话的SID。*/
                --S.SERIAL# /*会话的序号*/,
                S.STATE /*WAIT STATE~*/ ,
                S.BLOCKING_SESSION ,
                --SESSION IDENTIFIER OF THE BLOCKING SESSION. THIS COLUMN IS VALID ONLY IF BLOCKING_SESSION_STATUS HAS THE VALUE VALID.
                S.BLOCKING_SESSION_STATUS STATUS, /*THIS COLUMN PROVIDES DETAILS ON WHETHER THERE IS A BLOCKING SESSION: */
                ( CASE
                  WHEN SQL_TEXT IS NULL /*LO.REQUEST = 0 */
                   THEN
                   '(SID:' || S.SID || ')会话 SQL已跑完'
                  ELSE
                   '(SID:' || S.SID || ')会话 正执行SQL:' || SQL_.SQL_TEXT
                END ) SQL_TEXT /*执行完的SQL'SQL_TEXT标记SQL已跑完,否则标记SQL'*/ ,
                --SQL_.SQL_FULLTEXT SQL全文本,
                S.USERNAME /*创建该会话的用户名*/ ,
                O.OWNER || '.' || O.OBJECT_NAME 锁的对象, --V$SESSION.ROW_WAIT_OBJ#若操作完的该字段值=-1,所以关联的V$LOCKED_OBJECT取锁表
                LO.REQUEST   , -- Lock mode in which the process requests the lock 会话申请的锁的模式
                S.EVENT   ,
                S.MACHINE /*客户端的机器名。*/ ,
                S.LOGON_TIME /*登陆时间*/ ,
                'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';' KILL --若存在锁情况,会用到KILL锁释放~
  FROM V$SESSION S
  LEFT JOIN V$SQL SQL_
    ON SQL_.SQL_ID = S.SQL_ID
  JOIN V$LOCKED_OBJECT L
    ON L.SESSION_ID = S.SID
  JOIN ALL_OBJECTS O
    ON L.OBJECT_ID = O.OBJECT_ID
  JOIN V$LOCK LO
    ON (LO.BLOCK != 0 OR LO.REQUEST != 0 )
    --V$LOCK.block => A value of either 0 or 1, depending on whether or not the lock in question is the blocker
    --V$LOCK.REQUEST => Lock mode in which the process requests the lock:下文有值的意义~ ['0 - none']
  WHERE LO.SID = L.SESSION_ID
   AND LO.SID = S.SID
  ORDER BY S.BLOCKING_SESSION DESC ;


注释:
--视图==官网注释
--v$session        ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223
--V$SQL            ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3043.htm#REFRN30246
--V$LOCK           ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121
--V$LOCKED_OBJECT  ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2030.htm#REFRN30125
--ALL_OBJECTS      ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1158.htm#REFRN20146

--显示字段==官网注释:
V$SESSION.STATE = Wait state :
--WAITING - Session is currently waiting
--WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false
--WAITED SHORT TIME - Last wait was less than a hundredth of a second
--WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column   S.BLOCKING_SESSION ,
--Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
V$SESSION.BLOCKING_SESSION_STATUS = This column provides details on whether there is a blocking session :
--VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns
--NO HOLDER - there is no session blocking this session
--NOT IN WAIT - this session is not in a wait
--UNKNOWN - the blocking session is unknown
V$LOCK.REQUEST = Lock mode in which the process requests the lock :
--0 - none
--1 - null (NULL)
--2 - row-S (SS)
--3 - row-X (SX)
--4 - share (S)
--5 - S/Row-X (SSX)
--6 - exclusive (X)


祝好~

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

请登录后发表评论 登录
全部评论
Oracle搬砖侠,略懂MySQL 、MongoDB运维,积极向自动化,智能化、平台化运运维方向努力。

注册时间:2014-07-28

  • 博文量
    666
  • 访问量
    256099