DB Lock & Current 트랜잭션
Tibero DB Lock과 트랜잭션에 대하여 설명합니다.
Current 세션 수
대상 데이터베이스에 접속되어 있는 모든 세션과 현재 작업 중인 세션의 수, Total WPM(Working Process Memory)을 확인합니다.
세션의 상태는 READY, RUNNING, TX_RECOVERING 등이 있습니다.
Single DB
다음은 Single DB 쿼리 작성 예입니다.
SELECT to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time",
"Working Process Memory",
a.tots "Total Session" ,
b.run "Running Session"
FROM (SELECT SUM(pga_used_mem) "Working Process Memory",
COUNT(*) tots
FROM v$session) a,
(SELECT COUNT(*) run
FROM v$session
WHERE status='RUNNING') b;결과는 아래와 같습니다.
Time Working Process Memory Total Session Running Session
------------------- ----------------------- -------------- ---------------
2023/07/04 14:21:45 8872424 14 10
1 row selected.TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT a.INST_ID,
to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time",
"Work Proc Memory",
a.tots "Tot Session",
b.run "Run Session"
FROM (SELECT INST_ID,
SUM(pga_used_mem) "Work Proc Memory",
COUNT(*) tots
FROM gv$session
group by inst_id) a,
(SELECT INST_ID,
COUNT(*) run
FROM gv$session
WHERE status='RUNNING'
group by inst_id) b
WHERE a.INST_ID = b.INST_ID(+);결과는 아래와 같습니다.
INST_ID Time Work Proc Memory Tot Session Run Session
-------- -------------------- ----------------- ------------ ------------
1 2023/07/04 14:24:59 8703984 14 11
2 2023/07/04 14:24:59 653512 5 1
2 rows selected.각 항목에 대한 설명은 다음과 같습니다.
항목
설명
INST_ID
Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Time
쿼리를 날린 현재 시간
Work Proc Memory
현재 모든 세션이 사용하고 있는 PGA의 전체 크기
Tot Session
접속 중인 총 세션의 수
Run Session
실제 작업 중인 세션의 수
총 세션의 상당수가 Running 세션이라면 Lock과 Current 트랜잭션 정보를 확인하여 적절한 조치를 합니다.
Current 트랜잭션
현재 진행 중인 트랜잭션들의 목록과 관련 정보를 확인합니다.
Single DB
다음은 Single DB 쿼리 작성 예입니다.
SELECT distinct vs.sid,
vs.serial#,
vs.username,
va.object,
vs.status,
vt.used_blk,
vt.usn,
vt.start_time,
floor(mod((sysdate - vt.start_time)*24, 24)) || ':'||
lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'||
lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time",
vst.sql_text
FROM v$session vs,
vt_transaction vt,
v$sqltext vst,
v$access va
WHERE vt.sess_id = vs.sid
and vt.sess_id = va.sid
and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id
ORDER BY 1;결과는 아래와 같습니다.
SID SERIAL# USERNAME OBJECT STATUS USED_BLK USN START_TIME Transaction Tim SQL_TEXT
----- -------- --------- ---------------- -------- --------- ------- ----------- ----------------- --------------
135 9907 TPCC BMSQL_NEW_ORDER RUNNING 1 9 2023/07/04 0:00:05 DELETE FROM bmsql_new_order
WHERE no_w_id = ? AND no_d_id = ?
AND no_o_id = ? TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT distinct vs.inst_id,
vs.sid,
vs.serial#,
vs.username,
va.object,
vs.status,
vt.used_blk,
vt.usn,
vt.start_time,
floor(mod((sysdate - vt.start_time)*24, 24)) || ':'||
lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'||
lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time",
vst.sql_text
FROM gv$session vs,
gv$transaction vt,
gv$sqltext vst,
gv$access va
WHERE vt.sess_id = vs.sid
and vt.sess_id = va.sid
and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id
ORDER BY 1, 2;결과는 아래와 같습니다.
INST_ID SID SERIAL# USERNAME OBJECT STATUS USED_BLK USN START_TIME Transaction Tim SQL_TEXT
------- ------ --------- -------- --------------- --------- --------- ----- ---------- ----------------- ------------
1 152 10785 TPCC BMSQL_NEW_ORDER RUNNING 1 4 2023/07/04 0:00:16 DELETE FROM bmsql_new_order
2 134 97 TPCC BMSQL_NEW_ORDER READY 1 8 2023/07/04 0:00:02 select * from tab각 항목에 대한 설명은 다음과 같습니다.
항목
설명
INST_ID
Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID
트랜잭션을 수행 중인 세션의 ID
USERNAME
트랜잭션을 수행 중인 세션의 사용자
OBJECT
현재 동작 중인 트랜잭션에서 사용 중인 Table 목록 (하나의 트랜잭션에서 여러Table을 Access할 경우 Table마다 하나의 Row로 출력)
STATUS
트랜잭션을 수행하고 있는 세션의 상태 정보
USED_BLK
현재 트랜잭션이 사용 중인 Block의 개수
START_TIME
트랜잭션이 시작된 시간
Transaction Time
트랜잭션이 진행된 시간
SQL_TEXT
현재 수행 중인 SQL 내용
현재 트랜잭션이 사용 중인지 확인하고 장시간 Commit이나 Rollback 없이 진행되는 트랜잭션의 경우 애플리케이 션의 잘못된 코딩인지 아니면 관리자나 개발자의 실수인지를 판단하여 목적 없는 트랜잭션의 Commit 또는 Rollback을 권고합니다.
Current SQL 정보 (For 세션)
현재 세션별 수행되고 있는 SELECT 및 DML 구문을 확인합니다.
Single DB
다음은 Single DB 쿼리 작성 예입니다.
SELECT sid,
serial#,
'{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL
FROM v$session vs,
v$sqltext vst
WHERE vs.sql_id=vst.sql_id
GROUP BY SID, SERIAL#;결과는 아래와 같습니다.
SID SERIAL# SQL
----- ---------- ------------------------------------------------------------------------
134 12535 {DELETE FROM bmsql_new_order WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
143 12549 {UPDATE bmsql_order_line SET ol_delivery_d = ? WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?}TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT max(vs.inst_id) inst_id,sid,
serial#,
'{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL
FROM gv$session vs,
gv$sqltext vst
WHERE vs.sql_id=vst.sql_id
GROUP BY SID, SERIAL#;결과는 아래와 같습니다.
INST_ID SID SERIAL# SQL
---------- ----- -------- ---------------------------------------------------------------
1 134 12535 {DELETE FROM bmsql_new_order WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
2 143 12223 {SELECT"INST_ID","HASH_VALUE","PLAN_HASH_VALUE","SQL_ID","CHILD_SELECT "INST_ID","HASH_VALUE","PLAN
_HASH_VALUE","SQL_ID","CHILD_NUMBER","COMMAND_TYPE","PIECE","SQL_TEXT" FROM (select
instance_NUMBER"
,"COMMAND_TYPE","PIECE","SQL_TEXT" FROM (select instance_id()
as inst_id, "V"."HASH_VALUE", "V"."PLA
N_HASH_VALUE", "V"."Sid() as inst_id, "V"."HASH_VALUE",
"V"."PLAN_HASH_VALUE", "V"."SQL_ID", "V"."CH
ILD_NUMBER", "V"."COMMAND_TYPE", "V"."PIECE", "V"QL_ID",
"V"."CHILD_NUMBER", "V"."COMMAND_TYPE", "V"
."PIECE", "V"."SQL_TEXT" from v$sqltext v) /*GV$SQLTEXT*/
QB_006 WHERE ("SQL_."SQL_TEXT" from v$sqlt
ext v) /*GV$SQLTEXT*/ QB_006 WHERE ("SQL_ID" IS NOT NULL) ID"
IS NOT NULL) } 각 항목에 대한 설명은 다음과 같습니다.
항목
설명
INST_ID
Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID
세션 ID
SERIAL#
세션의 Serial (같은 세션 ID라도 사용자가 바뀌면 Serial도 변경됨)
SQL
현재 수행 중인 SQL 내용
현재 활성화된 세션의 SQL 구문을 확인하여 불량 SQL문이나 루프문으로 DB에 부하를 주고 있는 세션이 있다면 그 쿼리를 찾아내고 해당 세션에 대한 적절한 조치를 합니다.
WLOCK 정보 (For 세션)
현재 실행 중인 세션의 트랜잭션 시간 및 Lock 모드를 확인합니다.
Single DB
다음은 Single DB 쿼리 작성 예입니다.
SELECT s.sess_id "SID",
s.serial_no "SERIAL#",
s.status "STATUS",
s.user_name "USER",
o.object_name,
FLOOR((sysdate - vt.start_time)*24) || ':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "LOCK_TIME",
DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4,
'[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "LOCK_MODE",
NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number,
s.prev_child_number) "SQL_ID"
FROM vt_wlock l,
vt_session s,
dba_objects o ,
vt_transaction vt
WHERE l.type='WLOCK_DML'
AND l.sess_id = s.vtr_tid
AND l.id1 = o.object_id (+)
AND l.sess_id = vt.sess_id order by "LOCK_TIME" DESC;결과는 아래와 같습니다.
SID SERIAL# STATUS USER OBJECT_NAME LOCK_TIME LOCK_MODE SQL_ID
----- --------- -------- ------ -------------------- --------- ------------- -------------------
140 14497 RUNNING TPCC BMSQL_NEW_ORDER 0:01:45 [2]Row- X(RX) 7ptad1jfq93j6/152
135 14491 RUNNING TPCC BMSQL_OORDER 0:00:09 [2]Row- X(RX) 3s4c9v1c9xzc5/155
135 14491 RUNNING TPCC BMSQL_ORDER_LINE 0:00:09 [2]Row- X(RX) 3s4c9v1c9xzc5/155
135 14491 RUNNING TPCC BMSQL_CUSTOMER 0:00:09 [2]Row- X(RX) 3s4c9v1c9xzc5/155
137 14495 RUNNING TPCC BMSQL_NEW_ORDER 0:00:06 [2]Row- X(RX) 7ptad1jfq93j6/152TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT vt.inst_id,
s.sess_id "SID",
s.serial_no "SERIAL#",
s.status "STATUS",
s.user_name "USER",
o.object_name,
FLOOR((sysdate - vt.start_time)*24) || ':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "LOCK_TIME",
DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4,
'[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "LOCK MODE",
NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number,
s.prev_child_number) "SQL_ID"
FROM vt_wlock l,
vt_session s,
dba_objects o ,
gv$transaction vt
WHERE l.type='WLOCK_DML'
AND l.sess_id = s.vtr_tid
AND l.id1 = o.object_id (+)
AND l.sess_id = vt.sess_id
ORDER BY "LOCK_TIME" DESC;결과는 아래와 같습니다.
INST_ID SID SERIAL# STATUS USER OBJECT_NAME LOCK_TIME LOCK MODE SQL_ID
---------- ----- ---------- -------- --------------- -------------------- --------------- ------------- ------------------------------
1 136 196 RUNNING TPCC BMSQL_OORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78
1 136 196 RUNNING TPCC BMSQL_NEW_ORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78
2 137 198 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57
2 143 210 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57DML Lock 관련 정보에 대한 각 항목의 설명은 다음과 같습니다.
항목
설명
INST_ID
Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID
트랜잭션을 수행 중인 세션 ID
SERIAL#
트랜잭션을 수행 중인 세션 일련번호
STATUS
트랜잭션을 수행하고 있는 세션의 상태 정보
USER
트랜잭션을 수행 중인 세션의 사용자
OBJECT_NAME
DML Lock이 걸려있는 오브젝트
LOCK_TIME
DML Lock이 걸려있는 시간
LOCK_MODE
DML Lock 종류 (RS=1, RX=2, S=3, SRX=4, X=5)
SQL_ID
현재 수행 중인 SQL_ID
WLOCK Holder & Waiter 정보
Blocking 하고 있는 Lock 정보와 Waiting 하고 있는 Lock 정보를 모니터링합니다.
Single DB
다음은 Single DB 쿼리 작성 예입니다.
SELECT bs.user_name "Blocking User" ,
ws.user_name "Waiting User" ,
bs.sess_id "Blocking Sid" ,
ws.sess_id "Waiting Sid" ,
wk.type "Lock Type" ,
hk.lmode "Holding mode" ,
wk.lmode "Request mode" ,
nvl(bs.sql_id, bs.prev_sql_id) "Sql_id"
FROM vt_wlock hk,
vt_session bs,
vt_wlock wk,
vt_session ws
WHERE wk.status = 'WAITER'
and hk.status = 'OWNER'
and hk.lmode > 1
and wk.type = hk.type
and wk.id1 = hk.id1
and wk.id2 = hk.id2
and wk.sess_id = ws.vtr_tid
and hk.sess_id = bs.vtr_tid
ORDER BY 1;결과는 아래와 같습니다.
locking User Waiting User Blocking Sid Waiting Sid Lock Type Holding mode Request mode Sql_id
------------- --------------- ------------ ----------- --------------- ------------ ------------- ------------------------------
TPCC TPCC 133 146 WLOCK_TX 5 6 3s4c9v1c9xzc5
TPCC TPCC 133 147 WLOCK_TX 5 6 3s4c9v1c9xzc5
TPCC TPCC 133 148 WLOCK_TX 5 6 3s4c9v1c9xzc5
TPCC TPCC 133 149 WLOCK_TX 5 6 3s4c9v1c9xzc5
TPCC TPCC 133 150 WLOCK_TX 5 6 3s4c9v1c9xzc5항목에 대한 설명은 아래와 같습니다.
항목
설명
INST_ID
Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Blocking User
Lock을 붙잡고 있는 Holder
Waiting User
Lock을 기다리고 있는 사용자
Blocking Sid
Lock을 붙잡고 있는 세션의 SID
Waiting Sid
Lock을 기다리고 있는 세션의 SID
Lock Type
기다리고있는 Wlock의 타입
Holding mode
Lock을 Holding하고 있는 모드
Request mode
Lock을 Request하고 있는 모드
Sql_id
실행한 쿼리의 ID
Lock에 걸려 대기 중인 세션 및 현황 정보를 모니터링합니다.
Lock에 걸려있는 세션을 확인하고 해당 세션을 끊거나 애플리케이션 상 잘못된 구현 또는 사용자의 잘못된 쿼리로 장시간 Lock이 걸려있는 부분을 확인하고 Commit 또는 Rollback을 권고합니다.
LOCK_TYPE이 WLOCK_TX일 경우 Row Level 단위 Lock에 걸려있을 확률이 높습니다.
Last updated