[20201117]解析cursor pin S等待事件.txt

[20201117]解析cursor pin S等待事件.txt --//链接: https://blog.pythian.com/reducing-contention-on-hot-cursor-objects-cursor-pin-s/ --//感觉解析不错，做一个记录中文翻译使用金山词霸，不会太准，仅仅作为参考。 Oracle states: "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." In other words, two or more sessions are trying to concurrently run the same statement (the same cursor in library cache), which forces them to compete to update a shared mutex pin for the same cursor object. --//Oracle声明："当一个会话想更新共享互斥锁脚时，它等待此事件，而另一个会话目前正在为同一游标对象更新共享互斥锁脚。"换句话说 --//，两个或多个会话试图同时运行相同的语句(库缓存中的相同游标)，这迫使它们竞争为同一游标对象更新共享互斥锁脚。 This wait event provides very useful information to identify why sessions are competing to update a shared mutex pin: --//此等待事件提供了非常有用的信息，以确定为什么会话竞争更新共享互斥引脚： Information identifying why sessions are competing to update a shared mutex pin - cursor: pin S. --//信息识别为什么会话竞争更新共享互斥引脚cursor: pin S. Here's how a mutex works: If a session wants to use a cursor, it must not disappear from the library cache while in use. The session uses a mutex to ensure the cursor cannot be changed or deleted so, to this end, it logs that there is an interested session by incrementing the mutex usage by one. This is called taking a shared lock. --//如果会话想使用游标，则在使用期间不能从库缓存中消失。 会话使用互斥对象来确保光标不能被更改或删除，为此，它通过将互斥对象 --//的使用增加一个来记录有兴趣的会话。 这就是所谓的共享锁。 The process for taking a shared lock:     A session wants to run a cursor and so checks the owning cursor pin mutex to see if there is a session waiting to     change the mutex (e.g. performing a hard-parse). It does this by checking the high-order bits to see if they are     zero or have a session ID. --//会话希望运行游标，因此检查拥有的游标引脚互斥，以查看是否有会话等待更改互斥(例如。 执行艰苦的解析)。 它通过检查高阶位 --//来查看它们是否为零或具有会话ID来实现这一点。     If the high-order bits are zero, then it locks and increments by one (this is an atomic action). Waiting to lock and     increment causes the "cursor: pin S" wait event. This increment is done on the low-order bits of the mutex. --//如果高阶位为零，则锁定并递增一个(这是原子动作)。 等待锁定和增量导致"光标：引脚S"等待事件。 这个增量是在互斥对象的低 --//阶位上完成的。     If the lock and increment fails, then some other session must be updating the mutex, so it's necessary to sleep and     try again, i.e. lock and increment. The "cursor: pin S" wait event will be longer. This can cause extra CPU load on     the server as it spins attempting to update the mutex. --//如果锁和增量失败，那么其他会话必须更新互斥对象，因此有必要休眠并重试，即。 锁定和增量。 "cursor: pin S"等待事件将更 --//长。 这可能导致服务器上额外的CPU负载，因为它旋转试图更新互斥对象。         If the high-order bits are not zero then there is a session waiting to change the mutex. The current interested     session waits on the event "cursor: pin S wait on X." If this is the case then it sleeps and tries again. --//如果高阶位不是零，则有一个会话等待更改互斥对象。 当前感兴趣的会话等待事件"cursor: pin S wait on X."如果是这样的话， --//它就会睡觉并再次尝试。         Once the cursor is closed and finished, the shared lock on the mutex must be released by performing a lock and     decrementing by one. Once again, if there is a failure to lock and decrement the next step is to sleep and try     again. --//一旦光标关闭并完成，互斥对象上的共享锁必须通过执行一个锁并递减一个来释放。 再次，如果没有锁定和减少，下一步是睡觉， --//然后再试一次。     If a session wants to perform a hard parse on a cursor already existing in the library cache it must acquire the mutex in exclusive mode. --//如果会话想对库缓存中已经存在的游标执行硬解析，则必须以独占模式获取互斥对象。 The process for taking an exclusive lock:     A session wants to perform a hard parse on a statement so it checks the cursor pin mutex to see if it's in use. --//会话希望对语句执行硬解析，因此它检查游标引脚互斥，以查看是否正在使用。     It checks the high-order bits and, if zero, updates the high-order bits to the current session ID (this     compare-and-swap routine is a CPU atomic action). --//它检查高阶位，如果为零，则将高阶位更新为当前会话ID(此比较和交换例程是CPU原子操作)。         If the high-order bits are already set, the process has to wait on the event "cursor: pin X." The session then     sleeps and tries again. --//如果已经设置了高阶位，则进程必须等待事件"cursor: pin X"，然后会话休眠并再次尝试。     Once the high-order bits are set to the current session ID, it checks the low-order bits to see if the cursor is     currently in use. --//一旦将高阶位设置为当前会话ID，它将检查低阶位，以查看光标当前是否正在使用。     If the low-order bits are not zero, it must wait for the counter to decrement to zero (Note: the counter cannot be     incremented once the high-order bits are set to the session ID). --//如果低阶位不是零，则必须等待计数器递减到零(注意：一旦将高阶位设置为会话ID，计数器就不能递增)。         Once the low-order bits are set to zero then the hard parse can proceed.     The session removes the exclusive mutex lock by resetting the high-order bits to zero. --//一旦低阶位设置为零，则硬解析可以继续进行。 --//会话通过将高阶位重置为零来删除独占互斥锁。