ITPub博客

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

Mutexes in Oracle10g

原创 Linux操作系统 作者:wuhesheng 时间:2009-06-24 17:22:15 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 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: cursor: pin S
请登录后发表评论 登录
全部评论

注册时间:2008-08-20

  • 博文量
    34
  • 访问量
    64672