Oracle 视图 V$WAIT_CHAINS 官方解释,作用,如何使用详细说明

本站中文解释

Oracle视图V$WAIT_CHAINS被用来查看当前系统阻塞的时间段及其对应的父子等待链路信息,帮助终止一个死锁。

V$WAIT_CHAINS视图表达了SID1在SID2上的停顿,SID2在SID3上的停顿,并显示了Oracle用来表示这个过程的树状结构。它可以从第一个阻塞的SID开始慢慢追溯到最后一个阻塞的SID,以及最终的死锁SID。

使用V$WAIT_CHAINS视图,OEM可以检测和解决死锁问题。有关死锁检测和解决的详细步骤如下:

1. 在OEM中运行“死锁报告”任务来获取系统阻塞的时间段及其对应的等待链路信息。

2. 运行V$WAIT_CHAINS视图,来查看阻塞的轮次和关系。

3. 根据获取的等待链路信息来分析死锁,并对死锁SID追踪对应的线程以强制终止死锁。

4. 再运行一次“死锁报告”任务,查看死锁是否被处理。

官方英文解释

V$WAIT_CHAINS displays information about blocked sessions. A wait chain is composed of sessions that are blocked by one another. Each row represents a blocked and blocker session pair. If a wait chain is not a cyclical wait chain, then the last row for the chain does not have a blocker.
Column Datatype Description

CHAIN_ID

NUMBER

A number identifying the wait chain

CHAIN_IS_CYCLE

VARCHAR2(5)

Indicates whether the final blocked session in the wait chain is blocked by the initial blocked session (TRUE) or not (FALSE)

CHAIN_SIGNATURE

VARCHAR2(801)

An Oracle-specific text signature of the wait chain. This signature can be used to identify similar wait chains.

CHAIN_SIGNATURE_HASH

NUMBER

A numeric representation of CHAIN_SIGNATURE

INSTANCE

NUMBER

Blocked session’s instance identifier

OSID

VARCHAR2(25)

Blocked session’s operating system process identifier

PID

NUMBER

Blocked session’s Oracle process identifier

SID

NUMBER

Blocked session’s Oracle session identifier

SESS_SERIAL#

NUMBER

Blocked session’s Oracle session serial number

PDB_ID

NUMBER

Blocked session’s PDB identifier

PDB_NAME

VARCHAR2(31)

Blocked session’s PDB name

BLOCKER_IS_VALID

VARCHAR2(5)

Indicates whether the blocked session has a blocker (TRUE) or not (FALSE)

BLOCKER_INSTANCE

NUMBER

Blocker session’s instance identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_OSID

VARCHAR2(25)

Blocker session’s operating system process identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_PID

NUMBER

Blocker session’s Oracle process identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_SID

NUMBER

Blocker session’s Oracle session identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_SESS_SERIAL#

NUMBER

Blocker session’s Oracle session serial number; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_PDB_ID

NUMBER

Blocker session’s PDB identifier

BLOCKER_PDB_NAME

VARCHAR2(31)

Blocker session’s PDB name

BLOCKER_CHAIN_ID

NUMBER

If not NULL, then the blocker session is a member of another chain specified by this chain identifier. For the remaining wait chain information, see the wait chain with the specified CHAIN_ID.

IN_WAIT

VARCHAR2(5)

Indicates whether the blocked session is in a wait (TRUE) or not (FALSE)

TIME_SINCE_LAST_WAIT_SECS

NUMBER

Number of seconds since the last time the blocked session waited; NULL if IN_WAIT is TRUE

WAIT_ID

NUMBER

A number identifying the wait; NULL if IN_WAIT is FALSE

WAIT_EVENT

NUMBER

Resource or event number for which the blocked session is waiting; NULL if IN_WAIT is FALSE

WAIT_EVENT_TEXT

VARCHAR2(64)

Resource or event for which the blocked session is waiting; NULL if IN_WAIT is FALSE

P1

NUMBER

First additional wait parameter; NULL if IN_WAIT is FALSE

P1_TEXT

VARCHAR2(64)

Description of the first additional wait parameter; NULL if IN_WAIT is FALSE

P2

NUMBER

Second additional wait parameter; NULL if IN_WAIT is FALSE

P2_TEXT

VARCHAR2(64)

Description of the second additional wait parameter; NULL if IN_WAIT is FALSE

P3

NUMBER

Third additional wait parameter; NULL if IN_WAIT is FALSE

P3_TEXT

VARCHAR2(64)

Description of the third additional wait parameter; NULL if IN_WAIT is FALSE

IN_WAIT_SECS

NUMBER

Seconds the blocked session has been in the current wait; NULL if IN_WAIT is FALSE

TIME_REMAINING_SECS

NUMBER

Seconds remaining until the blocked session ends its wait (-1 if the blocked session can indefinitely wait); NULL if IN_WAIT is FALSE

NUM_WAITERS

NUMBER

Number of sessions waiting for the blocked session

ROW_WAIT_OBJ#

NUMBER

Object ID for the table containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE

ROW_WAIT_FILE#

NUMBER

Identifier for the data file containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

ROW_WAIT_BLOCK#

NUMBER

Identifier for the block containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

ROW_WAIT_ROW#

NUMBER

Current row being locked; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

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


数据运维技术 » Oracle 视图 V$WAIT_CHAINS 官方解释,作用,如何使用详细说明