Solution : https://service.sap.com/sap/support/notes/964344 (SAP Service marketplace login required)
Summary :
Mutexes in Oracle, primarily utilized for library cache access from Oracle 10g onwards, combine latches and pins functions, offering synchronization (exclusive and shared modes) and pinning capabilities to optimize performance by keeping objects in memory. They are superior to older block mechanisms due to lower memory requirements, faster access speeds, and improved contention management, making them more efficient for handling 'hot objects'. The Oracle system uses specific wait events to manage mutex behaviors, which include 'cursor mutex X/S' and 'cursor pin S wait on X', among others, to handle mutex requests and pinning operations. These events help in avoiding session blockages and ensure smoother database operations. Views such as V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY provide insights into mutex waits, which can be analyzed to enhance system performance. Oracle provides parameters (_KKS_USE_MUTEX_PIN and _MUTEX_WAIT_SCHEME) to control mutex usage, ensuring flexibility and optimized management of resources.
Key words :
lower memory requirements faster access higher granularity, terms frequently asked questions solution 1, hp-ux pa-risc, creating cursor-specific statistics, checking cursor-specific statistics, wait event generally corresponds, exclusive library cache lock, shared library cache lock, wait event rarely occurs, wait event parameter p2
Related Notes :
| 1588876 | Oracle Enhancement to add different "Mutex" wait schemes |
| 971261 | Oracle 9.2 / 10.2: Hanging queries with STAR_TRANSFORMATION |
| 912389 | Cursor parameters on Oracle |
| 767414 | FAQ: Oracle latches |
| 712624 | High CPU consumption by Oracle |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |