背景

為了學習理解oracle latch,瞭解下與latch相關的一些引數及其含義，於是便有此文，oracle latch原理及機制是非常複雜的，我們會寫一個系列，力爭
對它有一個整體的理解，提各分析問題的能力，進一步整合oracle的體系架構。

結論

1,測試資料庫版本為oracle 11.2.0.1

2,與latch spinning相關的引數為：
_sping_count控制請求獲取一個latch spinning的次數，而_kgx_spin_count控制mutex的spinning次數

3，與latch sleeping相關的引數
可見_max_sleep_holding_latch指定持有一個latch的latch sleeping的次數
還有其它的引數為：
_max_exponential_sleep，不過其值為0，且此引數含義仍未知，須進一步測試

4,與latch相關的引數就非常多了，本文主要測試了 _db_block_lru_latches ,會控制simulator lru latch的子latch個數，且其引數不能調大也不能調小，只能是8

5,從4引申出，ORACLE某些核心的隱含引數不參進行調節，即使你調了，還是會恢復原狀,因為它的值是經過嚴密測試，不能隨便調整

6,oracle v$latchname與v$latch是1對1關係

7,有些latch有子latch,即相關資料要在v$latch,v$latch_parent,v$latch_childrent獲取
如果有些latch無子latch,相關資料只能在v$latch及v$latch_parent獲取

8, 經過測試發現，就是有些latch有子latch,而有些latch呢，卻沒有子latch
ORACLE為何這樣設計呢，它的目的和目標何在呢，這是一個很值得思考的問題，我們將在下文進行繼續測試

9,本文還掌握到一個分析思路，即兩端分析法，先基於一端進行分析，嘗試分析其各種可能分支；如果還不行
換成另一端進行分析，同理，嘗試分析其各種可能分支，這是收穫最大的地方

測試

1,資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2，與latch spinning相關的引數
可見_sping_count控制請求獲取一個latch spinning的次數，而_kgx_spin_count控制mutex的spinning次數

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_spin_count 1 Amount to spin waiting for a latch
_kgx_spin_count 255 Mutex spin count

3，與latch sleeping相關的引數
可見_max_sleep_holding_latch指定持有一個latch的latch sleeping的次數
_max_exponential_sleep

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_max_exponential_sleep 0 max sleep during exponential backoff
_high_priority_process_num_yields_before_sleep 1000 the number of yields performed by high priority pr
ocessesbefore they sleep

_fg_sync_sleep_usecs 0 Log file sync via usleep
_parallel_server_sleep_time 10 sleep time between dequeue timeouts (in 1/100ths)

4,與latch相關的引數
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_latch_recovery_alignment 65534 align latch recovery structures
_latch_miss_stat_sid 0 Sid of process for which to collect latch stats
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_latch_class_0 latch class 0
_latch_class_1 latch class 1
_latch_class_2 latch class 2
_latch_class_3 latch class 3
_latch_class_4 latch class 4
_latch_class_5 latch class 5
_latch_class_6 latch class 6
_latch_class_7 latch class 7

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_latch_classes latch classes override
_ultrafast_latch_statistics TRUE maintain fast-path statistics for ultrafast latche
s

_enable_reliable_latch_waits TRUE Enable reliable latch waits
_session_idle_bit_latches 0 one latch per session or a latch per group of sess
ions

_num_longop_child_latches 1 number of child latches for long op array
_session_allocation_latches 1 one latch per group of sessions
_enqueue_hash_chain_latches 1 enqueue hash chain latches

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_lm_drm_xlatch 0 dynamic remastering forced exclusive latches
_lm_num_pt_latches 128 number of latches in the object affinity hash tabl
e

_lm_lmon_nowait_latch TRUE if TRUE makes lmon get nowait latches with timeout
loop

_gcs_latches 0 number of gcs resource hash latches to be allocate
d per LMS process

_disable_latch_free_SCN_writes_via_32cas FALSE disable latch-free SCN writes using 32-bit compare

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
& swap

_disable_latch_free_SCN_writes_via_64cas FALSE disable latch-free SCN writes using 64-bit compare
& swap

_db_block_lru_latches 8 number of lru latches
_db_block_hash_latches 8192 Number of database block hash latches
_db_blocks_per_hash_latch Number of blocks per hash latch
_gc_latches 8 number of latches per LMS process
_flashback_copy_latches 10 Number of flashback copy latches
_ktc_latches 0 number of ktc latches

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_ktu_latches 0 number of KTU latches
_kgl_latch_count 0 number of library cache latches
_kgx_latches 1024 # of mutex latches if CAS is not supported.
_k2q_latches 0 number of k2q latches
_cp_num_hash_latches 1 connection pool number of hash latches
_px_freelist_latch_divisor 2 Divide the computed number of freelists by this po
wer of 2

_deq_ht_child_latches 8 deq ht child latches

37 rows selected.

SQL>

5，我們以上述_db_block_lru_latches引數為例，結合實際進一步理解下
可見lru latches共計8個
_db_block_lru_latches 8 number of lru latches

SQL> select latch#,name,hash from v$latchname where lower(name) like '%lru%';

LATCH# NAME HASH
---------- ------------------------------ ----------
145 cache buffers lru chain 3559635447
158 simulator lru latch 405505728
197 mapped buffers lru chain 93631960
478 KFC LRU latch 3390864701

SQL> select addr,latch#,level#,name,hash from v$latch_parent where latch#=145 and name='cache buffers lru chain';

ADDR LATCH# LEVEL# NAME HASH
---------------- ---------- ---------- ------------------------------ ----------
00000000600188A8 145 2 cache buffers lru chain 3559635447

可見cache buffers lru chain共計16個子latch，可見_db_block_lru_latches引數並不是控制cache buffers lru chain的子latch的個數
SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=145 and name='cache buffers lru chain' order by 3;

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000DA70EAE8 145 1 2 cache buffers lru chain 3559635447
00000000DA70EBA8 145 2 2 cache buffers lru chain 3559635447
00000000DA71A048 145 3 2 cache buffers lru chain 3559635447
00000000DA71A108 145 4 2 cache buffers lru chain 3559635447
00000000DA7255A8 145 5 2 cache buffers lru chain 3559635447
00000000DA725668 145 6 2 cache buffers lru chain 3559635447
00000000DA730B08 145 7 2 cache buffers lru chain 3559635447
00000000DA730BC8 145 8 2 cache buffers lru chain 3559635447
00000000DA73C068 145 9 2 cache buffers lru chain 3559635447
00000000DA73C128 145 10 2 cache buffers lru chain 3559635447
00000000DA7475C8 145 11 2 cache buffers lru chain 3559635447

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000DA747688 145 12 2 cache buffers lru chain 3559635447
00000000DA752B28 145 13 2 cache buffers lru chain 3559635447
00000000DA752BE8 145 14 2 cache buffers lru chain 3559635447
00000000DA75E088 145 15 2 cache buffers lru chain 3559635447
00000000DA75E148 145 16 2 cache buffers lru chain 3559635447

16 rows selected.

6，我們換個思路，調整下_db_block_lru_latches的值，看上述資料有無變化，如無變化，表明引數_db_block_lru_latches確實與cache buffers lru chain無關
經測試，確實無關
SQL> alter system set "_db_block_lru_latches"=10;
alter system set "_db_block_lru_latches"=10
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set "_db_block_lru_latches"=10 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1409287016 bytes
Database Buffers 721420288 bytes
Redo Buffers 4964352 bytes
Database mounted.
Database opened.
SQL>

可見雖然顯式調整了此引數值，ORACLE核心還是恢復了原值，可見有些引數並非想改就改，ORACLE內部是有一種非常複雜的演算法控制的
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_db_block_lru_latches 8 number of lru latches

SQL> select latch#,name,hash from v$latchname where lower(name) like '%lru%';

LATCH# NAME HASH
---------- ------------------------------ ----------
145 cache buffers lru chain 3559635447
158 simulator lru latch 405505728
197 mapped buffers lru chain 93631960
478 KFC LRU latch 3390864701

SQL> select addr,latch#,level#,name,hash from v$latch_parent where latch#=145 and name='cache buffers lru chain';

ADDR LATCH# LEVEL# NAME HASH
---------------- ---------- ---------- ------------------------------ ----------
00000000600188A8 145 2 cache buffers lru chain 3559635447

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=145 and name='cache buffers lru chain' order by 3;

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000DA70EAE8 145 1 2 cache buffers lru chain 3559635447
00000000DA70EBA8 145 2 2 cache buffers lru chain 3559635447
00000000DA71A048 145 3 2 cache buffers lru chain 3559635447
00000000DA71A108 145 4 2 cache buffers lru chain 3559635447
00000000DA7255A8 145 5 2 cache buffers lru chain 3559635447
00000000DA725668 145 6 2 cache buffers lru chain 3559635447
00000000DA730B08 145 7 2 cache buffers lru chain 3559635447
00000000DA730BC8 145 8 2 cache buffers lru chain 3559635447
00000000DA73C068 145 9 2 cache buffers lru chain 3559635447
00000000DA73C128 145 10 2 cache buffers lru chain 3559635447
00000000DA7475C8 145 11 2 cache buffers lru chain 3559635447

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000DA747688 145 12 2 cache buffers lru chain 3559635447
00000000DA752B28 145 13 2 cache buffers lru chain 3559635447
00000000DA752BE8 145 14 2 cache buffers lru chain 3559635447
00000000DA75E088 145 15 2 cache buffers lru chain 3559635447
00000000DA75E148 145 16 2 cache buffers lru chain 3559635447

