Oracle 10g/11g Latch机制的变化:
前面曾经提到,Oracle的Latch机制采用spin来进行持有CPU的不断尝试,虽然通常Latch的获取会非常快(一般在微秒级),但是很多时候Latch竞争还是会引发极为严重的CPU争用。所以从Oracle 10g开始,Oracle尝试引入一种新的机制来代替传统的Latch机制,这就是Mutex机制,也就是互斥机制。和Latch相比,一个Mutex Get大约仅需要30~35个指令,而Latch Get则需要大约150~200个指令,同时在大小上,每个Mutex仅占大约16Bytes空间,而Latch在10gR2中要占用大约112Bytes空间。
在Oracle 10.2..0.1中一个新的参数_kks_use_mutex_pin被引入,不过缺省值为False:
sys@TQGZS> select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
2 from sys.x$ksppi x , sys.x$ksppcv y
3 where x.indx = y.indx
4 and x.ksppinm like '%&par%'
5 /
Enter value for par: kks
old 4: and x.ksppinm like '%&par%'
new 4: and x.ksppinm like '%kks%'
NAME VALUE DESCRIB
------------------------- --------------- --------------------------------------------------------
_kks_use_mutex_pin FALSE Turning on this will make KKS use mutex for cursor pins.
这意味着新的Mutex机制已经准备好了用于应付Cursor Pin处理。在Oracle 10gR2/11gR1随后的版本中,这个参数被设置为Ture:
sys@CCDB> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
sys@CCDB> @GetHidPar.sql
Enter value for par: mutex
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%mutex%'
NAME VALUE DESCRIB
------------------------------ -------------------- --------------------------------------------------------
_kks_use_mutex_pin TRUE Turning on this will make KKS use mutex for cursor pins.
Mutex机制首先被引入用于替代Library Cache Latch以及Library Cache Pin等机制。使用Mutex Pin机制Oracle能够使用更少的CPU资源获得更好的性能。由于Mutex Pin机制带来的Cursor管理上性能提升,所以曾经用来缓解Cursor Latch竞争的参数CURSOR_SPACE_FOR_TIME从Oracle 10.2.0.5和Oracle 11.1.0.7开始将不再被支持。
在Oracle 11.1.0.6中,以下是数据库中Library Cache相关的Latch与等待:
sys@CCDB> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
sys@CCDB> select name from v$latch where name like '%library%';
NAME
------------------------------
library cache load lock
sys@CCDB> select name from v$event_name where name like '%library%';
NAME
------------------------------
library cache pin
library cache lock
library cache load lock
library cache: mutex X
library cache: mutex S
OSD IPC library
library cache revalidation
library cache shutdown
8 rows selected.
在Oracle Database 11g中,和Mutex相关的数据库描述已经被引入,首先的变化是和Library Cache相关的Latch仅余library cache load lock:
sys@CCDB> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
sys@CCDB> select name from v$latch where name like '%library%';
NAME
------------------------------
library cache load lock
其次是等待事件引入了Library Cache:Mutex S/X等待,用以描述因为Mutex竞争而导致的Library Cache等待:
sys@CCDB> select name from v$event_name where name like '%library%';
NAME
------------------------------
library cache pin
library cache lock
library cache load lock
library cache: mutex X
library cache: mutex S
OSD IPC library
library cache revalidation
library cache shutdown
8 rows selected.
虽然在Oracle Database 11gR1中,这些变化已经可以清晰地看到,但是一些问题仍然存在,Oracle仍然在通过一系列不断努力和改进使得这些新的变化走向成熟。
当然Library Cache Latch在某些条件下仍然会被用到,Oracle数据库的很多内容还在不断变化中。关于Mutex的信息主要可以通过两个视图来查询:v$mutex_sleep和v$mutex_sleep_history。以下是一个Oracle 11g生产环境中的查询示范输出:
sys@CCDB> select SLEEP_TIMESTAMP,MUTEX_TYPE,GETS,SLEEPS,LOCATION,MUTEX_VALUE
2 from v$mutex_sleep_history where rownum <10;
SLEEP_TIMESTAMP MUTEX_TYPE GETS SLEEPS LOCATION MUTEX_VALUE
------------------------------ --------------- ---------- ---------- -------------------- ----------------
11-MAY-10 08.46.15.176426 PM Cursor Pin 20439381 80 kksfbc [KKSCHLPIN1] 00
13-MAY-10 04.01.19.066757 PM Library Cache 39098 113171 kglhdgn1 62 0000021E00000000
13-MAY-10 04.50.40.279082 PM Library Cache 172560 1384786 kgllkdl1 85 00
06-MAY-10 09.34.57.393852 PM Library Cache 76718 43 kgllkdl1 85 000001CC00000000
06-MAY-10 10.24.47.734523 PM Library Cache 103771 383024 kglhdgn1 62 0000031B00000000
13-MAY-10 04.50.40.328295 PM Library Cache 150176 46662 kgllkdl1 85 000001C200000000
13-MAY-10 04.50.40.291834 PM Library Cache 208155 45 kgllkdl1 85 00
09-MAY-10 01.00.03.163520 AM Library Cache 155 1 kglhdgn1 62 00
07-MAY-10 03.33.35.071512 PM Library Cache 2933 5 kgllkdl1 85 0000029700000000
9 rows selected.
- The End -
参考至:http://www.dbtan.com/2010/05/oracle-10g11g-latch.html
如有错误,欢迎指正
邮箱:czmcj@163.com