https://www.cnblogs.com/grey-wolf/p/10119219.html
Oracle中V$SESSION等各表的字段解释,Oracle官方解释
阅读目录
一、常用的视图
1.会话相关视图
2.锁相关的视图:
二、V S E S S I O N 三 、 V SESSION 三、V SESSION三、VSESSION_WAIT
- 查询会话正在等待行锁的会话:
三、V L O C K E D O B J E C T 1. 简 介 及 表 字 段 2. 查 询 持 有 锁 的 会 话 3. 查 询 持 有 锁 的 会 话 正 在 执 行 的 s q l 4. 根 据 对 象 名 查 询 持 有 该 锁 对 象 的 会 话 5. 根 据 对 象 名 查 询 持 有 该 锁 对 象 的 会 话 及 s q l 四 、 V LOCKED_OBJECT 1.简介及表字段 2.查询持有锁的会话 3.查询持有锁的会话正在执行的sql 4.根据对象名查询持有该锁对象的会话 5.根据对象名查询持有该锁对象的会话及sql 四、V LOCKEDOBJECT1.简介及表字段2.查询持有锁的会话3.查询持有锁的会话正在执行的sql4.根据对象名查询持有该锁对象的会话5.根据对象名查询持有该锁对象的会话及sql四、VLOCK
1.简介
2.一个本地的死锁案例:
2.1 会话432的持有的锁,被锁的对象,正在执行的sql:
回到顶部
一、常用的视图
1.会话相关视图
View Description
V$PROCESS
Contains information about the currently active processes
V$SESSION
Lists session information for each current session
V$SESS_IO
Contains I/O statistics for each user session
V$SESSION_LONGOPS
Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.
V$SESSION_WAIT
Displays the current or last wait for each session
V$SESSION_WAIT_HISTORY
Lists the last ten wait events for each active session
V$WAIT_CHAINS
Displays information about blocked sessions
V$SESSTAT
Contains session statistics
V$RESOURCE_LIMIT
Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA
Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution
2.锁相关的视图:
View Description
V$LOCK
Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS
Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS
Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS
Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS
Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK
Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL
Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch
V$LOCKED_OBJECT
Lists all locks acquired by every transaction on the system
V$SESSION_WAIT
Lists the resources or events for which active sessions are waiting
V$SYSSTAT
Contains session statistics
V$RESOURCE_LIMIT
Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA
Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH
Contains statistics for nonparent latches and summary statistics for parent latches
回到顶部
二、V$SESSION
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-SESSION.html#GUID-28E2DC75-E157-4C0A-94AB-117C205789B9
回到顶部
三、V$SESSION_WAIT
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-SESSION_WAIT.html#GUID-4EDAB293-F3FC-40FE-BC75-4FEE6A4D7705
这里面有个event字段,event字段的枚举值可以存在下面的表中,可以这样查询:
SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name
- 查询会话正在等待行锁的会话:
select event,sid,p1,p2,p3 from v$session_wait where event=‘enq: TX - row lock contention’;
回到顶部
三、V$LOCKED_OBJECT
1.简介及表字段
该表列出每个事务获取到的所有锁。它可以展示出哪个会话正在持有锁,在什么对象上持有锁,以及锁的模式。
Column Datatype Description
XIDUSN
NUMBER
Undo segment number
XIDSLOT
NUMBER
Slot number
XIDSQN
NUMBER
Sequence number
OBJECT_ID
NUMBER
Object ID being locked
SESSION_ID
NUMBER
Session ID
ORACLE_USERNAME
VARCHAR2(128)
Oracle user name
OS_USER_NAME
VARCHAR2(128)
Operating system user name
PROCESS
VARCHAR2(24)
Operating system process ID
LOCKED_MODE
NUMBER
Lock mode. The numeric values for this column map to these text values for the lock modes for table locks:
0 - NONE: lock requested but not yet obtained
1 - NULL
2 - ROWS_S (SS): Row Share Lock
3 - ROW_X (SX): Row Exclusive Table Lock
4 - SHARE (S): Share Table Lock
5 - S/ROW-X (SSX): Share Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock
See Also: Oracle Database Concepts for more information about lock modes for table locks
CON_ID
NUMBER
The ID of the container to which the data pertains. Possible values include:
0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
1: This value is used for rows containing data that pertain to only the root
n: Where n is the applicable container ID for the rows containing data
2.查询持有锁的会话
SELECT USERNAME, LOCKWAIT, STATUS, MACHINE, PROGRAM
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT);
3.查询持有锁的会话正在执行的sql
复制代码
SELECT
SQL_TEXT
FROM
V
S
Q
L
W
H
E
R
E
H
A
S
H
V
A
L
U
E
I
N
(
S
E
L
E
C
T
S
Q
L
H
A
S
H
V
A
L
U
E
F
R
O
M
V
SQL WHERE HASH_VALUE IN ( SELECT SQL_HASH_VALUE FROM V
SQLWHEREHASHVALUEIN(SELECTSQLHASHVALUEFROMVSESSION
WHERE
SID IN (
SELECT
SESSION_ID
FROM
V$LOCKED_OBJECT
)
);
复制代码
4.根据对象名查询持有该锁对象的会话
复制代码
SELECT o.object_name,s.sid, s.serial#
FROM v
l
o
c
k
e
d
o
b
j
e
c
t
l
,
d
b
a
o
b
j
e
c
t
s
o
,
v
locked_object l, dba_objects o, v
lockedobjectl,dbaobjectso,vsession s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND o.object_name='SEATINFORMATION';
SEATINFORMATION为本地表的名字。
复制代码
5.根据对象名查询持有该锁对象的会话及sql
SELECT o.object_name,s.sid, s.serial#,s.SQL_HASH_VALUE,v.*
FROM v
l
o
c
k
e
d
o
b
j
e
c
t
l
,
d
b
a
o
b
j
e
c
t
s
o
,
v
locked_object l, dba_objects o, v
lockedobjectl,dbaobjectso,vsession s, V$SQL v
WHERE l.object_id = o.object_id
AND l.session_id = s.sid and s.SQL_HASH_VALUE = v.HASH_VALUE
AND o.object_name='INCIDENTINFORMATION';
回到顶部
四、V$LOCK
1.简介
该视图列出当前被oracle持有的锁。
Column Datatype Description
ADDR
RAW(4 | 8)
Address of lock state object
KADDR
RAW(4 | 8)
Address of lock
SID
NUMBER
Identifier for session holding or acquiring the lock
TYPE
VARCHAR2(2)
Type of user or system lock
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
The system type locks are listed in Table 8-1. Be aware that not all types of locks are documented. To find a complete list of locks for the current release, query the V L O C K T Y P E d a t a d i c t i o n a r y v i e w , d e s c r i b e d o n " V LOCK_TYPE data dictionary view, described on "V LOCKTYPEdatadictionaryview,describedon"VLOCK_TYPE".
– 我们主要关注TX 和TM 两种类型的锁
–UL 锁用户自己定义的,一般很少会定义,基本不用关注
ID1
NUMBER
Lock identifier #1 (depends on type)
ID2
NUMBER
Lock identifier #2 (depends on type)
— 当lock type 为TM 时,id1 为DML-locked object 的object_id
— 当lock type 为TX 时,id1 为usn+slot ,而id2 为seq 。
– 当lock type 为其它时,不用关注
LMODE
NUMBER
Lock mode in which the session holds 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)–大于0时表示 当前会话以某种模式 占有 该锁,等于0时表示当前会话正在等待该锁资源,即表示该会话被阻塞。
–大于0时表示 当前会话以某种模式 占有 该锁,等于0时表示当前会话正在等待该锁资源,即表示该会话被阻塞。
往往在发生TX 锁时,伴随着TM 锁,比如一个sid=9 会话拥有一个TM 锁,一般会拥有一个或几个TX锁,但他们的id1 和id2 是不同的,请注意
REQUEST
NUMBER
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)
– 大于0时,表示当前会话被阻塞,其它会话占有该锁的模式
CTIME
NUMBER
Time since current mode was granted
BLOCK
NUMBER
Indicates whether the lock in question is blocking other processes. The possible values are:
0 - The lock is not blocking any other processes
1 - The lock is blocking other processes
2 - The lock is not blocking any blocked processes on the local node, but it may or may not be blocking processes on remote nodes. This value is used only in Oracle Real Application Clusters (Oracle RAC) configurations (not in single instance configurations).
CON_ID
NUMBER
The ID of the container to which the data pertains. Possible values include:
0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
1: This value is used for rows containing data that pertain to only the root
n: Where n is the applicable container ID for the rows containing data
2.一个本地的死锁案例:
从oracle服务器的trace日志中看到:
会话432和会话188互相等待。
以下是相关信息:
2.1 会话432的持有的锁,被锁的对象,正在执行的sql:
SELECT * FROM V$LOCK l where l.sid = 432;
https://logicalread.com/diagnosing-oracle-wait-for-tx-enqueue-mode-6-mc01/#.XBUGJM7_zIU