Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

Oracle Mutexes Interview Questions and Answer

|| 0

Oracle Mutexes Interview Questions and Answer
Stechies

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.

Synchronization mechanism

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.

Pinning

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":

SELECT
SID SESSION_ID,
DECODE(SIGN(P2 - 4294967295), 1, P2 / 4294967296, P2 / 65536)
BLOCKING_SESSION
FROM
V$SESSION
WHERE
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 11.2.0.2, 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.


Advertisement
Advertisement
Advertisement