Mutexes in Oracle10g

To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2.
For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins.
Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism.
The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex toTRUE.

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


Oracle10g中，對shared pool中的一些Serialization operation使用更輕量的 KGX mutexes (_use_kks_mutex) 取代library cache pin，從而降低CPU Usage.

_use_kks_mutex = TRUE的時候，

SQL> exec p
SQL AREA 393 3 630 0
SQL AREA 393 3 630 0
SQL AREA 0 0 0 0

PL/SQL procedure successfully completed.

SQL> exec p
SQL AREA 394 3 632 0
SQL AREA 394 3 632 0
SQL AREA 0 0 0 0

PL/SQL procedure successfully completed.

_use_kks_mutex = FALSE的時候，

SQL> set serveroutput on
SQL> exec p
SQL AREA 360 3 4,611 3,960
SQL AREA 360 3 5,610 4,959
SQL AREA 0 0 999 999

PL/SQL procedure successfully completed.

SQL> EXEC P
SQL AREA 363 3 5,643 4,986
SQL AREA 363 3 6,642 5,985
SQL AREA 0 0 999 999

PL/SQL procedure successfully completed.

儲存過程程式碼如下，

create or replace procedure p
authid current_user
as
l_ns varchar2(4000);
l_gets number;
l_gethits number;
l_pins number;
l_pinhits number;
l_sgets number;
l_sgethits number;
l_spins number;
l_spinhits number;
begin
for i in 1 .. 1000
loop
execute immediate
’select namespace, gets, gethits, pins, pinhits
from v$librarycache
where namespace = ”SQL AREA” ‘
into l_ns, l_gets, l_gethits, l_pins, l_pinhits;

if ( i in (1,1000) )
then
if ( i = 1 )
then
l_sgets := l_gets; l_sgethits := l_gethits;
l_spins := l_pins; l_spinhits := l_pinhits;
end if;
dbms_output.put_line
( l_ns || to_char(l_gets,’999,999′) ||
to_char(l_gethits,’999,999′) ||
to_char(l_pins,’999,999′) ||
to_char(l_pinhits,’999,999′) );
if ( i = 1000 )
then
dbms_output.put_line
( l_ns || to_char(l_gets-l_sgets,’999,999′) ||
to_char(l_gethits-l_sgethits,’999,999′) ||
to_char(l_pins-l_spins,’999,999′) ||
to_char(l_pinhits-l_spinhits,’999,999′) );
end if;
end if;
end loop;
end;
/

從測試中可看到，使用mutex，library cache pin大幅度降低。
Oracle這個演算法改進是oracle10g中的一個亮點。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/119501/viewspace-607461/，如需轉載，請註明出處，否則將追究法律責任。