首页 > Linux操作系统 > Linux操作系统 > Mutexes in Oracle10g

Mutexes in Oracle10g

原创 Linux操作系统 作者:v_fantasy 时间:2009-05-07 20:16:00 0 删除 编辑


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, 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 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
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;
for i in 1 .. 1000
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) )
if ( i = 1 )
l_sgets := l_gets; l_sgethits := l_gethits;
l_spins := l_pins; l_spinhits := l_pinhits;
end if;
( 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 )
( 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;

从测试中可看到,使用mutex,library cache pin大幅度降低。

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: cursor: pin S
请登录后发表评论 登录


  • 博文量
  • 访问量