关于oracle mutex和latch的问题

 

关于latch和mutex的问题
From:http://www.itpub.net/thread-1004815-1-1.html
A:

latch是闩锁，是一种串行化机制，用来保护SGA中的内存结构。
mutex是什么，也是一种串行化机制，是互斥锁？
个人对mutex没有什么概念。这个是10G新引进的？9I好像没有啊。
哪位明白人，给帮忙详细解释下mutex，最好还能和latch 做下比较。
谢谢。

B:

BTW, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new in 10.2 and they enable shared access to objects in somewhat similar manner to shared latche; every successful get of a particular mutex will increment its value and a release will decrement. When the count is zero, no one has the mutex and it is safe to get it in exclusive mode. However, they are more fine-grained than kgl latches and provide a better wait mechanism, as far as I understand.
So if your environment supports atomic compare and swap operation (such 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 the new KGX latches.
At least on my laptop this feature isn't enabled by default (from an OracleWorld 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 the shared pool, so you might need to increase shared pool size).

There are also X$MUTEX_SLEEP and X$MUTEX_SLEEP_HISTORY fixed tables that can show some interesting information if you generate some mutex waits into them.

Now, I don't suggest for a moment that you have to understand this response. It is at an extremely deep technical level, and the practical applications of such understanding are probably few and far between. My point, though, is that you must take the time to study and understand Oracle internal operations if you are to have success in Oracle performance optimization. There are no shortcuts to making a database application run efficiently. You must understand good SQL practice, good PL/SQL practice and good host-language practice for starters. Beyond that, you ought to know how Oracle manages concurrency, how Oracle processes SQL statements and how Oracle performs data and code (SQL and PL/SQL) caching. If you're asking the question "How do I set up an Oracle database for optimal performance?" you have a long way to go. Don't try to avoid the effort; take the time to study and learn, and you will reap the rewards.

B:

SQL> show parameter kks

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_kks_use_mutex_pin                   boolean     TRUE
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
今天有人问：
show parameter kks怎么没有结果，因为我的环境该变量没有使用默认值。

15:54:57 SQL> SELECT NAME,VALUE,ISDEFAULT FROM V$PARAMETER WHERE NAME LIKE '%kks
%';

NAME                           VALUE                          ISDEFAULT
------------------------------ ------------------------------ ---------
_kks_use_mutex_pin             TRUE                           FALSE

当然我们利用其他方法show hidder parameter ：

http://space.itpub.net/?uid-756652-action-viewspace-itemid-468211

D:

oracle很多东西在内存里是通过latch保护的，latch最大的一个问题就是少，对用户来说，library cache pin，cbc, enqueue hash，这些都是一个保护多个，实际上造成我在需求a的时候，因为保护a的latch正在保护被调用的b，而导致我没有办法访问到本来就没有被访问的a，很恶心，这样做可能是因为latch需求太高，如果极端的实现一对一保护，会占用大量的资源，搞不定，mutext很可能就是提高这些，尽可能达到一对一，降低cpu这些资源，还是感觉挺不错的，不过10r2开始的时候，至少到10204还是有cursor mutex的bug。

E:

互斥是互相排斥的对象的简写形式。与闩锁类似，互斥是一种低级串行化机制，用来控制 SGA 中共享数据结构的访问。

互斥是闩锁的替代方案，因为获取互斥的速度更快，并且更小。

F:

其实算是对latch的改良机制吧，mutex早在操作系统中就有算法实现了吧。

G:

转两个帖子，讲得很深入
对library cache lock 与library cache pin的内核分析
来自 http://www.违规广告.cn/forum/sho ... d=3975&CPages=1
The library cache is not one cache, but many. It contains the pseudo code for
PL/SQL program units. It contains parse trees and execution plans for shareable
SQL statements. It also contains abstract representations in a form called DIANA
of the database objects referenced by the SQL statements. The information is
needed in this form for PL/SQL program unit compilation and SQL statement
parsing and execution, despite the fact that the dictionary cache contains the
same information in a different form. The library cache also contains control
structures such as synonym translations, dependency tracking information, and
library cache locks and pins.
Library cache locks are referred to as breakable parse locks in the Oracle
documentation. They are applied to the library cache objects for SQL statements
and PL/SQL program units, and recursively to the library cache objects for the
database objects on which they depend. Library cache locks are held in shared
mode during parse operations and are converted to null mode thereafter. If a
DDL statement later modifies the definition of a database object, then the library
cache information for that database object and all dependent library cache
objects is invalidated by breaking the library cache locks.
Library cache locks can only be broken, however, when the library cache object is
not also pinned. A pin is applied to the library cache object for a PL/SQL program
unit or SQL statement while it is being compiled, parsed, or executed. Pins are
normally held in shared mode, but are also held in exclusive mode while the
library cache information for the object is being changed. The library cache
objects for pipes and sequences are most subject to change. When a library cache
object is pinned, pins are applied to all referenced objects in turn. When a pin is
applied to the library cache object for a database object, then a corresponding
row cache enqueue lock is acquired on the underlying data dictionary row,
thereby preventing conflicting DDL.
Every object in the library cache has a handle that acts as the resource structure
for library cache locks and pins. The handle, lock, and pin structures are all
dynamically allocated within the shared pool. The handle implements two-way
linked lists of locks held, locks waited for, pins held, and pins waited for. Sessions
waiting for a lock or pin report a library cache lock or library cache pin wait
respectively

---------------------
Mutex 来自 http://wiki.oracle.com/page/Mutex?t=anon
Mutex is the short form mutual exclusion object.
A mutex, similar to a latch, is a low-level serialization mechanism used to control access to a shared data structure in the SGA.
Serialization is required to avoid an object being:
- Deallocated while someone is accessing it
- Read while someone is modifying it
- Modified while someone is modifying it
- Modified while someone is reading it
Mutexes can be defined and used in different ways, as in the following examples:
- Each structure being protected by a mutex can have its own mutex (for example, a parent cursor has its own mutex, and each child cursor has its own mutex)
- Each structure can be protected by more than one mutex, with each mutex protecting a different part of the structure.
- A mutex can protect more than one structure.
Although mutexes an latches are both serialization mechanisms, mutexes have certain features that latches do not:
- Smaller and Faster
Mutexes are an alternative to latches because they are smaller and much faster to get. A mutex get uses fewer instructions compared to a latch get. A mutex takes less memory space compared to a latch.
- Less Potential for False Contention
Latch typically protect multiple objects. when a latch protects one or more hot objects, the latch itself can become a serialization point when accessing any of the objects protected by that latch. This can b e a false contention point, where the contention is for the protection mechanism (that is, latch), rather than the target object you are attempting to access. Unlike latches, with mutexes it is possible to create mutex for each structure protected. this mean that false contention is much less likely because each structure can be protected by its own mutex.
- Replace Latches and Pins
A mutex can be concurrently referenced by many sessions, providing all sessions reference the mutex in S (Shared) mode. The total number of sessions referencing a mutex in S mode is called the reference count ("ref count". The ref count for a mutex is stored within the mutex itself. A mutex can also be held in X (eXclusive) ode by one session only.
Mutexes have a dual nature; they can act as a serialization mechanism (for example, latch) and also as a pin (for example, preventing an object from aging out). For example, the ref count of a mutex is a replacement for a library cache pin. Instead of each session creating and then deleting a library cache pin when executing a cursor, each session increments and decrements the ref count (so the ref count replace n distinct pins).
Note: Latches and mutexes are independent mechanisms, that is, a process can hold a latch and a mutex at the same time.
Mutex operations are faster and have less contention than latches, but mutex operations still have waits associated with them. Two V$ view provide detail of mutex sleeps:
- V$MUTEX_SLEEP shows a summary of sleeps and wait time for particular mutex_type/location combination.
select * from v$mutex_sleep;
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
-------------------------------- ---------------------------------------- ---------- ----------
Cursor Stat kksIterCursorStat [KKSSTALOC6] 103 163
Cursor Stat kksFindCursorStat [KKSSTALOC3] 23157 36724
Cursor Parent kksfbc [KKSCHLCREA] 1799 10170
Cursor Parent kkspsc0 [KKSPRTLOC27] 26 627
Cursor Parent kkspsc0 [KKSPRTLOC26] 122 2872
Cursor Parent kkshbbr [KKSPRTLOC15] 660 1779
Cursor Parent kksLoadChild [KKSPRTLOC4] 1181 6932
Cursor Parent kksfbc [KKSPRTLOC2] 9006 34053
Cursor Parent kksfbc [KKSPRTLOC1] 2831 144439
Cursor Pin kksLockDelete [KKSCHLPIN6] 5021 1055990
Cursor Pin kkslce [KKSCHLPIN2] 265549 2792810468
Cursor Pin kksfbc [KKSCHLPIN1] 1203 5132409
Cursor Pin kksfbc [KKSCHLFSP2] 9279 56902065
- V$MUTEX_SLEEP_HISTORY show sessions sleeping for a particular mutex_type/location combination by time while it is held by a specific holding session.
Mutex wait event have two catagories:
- cursor:mutex indicates that the mutex waits on parent cursor operations and statistics block operations.
- cursorin events are wait for cursor pin operations, where a mutex has replaced the latch:library cache pin.
Mutex wait events are of two types:
- Short-duration events that should rarely be seen. These occur when one process attempts to update the mutex while it is being changed by another process. The waiting process will spin waiting for the mutex to be available. For example, cursorin S is incremented when another process is updating the reference count(pin) of shared cursor.
- Long-duration events occur when a process must wait for other processes to finish their operation. For example, cursor:mutex X is incremented when a process wants an exclusive access but the mutex is being held exclusive or shared by another process.
Mutex-Protected Operations:
A mutex is another protection mechanisms that can protect critical operations. From Oracle database V. 10.2.0.2 and later, a SELECT from the V$SQLSTATS view is protected by mutexes. The use of mutex-protectd operations is significantly faster than latched operations. The child cursor lists are protected by mutexes.

surachart         Latest page update: made by surachart , Feb 27 2009, 10:54 AM EST (about this update - complete history)
Keyword tags: mutex

Bookmark: Del.icio.us    StumbleUpon    Facebook

H:

Mutexes in Oracle10g
from:http://yumianfeilong.com/html/2007/05/23/90.html

摘要Mutexes的介绍从http://archive.netbsd.se/?ml=oracle-l&a=2006-04&t=1969601

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中的一个亮点。