Oracle教程 2023-02-25

Oracle 视图 DBA_HIST_MUTEX_SLEEP 官方解释，作用，如何使用详细说明

_HISTORY

Oracle视图DBA_HIST_MUTEX_SLEEP_HISTORY用于查看互斥锁休眠历史记录，可用来评估指定等待的互斥锁休眠的时长和产生原因。该视图报告每一次互斥锁休眠操作的元信息，如等待时间，等待类型，互斥锁号，活动会话及相关SQL语句等。

使用方法：

1. 首先定位互斥锁：通过DBA_WAITERS和DBA_BLOCKERS视图查找有问题的互斥锁状况；

2. 通过DBA_HIST_MUTEX_SLEEP_HISTORY视图找出当前互斥锁休眠的记录；

3. 分析每一条休眠记录：元信息（SLEEP_TYPE、MUTEX_ID、SLEEP_TIME），会话信息（SESSION_ID、SERIAL#）；

4. 查找执行SQL和对应活动会话：通过V$ACTIVE_SESSION_HISTORY视图查找相关会话，然后通过V$SQL查找对应的SQL；

5. 根据上面步骤可以识别互斥锁休眠原因，以及对应SQL语句，从而得到优化SQL的思路；

DBA_HIST_MUTEX_SLEEP displays mutex sleep summary historical statistics information.

Column	Datatype	NULL	Description
`SNAP_ID`	`NUMBER`	`NOT NULL`	Unique snapshot ID
`DBID`	`NUMBER`	`NOT NULL`	Database identifier for the snapshot
`INSTANCE_NUMBER`	`NUMBER`	`NOT NULL`	Instance number for the snapshot
`MUTEX_TYPE`	`VARCHAR2(32)`	`NOT NULL`	Mutex type
`LOCATION`	`VARCHAR2(40)`	`NOT NULL`	The code location where the waiter slept for the mutex
`SLEEPS`	`NUMBER`		Number of sleeps for this `MUTEX_TYPE` and `LOCATION`
`WAIT_TIME`	`NUMBER`		Wait time in microseconds
`CON_DBID`	`NUMBER`		The database ID of the PDB for the sampled session
`CON_ID`	`NUMBER`		The ID of the container that `CON_DBID` identifies. 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

分享到：