Re: Library cache lock vs. pin (cool stuff

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 20 Jan 2006 19:19:56 -0600
Message-ID: <018b01c61e28$d9decbe0$9fbc21c8@porgand>

Btw, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new thing in 10.2 and they enable shared access to objects in somewhat similar manner than shared latches, that every successful get of particular mutex will increment its value and release will decrement. When the count is zero, no-one has the mutex and it is safe to get it in exclusive mode too. However they are more fine grained than kgl latches and provide better waiting mechanism as far as I understand.

So if your environment supports atomic compare and swap operation (as CMPXCHG on Intel), you might get away without cursor_space_for_time setting for ultrahigh execution rates. Otherwise the atomic mutex operations would be achieved using new KGX latches.

At least on my laptop this feature isn't enabled by default (from and OracleWorld's paper I remember that it should become default in 10.2.0.2), but so far you can experiment with it if you set _kks_use_mutex_pin = true and bounce the instance (mutex structures will be stored in shared pool, so you might need to increase SP size).

There are also X$MUTEX_SLEEP and X$MUTEX_SLEEP_HISTORY fixed tables which can show some interesting information if you generate some mutex waits into those ;)

Tanel.

Original Message ----- From: Bobak, Mark To: paul.baumgartel_at_gmail.com ; Ray Stell Cc: Oracle-L Sent: Friday, January 20, 2006 12:49 PM Subject: RE: Library cache lock vs. pin

I think that's a pretty fair and accurate summary.

  --
  Mark J. Bobak
  Senior Oracle Architect
  ProQuest Information & Learning

"There are 10 types of people in the world: Those who understand binary, and those who don't."

  From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paul Baumgartel   Sent: Friday, January 20, 2006 1:45 PM   To: Ray Stell
  Cc: Oracle-L
  Subject: Re: Library cache lock vs. pin

Yes--I consulted my copy and what I take from it is that a library cache lock is held pretty much all the time on library cache objects--in shared mode during parsing (when the object is also pinned), and in null mode the rest of the time. When DDL invalidates an object, the lock is broken, so I interpret the lock as a "sentinel" of sorts that indicates whether an object requires reparsing or recompilation; the pin is the method by which objects are protected while operations on them are in progress.

That sound reasonable?

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 20 2006 - 19:19:56 CST

Re: Library cache lock vs. pin (cool stuff - KGX mutexes)