16 rows selected.

7，繼續引數_db_block_lru_latches確實與cache buffers lru chain無關，那問題來了，它與什麼有關呢？
我想，它既然與cachd buffers lru chain無關，肯定與其它的lru相關的latch有關
SQL> select latch#,name,hash from v$latchname where lower(name) like '%lru%';

LATCH# NAME HASH
---------- ------------------------------ ----------
145 cache buffers lru chain 3559635447
158 simulator lru latch 405505728
197 mapped buffers lru chain 93631960
478 KFC LRU latch 3390864701

先看下simulator lru latch

SQL> select addr,latch#,level#,name,hash from v$latch_parent where latch#=158;

ADDR LATCH# LEVEL# NAME HASH
---------------- ---------- ---------- ------------------------------ ----------
000000006001B930 158 6 simulator lru latch 405505728

可見simulator lru latch的子latch正好是8個，那麼是它嗎，我們說是有可能的，為了確保萬無一失，我們再看其它與lru相關的子latch的數量
SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=158;

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000DA75DF20 158 8 6 simulator lru latch 405505728
00000000DA7529C0 158 7 6 simulator lru latch 405505728
00000000DA747460 158 6 6 simulator lru latch 405505728
00000000DA73BF00 158 5 6 simulator lru latch 405505728
00000000DA7309A0 158 4 6 simulator lru latch 405505728
00000000DA725440 158 3 6 simulator lru latch 405505728
00000000DA719EE0 158 2 6 simulator lru latch 405505728
00000000DA70E980 158 1 6 simulator lru latch 405505728

8 rows selected.

再看下mapped buffers lru chain,可見此latch沒有子latch,我們再引申一下，就是有些latch有子latch,而有些latch呢，卻沒有子latch
ORACLE為何這樣設計呢，它的目的和目標何在呢，這是一個很值得思考的問題，我們將在下文進行繼續測試
SQL> select addr,latch#,level#,name,hash from v$latch_parent where latch#=197;

ADDR LATCH# LEVEL# NAME HASH
---------------- ---------- ---------- ------------------------------ ----------
0000000060025578 197 8 mapped buffers lru chain 93631960

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=197 order by 3;

no rows selected

再看下KFC LRU latch

SQL> select addr,latch#,level#,name,hash from v$latch_parent where latch#=478;

ADDR LATCH# LEVEL# NAME HASH
---------------- ---------- ---------- ------------------------------ ----------
00000000600458C0 478 6 KFC LRU latch 3390864701

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=478 order by 3;

no rows selected

所以，綜上，可以_db_block_lru_latches控制simulator lru latch的子latch的個數

8,我們以上述的思路再分析下_deq_ht_child_latches引數的含義
_deq_ht_child_latches 8 deq ht child latches

SQL> select latch#,name,hash from v$latchname where lower(name) like '%deq%';

LATCH# NAME HASH
---------- ------------------------------ ----------
442 AQ deq hash table latch 706044507
443 AQ dequeue txn counter latch 1724707315
446 AQ deq log statistics latch 451189647
447 AQ deq log cmt cbk chunk latch 1639732011

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=442 order by 3;

ADDR LATCH# CHILD# LEVEL# NAME HASH
---------------- ---------- ---------- ---------- ------------------------------ ----------
00000000D31A6238 442 1 4 AQ deq hash table latch 706044507
00000000D31A63A0 442 2 4 AQ deq hash table latch 706044507
00000000D31A6508 442 3 4 AQ deq hash table latch 706044507
00000000D31A6670 442 4 4 AQ deq hash table latch 706044507
00000000D31A67D8 442 5 4 AQ deq hash table latch 706044507
00000000D31A6940 442 6 4 AQ deq hash table latch 706044507
00000000D31A6AA8 442 7 4 AQ deq hash table latch 706044507
00000000D31A6C10 442 8 4 AQ deq hash table latch 706044507

8 rows selected.

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=443 order by 3;

no rows selected

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=446 order by 3;

no rows selected

SQL> select addr,latch#,child#,level#,name,hash from v$latch_children where latch#=447 order by 3;

no rows selected

9,剛才我們調整了引數_db_block_lru_latches，從原來8變成10，發現還是原來的值，那要是我調小引數值，又會如何呢
老樣子，引數_db_block_lru_latches不能調大也不能調小

SQL> alter system set "_db_block_lru_latches"=5 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1409287016 bytes
Database Buffers 721420288 bytes
Redo Buffers 4964352 bytes
Database mounted.
Database opened.

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_db_block_lru_latches 8 number of lru latches

個人簡介：

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院

河北廊坊新奧集團公司

專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

oracle 11g latch之系列一

背景

結論

測試

個人簡介：

相關文章