[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.

--//一旦低階位設定為零，則硬解析可以繼續進行。
--//會話透過將高階位重置為零來刪除獨佔互斥鎖。