Library cachelatches gone in Oracle 11g

转载

mb611a2e88042f6 2021-08-16 22:54:30

文章标签 sql oracle sed hadoop Oracle 文章分类 Oracle 数据库

Library cachelatches gone in Oracle 11g

by TANEL PODER posted on AUGUST 3,2008

In Oracle 11g even more library cache operations have been changed to useKGX mutexes instead of latches.

In Oracle 10.2.0.2+ the library cachepin latch usage wasreplaced with mutexes whenever _kks_use_mutex_pinwas true, alsofew other things like V$SQLSTATS arrays and parent cursor examination wereprotected by mutexes. However the traversing of library cache hash chains (theright child cursor lookup using kksfbc()) was still protected by library cachelatches which could become a problem with frequent soft parsing combined withtoo little cursor cache and long library cache hash chains (remember, thelibrary cache latches were always taken exclusively even for plain hash chainscanning).

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 forexample.

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 on11.1.0.6 and the boldlines 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 11gas the mutexes introduced in 10.2 have proven to work fine (and they havemanaged to implement mutexes for protecting (almost) the full set of librarycache 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). Theparameter is still there though and out of interest I checked what happens if Iset it to false and bounced the instance.

As, expected, I started getting error messages like following right afterstartup:

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 maywork ok in one version (and platform) but could cause serious trouble anywhereelse.

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) ofthem 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 asthe latter was empty on 11g… it may be that the mutex get operations have beentuned further to not maintain the counters just to save even few more CPUcycles every get)

So, starting from 11g, each library cache bucket is protected by aseparate mutex (yes all 131072 of them!).

Previously we had all those buckets were hashed to and protected by max 67library cache latches which inevitably were prone to unnecessary falsecontention in case of many cursors being executed concurrently. Now this issueshould be resolved once and for all. Of course the library cache mutexes stilldon’t solve all problems in the world (especially the ones related to excessivehard parsing!), for example there’s still a chance of hash collision of twoentirely different cursors. Also, if there are many child cursors under aparent and the application cursor management is poor (e.g. cursors are closedafter every execution and no session cursor caching is done) then you couldstill have contention on the mutex due continuous library cache hash chaintraversing.

NB! If you want to move to the "New World" - and benefit fromthe awesomeness of Hadoop, without having to re-engineer your existingapplications - check out Gluent, my new startup that will make history!;-)