FAQ Oracle Mutexes
What do you mean by the term “Mutexes”?
By Mutexes we mean the operating system mechanisms which are a combination of pins and latches.
X (Exclusive): A user can request for a mutex through a session in the X mode ad because of this, no other session will be able to access the mutex in S or X mode.
S (Shared): In S mode, a mutex can be requested by one or more sessions. In case the mutex has already not been blocked in X mode by another session, then only will be possible. Until all the S blocks have been released, a X requests must wait.
Mutexes can be utilized to "pin" the protected objects in memory, to avoiding getting displaced. By incrementing a reference counter in the mutex, the S pin is implemented. A pin release works in parallels to a reduction of the reference counter. The protected object can only replaced if the reference counter reaches zero.
Why does Oracle utilize mutexes?
As of Oracle 10g, When Oracle accesses the library cache, that when it utilizes mutexes. Mutexes are also utilized in other areas, for instance, for accessing SecureFiles, in later Oracle releases.
What are the advantages of mutex?
While comparing Mutex to other block mechanisms such as latches, Mutex has the following advantages:
- Lower memory requirements
- Faster access
- Higher granularity, which guarantees less "false contention" (as occurs when a latch secures multiple "hot objects", for example).
- A combination of latch and pin functions
Which wait events can be related to mutexes?
The following wait events can be related to mutexes, as of Oracle 10g.
cursor mutex X
Meaning: This wait event takes place when a session has requested a mutex in X mode (for instance creating cursor-specific statistics, statistics, restructuring a child cursor, bind capturing), however another session has blocked the mutex in S or X mode at that moment.
cursor mutex S
Meaning: This wait event takes place when a session has requested a mutex in S mode (for instance, during, checking cursor-specific statistics and checking a parent cursor), however another session has already blocked the mutex in the X mode, or the mutexes reference counter has already been updated by another session.
cursor pin S wait on X
Meaning: for successfully executing a SQL statement, the mutexes which belongs to the cursor should be pinned. This entails an S block. Incase another transaction has already set a X block at the same time (for instance, as it is parsing the cursor), then the "cursor: pin S wait on X" and the wait event occurs. On an average, these waits typically lasts for a few milliseconds, however they should not occur quite often.
This wait event typically is in parallels to the "library cache pin" event in Oracle <=9i.
cursor pin X
Meaning: This relates to the fact that this wait event can take place when a cursor is in use which must be restructured (that is, an S or X block exists). Generally, this rarely takes place, as in such cases, a new cursor is in place of altering the already existing cursor.
cursor pin S
Meaning: A session is required modify a mutex pin's reference counter. Yet, at the same time another session impacts and makes a change to the reference counter. This special action has to be serialized, even though it only relates to S blocks.
This particular wait event occurs very frequently, incase many sessions want to adjust the mutex's reference counter around the same time. However as update to the counter can be carried out very quickly, due to which the wait times are low.
library cache: mutex X (Oracle 11g or higher)
Meaning: A session waits for an exclusive library cache lock.
library cache: mutex S (Oracle 11g or higher)
Meaning: A session waits for an shared library cache lock.
SecureFile mutex (Oracle 11g and higher)
Meaning: A write transaction is waiting for a SecureFile lock.
Which mechanism requests mutexes?
In case a mutex request is unsuccessful, then the session will try requesting the mutex for a total of 255 times. In case still it is unsuccessful, the session will then wait for a specific period of time. The session can continue using CPU resources or it can temporarily "sleep", All depending on the request. Post which the cycle of 255 requests along with a waiting period is repeated as required until a request finally is successful.
Can I deactivate the use of mutexes?
In Oracle 10g, the parameter _KKS_USE_MUTEX_PIN controls the use of mutexes. By default, the parameter is set to TRUE. If the user sets it to FALSE, the system utilizes the older "library cache pin" mechanism. This parameter does not exists, with Oracle 11g.
Where can I find details about any mutex waits which occur?
Oracle allows the user to view V$MUTEX_SLEEP along with the wait events described above.
This contains the following information:
- MUTEX_TYPE: The mutex type
- LOCATION: The call location
- SLEEPS: The number of sleeps.
- WAIT_TIME: Wait time in microseconds
The user can also view the V$MUTEX_SLEEP_HISTORY, which allows the user to see how frequently some sessions have to wait for each other.
The important columns are listed below:
- MUTEX_IDENTIFIER: Unique identifier of a mutex
- SLEEP_TIMESTAMP: The last mutex wait with the same requesting and stopping session
- MUTEX_TYPE: The mutex type
- GETS: The number of mutex requests
- SLEEPS: Number of sleeps
- REQUESTING_SESSION: The requesting session's SID
- BLOCKING_SESSION: The blocking session's SID
- LOCATION: The call location
In Oracle 10.2 and in the previous version, earlier, the blocking mutex holders are not listed in the BLOCKING_SESSION column of V$SESSION. These can be derived from the wait event parameter P2. The user can use the following command for determining the blocked and blocking Oracle sessions currently related to the "cursor: pin S wait on X":
DECODE(SIGN(P2 - 4294967295), 1, P2 / 4294967296, P2 / 65536)
STATE = 'WAITING' AND
EVENT = 'cursor: pin S wait on X';
The user can also use a similar method for executing queries on V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY.
In regards to mutexes, which all problems can occur?
The Typical program errors are listed as follows:
Increased CPU consumption
In case the problem takes place on HP-UX PA-RISC and Oracle 10.2.0.2, then it caused due to the bug 5399325. Here we suggest you use _KKS_USE_MUTEX_PIN = FALSE for deactivating the mutex mechanism.
For HP-UX, setting HPUX_SCHED_NOAGE in accordance with SAP doc 767414 may solve the problem.
With Oracle 10g (10.2.0.4 and higher) and fix 6904068, all depending on the operating system and the patch the user can set the parameter _FIRST_SPARE_PARAMETER or _SECOND_SPARE_PARAMETER to 1 (see doc 830576) for ensuring that even after an unsuccessful request.the process sleeps for one hundredth of a second. When there is a high load on the system, this majorly reduces CPU bottlenecks.
As of Oracle 184.108.40.206, the user can control the mutex behavior by utilizing the parameters _MUTEX_WAIT_SCHEME, MUTEX_WAIT_TIME, and _MUTEX_SPIN_COUNT.
PS-Enqueues and "cursor: pin X"-Waits
In Oracle 10.2.0.2, because of the bug 5908030 by using statements such as INSERT... SELECT or MERGE and by using parallel execution can cause a "cursor: pin X" and "enq: PS - contention" deadlock. In such a scenario, the user must use _KKS_USE_MUTEX_PIN = FALSE for deactivating the mutexes.
Long "cursor: pin S wait on X" wait processes
In case the 'cursor: pin S wait on X" waits takes place, 10.2. 0.2 in connection with STAR transformations; refer to SAP doc 971261.
In case If "cursor: pin S wait on X" wait events occur on HP-UX 11.23 or 11.31 without the lock holder being recognized, then the problem may be because of a HP-UX bug (see metalink document 580273.1). Please make sure that you have imported at least "Scheduler Cumulative Patch" PHKL_37809 (11.23) or PHKL_38397 (11.31). If the problem has already taken place and a process is hanging, the user can remove the block by using a "oradebugunlimit", as listed in doc 613872.
"cursor: pin S" waits
Waits on "cursor: pin S" can be triggered by a parallel execution of a SQL statement. FOR ALL ENTRIES statements, in addition to a reduction of the number of parallel executions in the application, the user can increase the blocking factors (doc 881083) for solving the problem, as this also significantly reduces the number of executed statements.
Refer to the information above about the parameter _FIRST_SPARE_PARAMETER.
"SecureFile mutex" waits
The user needs to check whether he can reduce the parallel processing of the DML operations on the application side or whether an Oracle bug is present.
Oracle Mutexes FAQ Unamaanisha nini kwa neno <unk>Mutexes<unk>? Muteks sisi maana utaratibu wa mfumo wa uendeshaji ambayo ni mchanganyiko wa pini na latches. X (Exclusive): Mtumiaji anaweza kuomba mutex kupitia kikao katika tangazo la hali ya X, kwa sababu hii, hakuna kikao kingine kitakachoweza kupata mutex katika hali ya S au X. S (Shared): Katika hali ya S, mutex inaweza kuombwa na kikao kimoja au zaidi. Kama mutex tayari haijafungwa katika hali ya X na kikao kingine, basi tu itakuwa inawezekana. Mpaka vitalu vyote vya S vimeachiliwa, maombi ya X lazima yasubiri. Mutexes inaweza kutumika kwa "pin" vitu kulindwa katika kumbukumbu, ili kuepuka kupata kuhamishwa. Kwa kuongeza counter rejea katika mutex, S pin ni kutekelezwa. A pin kutolewa kazi katika sambamba na kupunguza ya kuhesabu rejea. Kitu kulindwa inaweza tu kubadilishwa kama kuhesabu rejea inafikia sifuri. Kwa nini Oracle hutumia mutexes? Oracle 10g ni moja ya programu bora zaidi kwa ajili ya Oracle, ambayo inatoa huduma ya kuhifadhi kumbukumbu ya maktaba, ambayo inatoa huduma ya kuhifadhi kumbukumbu ya kumbukumbu ya kumbukumbu ya kumbukumbu. Mutexes pia hutumiwa katika maeneo mengine, kwa mfano, kwa ajili ya kupata SecureFiles, katika baadaye Oracle releases. Ni faida gani za mutex? Wakati kulinganisha Mutex na utaratibu mwingine block kama vile latches, Mutex ina faida zifuatazo: - chini ya kumbukumbu mahitaji - upatikanaji wa haraka - juu granularity, ambayo inahakikisha chini ya "mjadala wa uongo" (kama hutokea wakati latch ana uhakika "vitu moto" nyingi, kwa mfano). - Mchanganyiko wa latch na pin kazi ambayo kusubiri matukio inaweza kuhusiana na mutexes? Matukio yafuatayo ya kusubiri yanaweza kuhusiana na mutexes, kama ya Oracle 10g. "Mtu anajibu kwa ""mutex"" katika hali ya X, kwa mfano, kuunda takwimu maalum za cursor, kurudisha cursor ya mtoto, na kuunganisha, lakini kikao kingine kimezuia mutex katika hali ya S au X wakati huo." "Mtu ambaye ameweka mutex katika hali ya ""S"" (kwa mfano, wakati wa kuangalia takwimu za cursor-specify na kuangalia cursor ya mzazi) tayari ameziba mutex katika hali ya ""X"" au kukadiria kumbukumbu ya mutex tayari imerekebishwa na kikao kingine." cursor pin S kusubiri juu ya X Maana: kwa mafanikio ya kutekeleza SQL taarifa, mutexes ambayo ni mali ya cursor lazima pinned. Hii inahusisha S block. "Ikiwa shughuli nyingine tayari imeweka block ya X wakati huo huo (kwa mfano, kama ni parsing cursor), basi ""cursor: pin S kusubiri juu ya X"" na kusubiri tukio hutokea." Kwa wastani, kusubiri hizi kwa kawaida huchukua kwa milisekunde chache, hata hivyo hazipaswi kutokea mara nyingi. Hii kusubiri tukio kawaida ni katika sambamba na "maktaba cache pin" tukio katika Oracle <= 9i. Cursor pin X maana: Hii inahusiana na ukweli kwamba hii kusubiri tukio inaweza kutokea wakati cursor ni katika matumizi ambayo lazima kuwa restructured (yaani, S au X block ipo). Kwa ujumla, hii mara chache hufanyika, kama katika kesi kama hizo, cursor mpya ni katika nafasi ya kubadilisha cursor tayari zilizopo. cursor pin S Maana: kikao inahitajika kurekebisha mutex pin ya kumbukumbu counter. Hata hivyo, wakati huo huo kikao kingine athari na hufanya mabadiliko ya kukadiria rejea. Hatua hii maalum ina kuwa serialized, hata kama ni tu inahusiana na S vitalu. Hii maalum kusubiri tukio hutokea mara nyingi sana, incase vikao vingi wanataka kurekebisha mutex ya kumbukumbu counter karibu wakati huo huo. Hata hivyo kama update kwa counter inaweza kufanyika haraka sana, kutokana na ambayo muda wa kusubiri ni chini. "Kifungu cha ""Kifungu cha"" cha Oracle 11g au zaidi: Session inangoja kufuli la cache ya maktaba ya kipekee." "Kifungu cha ""Kifungu cha"" cha Oracle 11g au zaidi: Session inangoja kufuli kwa cache ya maktaba iliyoshirikiwa." SecureFile mutex (Oracle 11g na juu) Maana: kuandika shughuli ni kusubiri kwa SecureFile lock. Ni utaratibu gani unaomba mutexes? Ikiwa ombi la mutex halikufanikiwa, kikao kitajaribu kuomba mutex kwa jumla ya mara 255. Katika kesi bado ni kushindwa, kikao kisha kusubiri kwa kipindi maalum cha muda. Kipindi inaweza kuendelea kutumia rasilimali CPU au inaweza kwa muda "kulala", Wote kulingana na ombi. Baada ya hapo, mzunguko wa maombi 255 pamoja na kipindi cha kusubiri hurudiwa kama inahitajika hadi ombi hatimaye linafanikiwa. Je, ninaweza kuzima matumizi ya mutexes? Katika Oracle 10g, parameter _KKS_USE_MUTEX_PIN udhibiti matumizi ya mutexes. Kwa default, parameter ni kuweka kwa kweli. Kama mtumiaji kuweka ni kwa FALSE, mfumo hutumia zamani "maktaba cache pin" utaratibu. Hii ni moja ya vipimo vya Oracle 11G. Ambapo naweza kupata maelezo kuhusu matarajio yoyote mutex ambayo kutokea? Oracle inaruhusu mtumiaji kuona V$MUTEX_SLEEP pamoja na matukio ya kusubiri ilivyoelezwa hapo juu. Hii ina taarifa zifuatazo: - MUTEX_TYPE: Aina mutex - LOCATION: Wito eneo - SLEEPS: Idadi ya kulala. Wakati wa kusubiri katika microseconds: VMS inaweza pia kuona VMSSLEEPHISTORY, ambayo inaruhusu mtumiaji kuona jinsi mara kwa mara baadhi ya vikao lazima kusubiri kwa kila mmoja. Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Muteks: Hii inaweza kupatikana kutoka parameter ya tukio la kusubiri P2. "Kama ilivyoelezwa hapo juu, ""Msimu wa Oracle"" ni ""Msimu wa Oracle"" na ""Msimu wa Oracle"" ni ""Msimu wa Oracle"" na ""Msimu wa Oracle"" ni ""Msimu wa Oracle"" na ""Msimu wa Oracle"" ni ""Msimu wa Oracle"" na ""Msimu wa Oracle"" na ""Msimu wa Oracle"" ni ""Msimu ya Oracle"" na ""Msimu ya Oracle"" na ""Msimu ya Oracle"" na ""Msimu ya Oracle"" na ""Msimu ya Oracle"" na ""Msimu ya Oracle"" na ""Msimu ya Oracle."""" Kwa upande wa mutex, ambayo matatizo yote yanaweza kutokea? "Kama tatizo la ""UX"" ni pamoja na ""UX"" na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2 na ""UX"" katika mfumo wa Oracle 10.2.0.2." Hapa tunapendekeza kutumia _KKS_USE_MUTEX_PIN = FALSE kwa deactivating mutex utaratibu. "Hii ni kwa sababu ya ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year"" na ""Happy New Year""" Oracle 10g (mwisho wa 10.2.0.4 na zaidi) na Fix 6904068, wote kulingana na mfumo wa uendeshaji na patch, mtumiaji anaweza kuweka parameter ya 1 (tazama Doc 83576) ili kuhakikisha kwamba hata baada ya ombi lisilofanikiwa, mchakato unalala kwa sehemu ya mia ya pili. Wakati kuna mzigo wa juu juu ya mfumo, hii majorly hupunguza CPU bottlenecks. Kwa mujibu wa Oracle 184.108.40.206, mtumiaji anaweza kudhibiti tabia ya mutex kwa kutumia vigezo _MUTEX_WAIT_SCHEME, MUTEX_WAIT_TIME, na _MUTEX_SPIN_COUNT. "Kama ilivyoelezwa hapo juu, ""Pin X"" ni moja ya vipengele vya Oracle 10.2.0.2 ambayo inatoa ufikiaji wa kiwango cha juu cha upatikanaji wa data kwa kutumia taarifa kama vile ""INSERT.""" SELECT au MERGE na kwa kutumia utekelezaji sambamba inaweza kusababisha "cursor: pin X" na "enq: PS - utata" impasse. Katika hali kama hiyo, mtumiaji lazima kutumia _KKS_USE_MUTEX_PIN = FALSE kwa deactivating mutexes. "Mfano: ""Cursor: Pin S"" inahitajika kwa ajili ya mchakato wa kusubiri, na ""Cursor: Pin S"" inahitajika kwa ajili ya mchakato wa kusubiri." "Kama ilivyoelezwa katika ""STALK"" (STALK) na ""STALK"" (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STALK) (STA "Kama ""Cursor: pin S kusubiri juu ya X"" kusubiri matukio kutokea juu ya HP-UX 11.23 au 11.31 bila lock mmiliki ni kutambuliwa, basi tatizo inaweza kuwa kwa sababu ya HP-UX mdudu (tazama metalink hati 580271)."" "Kama vile, kwa mfano, ""Scheduler Cumulative Patch"" (PKL 378091123), ""PKL 3839711"" au ""PKL 3839711"" inaweza kutumika." "Ikiwa tatizo tayari limetokea na mchakato unasimama, mtumiaji anaweza kuondoa kizuizi kwa kutumia ""oradebuglimit"" kama ilivyoorodheshwa katika hati 613.872." "cursor: pin S" inangoja Inangoja juu ya "cursor: pin S" inaweza kusababisha na utekelezaji sambamba wa taarifa ya SQL. Kwa taarifa zote za ENTRY, pamoja na kupunguza idadi ya utekelezaji sambamba katika programu, mtumiaji anaweza kuongeza sababu za kuzuia (doc 881083) kwa kutatua tatizo, kwani hii pia hupunguza idadi ya taarifa zilizofanywa. "Kama ilivyoelezwa hapo juu, ""FIRST"" inamaanisha ""FIRST"" na ""SPARE"" inamaanisha ""PARAMETER.""" "SecureFile mutex" inangojea Mtumiaji anahitaji kuangalia kama anaweza kupunguza usindikaji sambamba wa shughuli DML juu ya upande wa maombi au kama mdudu Oracle ni sasa.