In Oracle 11g even more library cache operations have been changed to use KGX mutexes instead of latches.
In Oracle 10.2.0.2+ the library cache pin latch usage was replaced with mutexes whenever _kks_use_mutex_pin was true, also few other things like V$SQLSTATS arrays and parent cursor examination were protected by mutexes. However the traversing of library cache hash chains (the right child cursor lookup using kksfbc()) was still protected by library cache latches which could become a problem with frequent soft parsing combined with too little cursor cache and long library cache hash chains (remember, the library cache latches were always taken exclusively even for plain hash chain scanning).
In 11g all library cache related latches except “library cache load lock” are gone and corresponding operations are protected by mutexes instead. The “library cache” latches have been replaced by “Library Cache” mutexes for example.
Here are couple queries which illustrate the change.
**Executed on 10.2.0.3:
**
SQL> select name from v$latch where lower(name) like '%library%'; NAME -------------------------------------------------- library cache pin allocation library cache lock allocation library cache hash chains library cache lock library cache library cache pin library cache load lock 7 rows selected. SQL> select name from v$event_name where name like '%library%'; NAME ---------------------------------------------------------------- latch: library cache latch: library cache lock latch: library cache pin library cache pin library cache lock library cache load lock library cache revalidation library cache shutdown 8 rows selected.
Same queries executed on 11.1.0.6 and the bold lines above are gone:
SQL> select name from v$latch where lower(name) like '%library%'; NAME ---------------------------------------------------------------- library cache load lock SQL> select name from v$event_name where name like '%library%'; NAME ---------------------------------------------------------------- library cache pin library cache lock library cache load lock library cache: mutex X library cache: mutex S OSD IPC library library cache revalidation library cache shutdown 8 rows selected.
Looks like the developers have thrown out library cache latching mechanism in 11g as the mutexes introduced in 10.2 have proven to work fine (and they have managed to implement mutexes for protecting (almost) the full set of library cache operations).
So there is no way to revert back to old behaviour using _kks_use_mutex_pin=false (and you wouldn’t need to do this anyway, btw). The parameter is still there though and out of interest I checked what happens if I set it to false and bounced the instance.
As, expected, I started getting error messages like following right after startup:
ORA-03113: end-of-file on communication channel ORA-00600: internal error code, arguments: [kglGetSessionUOL], [7], [], [], [], [], [], []
This is a good example of dangers with undocumented parameters – they may work ok in one version (and platform) but could cause serious trouble anywhere else.
Anyway, back to mutexes. In 10g you see there are 3 types of mutexes used:
SQL> select * from v$mutex_sleep; MUTEX_TYPE LOCATION SLEEPS WAIT_TIME -------------------------------- ---------------------------------------- ---------- ---------- Cursor Stat kksFindCursorStat [KKSSTALOC3] 339 775 Cursor Parent kkspsc0 [KKSPRTLOC26] 1507 123969 Cursor Parent kksLoadChild [KKSPRTLOC5] 170 372 Cursor Parent kksLoadChild [KKSPRTLOC4] 385 3799 Cursor Parent kksfbc [KKSPRTLOC2] 1649 22484 Cursor Parent kksfbc [KKSPRTLOC1] 128 1599 Cursor Pin kksLockDelete [KKSCHLPIN6] 3505 928387 Cursor Pin kkslce [KKSCHLPIN2] 15343 160394917 Cursor Pin kksfbc [KKSCHLFSP2] 3219 9065433 9 rows selected.
In 11g there are couple additional mutexes, one (and most important) of them is Library Cache mutex:
SQL> select distinct mutex_type from v$mutex_sleep_history; MUTEX_TYPE -------------------------------- Library Cache Cursor Pin Cursor Parent Cursor Stat
(I had to sample v$mutex_sleep_history instead of v$mutex_sleep on 11g as the latter was empty on 11g… it may be that the mutex get operations have been tuned further to not maintain the counters just to save even few more CPU cycles every get)
So, starting from 11g, each library cache bucket is protected by a separate mutex (yes all 131072 of them!).
Previously we had all those buckets were hashed to and protected by max 67 library cache latches which inevitably were prone to unnecessary false contention in case of many cursors being executed concurrently. Now this issue should be resolved once and for all. Of course the library cache mutexes still don’t solve all problems in the world (especially the ones related to excessive hard parsing!), for example there’s still a chance of hash collision of two entirely different cursors. Also, if there are many child cursors under a parent and the application cursor management is poor (e.g. cursors are closed after every execution and no session cursor caching is done) then you could still have contention on the mutex due continuous library cache hash chain traversing.
