oracle+资源忙,Oracle 等待事件之三：常见的常规等待事件

诊断第一步先要概率系统事件次数排名

select event，count(*)  from

V$ACTIVE_SESSION_HISTORY group by event  having

count(*)   >

10000  order by count(*) ;

EVENT

----------------------------------------

SQL*Net break/reset to client​

.​

..​

....​

......

Direct path

read

During Direct Path operations the data is asynchronously

read from the database files. At some stage the session needs to

make sure that all outstanding asynchronous I/O have been completed

to disk. This can also happen if during a direct read no more slots

are available to store outstanding load requests (a load request

could consist of multiple I/Os).

在直接路径操作期间，数据是异步读取数据库文件。在某些阶段，会话需要确保所有未完成的异步IO已完成。这也可以发生在一个直接的读没有更多的插槽来处理突出的负载请求(负载请求可能包括多个IO)。

Wait Time: 10 seconds. The session will be posted by the

completing asynchronous I/O. It will never wait the entire 10

seconds. The session waits in a tight loop until all outstanding

I/Os have completed.

等待时间：10秒。该会话收到post的消息在完成异步IO。它将永远不会等待整个10秒。会话在一个轮训中等待，直到所有大型IO处理操作已经完成。​

​情景解释：这个等待事件多发生在会话将数据块直接读取到PGA当中而不是SGA中的情况，这些被读取的数据通常是会话私有的数据，所以不需要放到SGA作为共享数据。这些数据通常是来自与临时段上的数据，比如一个会话中SQL的排序数据，并行执行过程中间产生的数据，以及Hash

Join，merge

join产生的排序数据，因为这些数据只对当前的会话的SQL操作有意义，如果大量会话产生这种等待事件，要小心侦查，可能会导致IO带宽大量占用，导致整体IO响应下降。

latch free

The process waits for a latch that is currently busy (held by

another process).

该进程等待一个锁存器，目前正忙着(由另一个进程持有)。

Wait Time: The wait time increases exponentially and does not

include spinning on the latch (active waiting). The maximum wait

time also depends on the number of latches that the process is

holding. There is an incremental wait of up to 2 seconds.

等待时间：等待时间呈指数增加，不包括旋转的锁存(主动等待)。的最大等待时间也取决于被进程持有的锁存器的数目。有一个增量等待长达2秒。

Parameter Description

address   The

address of the latch for which the process is waiting

number

The latch number that indexes in the V$LATCHNAME

view.To find more information on the latch, use the following SQL

statement:

select *

from v$latchname

where latch# = number;

tries

A count of the number of times the process tried

to get the latch (slow with spinning) and the process has to

sleep

​

buffer latch

The session waits on the buffer hash chain latch. Primarily used

in the dump routines.

会话在缓冲哈希链锁存器上等待。主要用于转储例程。

Wait Time: 1 second

Parameter Description

latch addrThe virtual address in the SGA where this

latch is located. Use the following statement to find the name of

this latch:

select *

from v$latch a, v$latchname b

where addr = latch addr

and a.latch# = b.latch#;

chain#The

index into array of buffer hash chains. When the chain is

0xfffffff, the foreground waits on the LRU latch.

​

cursor: mutex S

A session waits on this event when it is requesting a mutex in

shared mode, when another session is currently holding a this mutex

in exclusive mode on the same cursor object.

一个会话等待此事件发生在share模式请求一个互斥锁，当另一个会话正在以exclusive模式持有相同cursor上互斥锁。

cursor: mutex X

The session requests the mutex for a cursor object in exclusive

mode, and it must wait because the resource is busy. The mutex is

busy because either the mutex is being held in exclusive mode by

another session or the mutex is being held shared by one or more

sessions. The existing mutex holder(s) must release the mutex

before the mutex can be granted exclusively.

会话请求x独占模式下对游标对象的互斥锁，它等待，因为资源忙。互斥锁忙：要么是在另几个会话x互斥或s共享方式持有这个锁。在持有者释放之前，会话必须一直等待。​

一个例子：​

top 5 events​

Library cache

mutex

17

63570s     3739540ms

94.1%

​

Statistic Name Time (s) % of DB Time

failed parse elapsed time 162,259.78 240.17

parse time elapsed 63,119.97 93.43​

从这里就可以看出，你有解析失败导致大量的等待,如语法错误

当你去试图执行一个错误的SQL的时候，也会发生解析，这时候也需要获得library cache

latch，但是因为错误所以解析没有完成，shared

pool中不会出现这个SQL对应的cursor，当你再次执行这个错误的SQL时，还会发生硬解析，又要尝试获得library cache

mutex

所以你会看到非常高的Library cache mutex X​

1.

select * from DBA_HIST_ACTIVE_SESS_HISTORY where event='library

cache: mutex X';

-取出P1

select * from V$MUTEX_SLEEP_HISTORY a where

A.MUTEX_IDENTIFIER=:P1;​

2.跟踪failed parse的SQL有那些

$ oerr ora 10035

10035, 00000, "Write parse failures to alert log file"

所以应该在system级别上设置10035 evnet level 1

ALTER SYSTEM SET EVENTS '10035 trace name context forever, level

1';

然后查看alert.log

找到解析失败的SQL，定位问题

cursor: pin S

A session waits on this event when it wants to update a shared

mutex pin and another session is currently in the process of

updating a shared mutex pin for the same cursor object.

This wait event should rarely be seen because a

shared mutex pin update is very fast.

一个会话等待这个事件的时候就想更新共享互斥引脚和另一个会话正在进行相同的光标对象更新共享互斥销过程。这个等待事件应该很少会看到因为共享互斥销更新很快。

Wait Time: Microseconds

cursor: pin X

A session waits on this event when it is requesting an exclusive

mutex pin for a cursor object and it must wait because the resource

is busy. The mutex pin for a cursor object can be busy either

because a session is already holding it exclusive, or there are one

or more sessions which are holding shared mutex pin(s). The

exclusive waiter must wait until all holders of the pin for that

cursor object have released it, before it can be granted.

一个会话等待此事件因为请求一个x模式的mutex

pin，因为资源忙。cursor的mutex被其他会话以s或者x的模式持有(mutex pin

)。请求等待着必须等待所有的持有者释放cursor的metux 倍释放，才能取得授权。

Wait Time: Microseconds

cursor: pin S wait on X

A session waits for this event when it is requesting a shared

mutex pin and another session is holding an exclusive mutex pin on

the same cursor object.

一个会话等待此事件，因它请求一个s模式获得一个mutex，另一个会话持有同一个cusor的x模式的mutex

。必须等待持有者释放。

Wait Time: Microseconds

Parameter Description

P1     Hash value of

cursor

P2     Mutex value (top 2

bytes contains SID holding mutex in exclusive mode, and bottom two

bytes usually hold the value 0)

P3     Mutex where (an

internal code locator) OR'd with Mutex Sleeps

library cache lock

This event controls the concurrency between clients of the

library cache. It acquires a lock on the object handle so that

either:

One client can prevent other clients from accessing the same

object

The client can maintain a dependency for a long time (for

example, no other client can change the object)

This lock is also obtained to locate an object in the library

cache.

此事件控制库缓存的客户端之间的并发性。它在对象句柄上获得一个锁，以便：

一个客户端可以阻止其他客户端访问同一个对象

客户端可以维护该库缓存很长一段时间(如，没有其他客户可以改变对象)

该锁也获得了在库缓存中查找对象的位置。

Wait Time: 3 seconds (1 second for PMON)

library cache pin

This event manages library cache concurrency. Pinning an object

causes the heaps to be loaded into memory. If a client wants to

modify or examine the object, the client must acquire a pin after

the lock.

此事件管理库缓存并发。钉住一个对象，使堆装入内存中。如果一个客户要修改或检查对象，这个客户必须在获得pin后锁住这个对象。

Wait Time: 3 seconds (1 second for PMON)

Parameter

Description

handle address Address of the object being loaded

pin address

Address of the load lock being

used. This is not the same thing as a latch or an enqueue, it is

basically a State Object.

mode

Indicates which data pieces of

the object that needs to be loaded

row cache lock

The session is trying to get a data dictionary lock.

会话尝试获得一个数据字典的锁。

Wait Time: Wait up to 60 seconds.

Parameter Description

cache id

The CACHE# column value in the

V$ROWCACHE view

mode

See "mode"

request

The pipe timer set by the

user