Infrastructure at your Service

Yann Neuhaus

How to handle mutex issues after migrating to Oracle 11.1

You have recently migrated to Oracle Database 11g Release 1 (11.1) and have had some issues with so called “mutexes”? Don’t worry, these nasty sounding mutexes (not mutants :roll: ) can be handled quite easily as I will show in my post. But first of all, what are mutexes?

Mutexes: access control for SGA memory structures

Mutexes are used to serialize access within SGA memory structures. Mutexes (from mutual exclusion) have been introduced in 10.2. The goal was to replace the latches with smaller and more “efficiently locking” structures.

Unfortenately, I have observed several issues concerning mutexes during the last 11g migrations.

First of all, there is the bug 6918493 concerning 11.1.0.6, which is a “back-port” of the bug 2752985 (available in 10.1). As soon as Dead Connection Detection (DCD) is activated at Oracle*Net level (sqlnet.expire_time>0), the database could be confronted with Oracle shadow processes hung in an OS level mutex self deadlock with a stack including “nstimexp -> … -> localtime_r -> …”.

There are two things you can do to solve this problem:

  • Disable sqlnet.expire_time (set it to 0)
  • Install patch 6918493 (for 11.1)

Apart from this issue observed with 11g R1, there may also be a problem with the parallel workers while running Data Pump (impdp PARALLEL option): As soon as several Data Pump workers are started, some deadlocks and Mutex lock issues can occur.

The solution to this issue:

  • Do not use the Data Pump parallel open (no parallel workers)

A database writer trace file can be generated with the following content while using the parallel option of Data Pump:

tail /u00/app/oracle/diag/rdbms/dbp3_site1/DBP3/trace/DBP3_dw0_3445.trc
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x7bbf7ffc8, type: 74, owner: 0x7861c1370, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x7632341e1, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8348, pg=0
LibraryObjectLock:  Address=bbf7ffc8 Handle=9fc48aa0 RequestMode=X CanBeBrokenCount=39 Incarnation=55 ExecutionCount=0
User=61739f90 Session=716f6f78 ReferenceCount=0 Flags=[0000] SavepointNum=e3fa5
 LibraryHandle:  Address=9fc48aa0 Hash=58c3d302 LockMode=S PinMode=S LoadLockMode=0 Status=INVL
 ObjectName:  Name=USER.APPUTIL
 FullHashValue=879c0cb22d3a105fdd0a74a533c3d302 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) Identifier=61846 OwnerIdn=41
 Statistics:  InvalidationCount=15 ExecutionCount=0 LoadCount=51 ActiveLocks=1 TotalLockCount=54 TotalPinCount=58
 Counters:  BrokenCount=39 RevocablePointer=55 KeepDependency=0 KeepHandle=0 BucketInUse=58293 HandleInUse=58293
 Concurrency:  DependencyMutex=9fc48b50(0, 135, 0, 0) Mutex=9fc48bc8(1347, 8542715, 272487, 6)

 

Good look with your Oracle 11.1 (hopefully 11.2 :-) ) migrations!

Best regards
Yann

 

Leave a Reply

Yann Neuhaus
Yann Neuhaus

Chairman of the Board, Chief Sales Officer (CSO), Region Manager