【原創】用示例說明表資料中出現熱塊&Latch的場景，並給出解決方案？
引言：Latch爭用就是由於多個會話同一時間訪問同一個資料塊引起的，這也是我們常說的熱塊。解決方法：把記錄打散到多個資料塊中，減少多個會話同一時間頻繁訪問一個資料塊機率，防止由於記錄都集中在一個資料塊裡產生熱塊現象。下面我們用實驗來說明熱塊是如何產生和解決的。
session：19
LEO1@LEO1>select distinct sid from v$mystat; 大家先了解一下LEO1使用者的SID是19
SID
-----------------
19
LEO1@LEO1>create table latch_table1 as select * from dba_objects; 建立latch_table1表
Table created.
LEO1@LEO1>select count(*) from latch_table1; 這個表中有71961條記錄
COUNT(*)
----------------
71961
LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','latch_table1'); 我們對錶做一個全面分析讓最佳化器瞭解表資料是如何分佈的。
PL/SQL proceduresuccessfully completed.
下面我們用dbms_rowid.rowid_block_number 函式來查出一個資料塊上有多少條記錄
dbms_rowid.rowid_block_number作用：函式返回輸入ROWID對應的資料塊編號
selectdbms_rowid.rowid_block_number(rowid), count(*) block_sum_rows from latch_table1 group bydbms_rowid.rowid_block_number(rowid) order by block_sum_rows ;
這裡顯示出每個資料塊上有多少條記錄，按記錄數從大到小排列
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)BLOCK_SUM_ROWS
--------------------------------------------------
234 81
391 81
225 82
259 82
220 83
233 83
279 84
274 85
219 88
275 89
277 89
276 90
278 90
我們看到一個資料塊中最多是90行記錄
select'LATCH_TABLE1' , block_sum_rows , count(*) con_rows_sum_blocks from
(selectdbms_rowid.rowid_block_number(rowid), count(*) block_sum_rows from latch_table1 group bydbms_rowid.rowid_block_number(rowid) order by block_sum_rows)
group byblock_sum_rows order by con_rows_sum_blocks;
有一致記錄數的資料塊有多少個，舉個例子好理解，上面我們看到276和278塊上都用90條記錄，現在我們想知道有90條記錄的塊一共有多少個我們用con_rows_sum_blocks列名錶示（一致記錄數的資料塊總和），每個塊上的記錄數我們用block_sum_rows列名錶示。
'LATCH_TABLE BLOCK_SUM_ROWS CON_ROWS_SUM_BLOCKS
-------------------------- ----------------------- -------------- -------------------
LATCH_TABLE1 85 1
LATCH_TABLE1 54 1
LATCH_TABLE1 84 1
LATCH_TABLE1 88 1
LATCH_TABLE1 25 1
LATCH_TABLE1 63 2
LATCH_TABLE1 90 2
LATCH_TABLE1 83 2
LATCH_TABLE1 82 2
LATCH_TABLE1 89 2
LATCH_TABLE1 64 4
LATCH_TABLE1 81 10
LATCH_TABLE1 80 17
LATCH_TABLE1 65 18
LATCH_TABLE1 79 20
LATCH_TABLE1 74 27
LATCH_TABLE1 73 28
LATCH_TABLE1 77 28
LATCH_TABLE1 72 29
LATCH_TABLE1 78 29
LATCH_TABLE1 75 33
LATCH_TABLE1 76 36
LATCH_TABLE1 71 54
LATCH_TABLE1 66 69
LATCH_TABLE1 70 75
LATCH_TABLE1 69 152
LATCH_TABLE1 67 158
LATCH_TABLE1 68 223
28 rows selected.
我們從上面看到有67，68，69條記錄的資料塊數是最多的（152，158，223），這就說明我們的LATCH_TABLE1表中大部分資料塊上每個資料塊中都分配了70條左右的記錄。如果有多個會話同一時間對同一個資料塊做DML操作那麼就會出現latch爭用。
LEO1@LEO1> setlinesize 300
LEO1@LEO1> setserveroutput on 啟動螢幕顯示功能
LEO1@LEO1>create or replace procedure p1 建立儲存過程，作用：迴圈執行8000次訪問資料塊
as
leo number;
begin
for i in 1..8000
loop
select count(*) into leo fromlatch_table1 where object_type='TABLE';
end loop;
dbms_output.put_line('successfully');
end;
/
2 3 4 5 6 7 8 9 10 11
Procedure created.
從會話級別定位latch的消耗情況
session：19
LEO1@LEO1>execute p1;
successfully
PL/SQL proceduresuccessfully completed.
session：148
LEO1@LEO1>execute p1;
successfully
PL/SQL proceduresuccessfully completed.
session：147
LEO1@LEO1>execute p1;
successfully
PL/SQL proceduresuccessfully completed.
session：27
LEO1@LEO1>select * from v$latchholder; 從會話級別定位latch的消耗情況
PID SID LADDR NAME GETS
--------------------------------------------------------------------------------------------------------
33 147 0000000087596920 cache buffers chains 107426
28 19 000000008762E260 cache buffers chains 200573
31 148 00000000875981B8 cache buffers chains 208601
PID ：持有latch的程式id
SID ：持有latch的會話id
LADDR ：latch的地址
NAME ：latch名字
GETS ：獲得latch的次數，可能是等待模式，也可能是非等待模式。
當19，147，148會話一起執行execute p1儲存過程時，我抓取到了這3個會話latch爭用資訊，說明它們不是按順序來持有的，是一種無序無規則的爭搶，誰先搶到算誰的。
這麼說是有根據的，你在抓取的時候會發現它們是按照無序的狀態斷斷續續蹦出來的，以一種無規則的方式呈現，顯示的時間很短暫。
LEO1@LEO1>select s1.sid,s2.event from v$session s1,v$session_wait s2 where s1.sid=s2.sidand s1.status='ACTIVE' and s2.event like 'latch%';
SID EVENT
--------------------------------------------------------------------------
19 latch: cache buffers chains
147 latch: cache buffers chains
148 latch: cache buffers chains
看在會話等待事件檢視中總是有這3個會話的“latch 連結串列”的爭用，原因當多個會話同時去訪問相同資料塊時，它們首先申請latch獲得訪問許可權，如果此時申請失敗，將會產生“latch: cache buffers chains”等待事件，這是因為多個會話在爭搶latch資源所致。
我們再來看看buffer busy waits 爭用是怎麼產生的。
首先建立p2 p3 p4 儲存過程，這3個儲存過程分別更新不同物件型別的行。我為什麼要這麼做呢？select object_type,count(*) sum_object from latch_table1 group by object_type orderby sum_object; 這句SQL統計出latch_table1表中相同物件型別各有多少行從小到大排序，如下所示
OBJECT_TYPE SUM_OBJECT
-----------------------------
JAVA RESOURCE 834
LOB 864
PACKAGE BODY 1251
PACKAGE 1311
TYPE 2779
TABLE 2806
INDEX 3798
VIEW 5029
JAVA CLASS 22920
SYNONYM 27785
為了平衡執行時間，我選取了TABLE INDEX VIEW這三個物件型別的行為測試目標，大家想一想這些行在資料塊中儲存的位置都是無序的，也就是說是打散存放的（因為插入時就是無序插入的，所以儲存時也是無序儲存的），既然是無序的，那麼我們可以認為大部分資料塊上都儲存著這三類的記錄行（當然也儲存著其他物件型別的行）。當多個會話同時對包含這三類的資料塊進行DML操作的時候就會發生“buffer busy waits”爭用，我們來看一看。
create or replaceprocedure p2 迴圈100次修改物件型別為表的記錄行
as
leo number;
begin
for i in 1..100
loop
update latch_table1 setobject_type='TABLE' where object_type='TABLE';
end loop;
dbms_output.put_line('successfully');
end;
/
create or replaceprocedure p3 迴圈100次修改物件型別為索引的記錄行
as
leo number;
begin
for i in 1..100
loop
update latch_table1 setobject_type='INDEX' where object_type='INDEX';
end loop;
dbms_output.put_line('successfully');
end;
/
create or replaceprocedure p4 迴圈100次修改物件型別為檢視的記錄行
as
leo number;
begin
for i in 1..100
loop
update latch_table1 setobject_type='VIEW' where object_type='VIEW';
end loop;
dbms_output.put_line('successfully');
end;
/
注意：這3個儲存過程要一起執行才可以，其實你不用擔心要多麼多麼同步精確，你開啟3個視窗，按順序在每個視窗上執行即可，因為我們是迴圈100次的執行過程也是需要一段時間的。這我有點囉嗦了，為了給初學的朋友說明一下。
session：19
LEO1@LEO1>execute p2;
successfully
PL/SQL proceduresuccessfully completed.
session：148
LEO1@LEO1>execute p3;
successfully
PL/SQL proceduresuccessfully completed.
session：147
LEO1@LEO1>execute p4;
successfully
PL/SQL proceduresuccessfully completed.
在另外開啟一個視窗，在上面三個儲存過程執行的過程中，我們檢視v$latchholder檢視，看看沒有沒出現latch爭用現象，在看看會話等待事件檢視中有沒有buffer busy waits等待事件發生。大家在測試的時候需要多執行幾遍，因為latch的等待時間非常短一閃而過，多抓幾遍就可以找到了。
session：27
LEO1@LEO1>select * from v$latchholder; 從會話級別定位latch的消耗情況
PID SID LADDR NAME GETS
--------------------------------------------------------------------------------------------------------
33 147 0000000087687710 cache buffers chains 748903
28 19 0000000087587460 cache buffers chains 2979267
31 148 00000000875B10A8 cache buffers chains 162116
LEO1@LEO1>select s1.sid,s2.event from v$session s1,v$session_wait s2 where s1.sid=s2.sidand s1.status='ACTIVE' and s2.event like '%buffer%';
SID EVENT
--------------------------------------------------------------------------
19 buffer busywaits
147 buffer busywaits
148 buffer busywaits
19 latch: cachebuffers chains
147 latch: cachebuffers chains
148 latch: cachebuffers chains
瞭然，我們要找的東東全都呈現在我們眼前，跟我們一開始設想的一樣，當多個會話同時DML訪問資料塊時就會造成熱塊，例如19會話現在需要訪問一個資料塊，而這個資料塊正在被另一個會話從硬碟讀取到記憶體或者正在修改這個資料塊，那麼19會話就需要等待，此時會產生一個buffer busy waits等待事件。我們看到了DML操作不僅有cache buffers chains 還有bufferbusy waits，這兩個latch是分別發生在訪問資料塊的不同時刻。我們理解了這些就能定位我們的系統瓶頸在哪裡。
解決方案
既然我們已經知道latch是由於熱塊導致的，我們在生產中會頻繁執行sql訪問一些相同的資料塊，我們很糾結，是降低sql執行次數還是把資料行分佈到更多的資料塊上來解決latch爭用問題呢，顯然後者才是可行性方案，下面我們把相同的資料分佈到更多的資料塊上來消除latch的爭用。
session：19
LEO1@LEO1>create table latch_table2 as select * from dba_objects where rownum<=4; 建立新表
Table created.
LEO1@LEO1> selectcount(*) from latch_table2; 表裡只有4條記錄
COUNT(*)
-----------------
4
LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','latch_table2'); 對錶做個分析
PL/SQL proceduresuccessfully completed.
LEO1@LEO1>alter table latch_table2 minimize records_per_block; 指定每個資料塊中可存放行數減少到最小（即4行）
Table altered.
LEO1@LEO1>insert into latch_table2 select * from dba_objects; 我們又插入71965行記錄
71965 rowscreated.
LEO1@LEO1>commit; 提交
Commit complete.
LEO1@LEO1>select count(*) from latch_table2; 我們現在表裡有71969行，那麼存放在幾個資料塊中呢
COUNT(*)
-----------------
71969
LEO1@LEO1>select 'LATCH_TABLE2' , block_sum_rows , count(*) con_rows_sum_blocks from
(selectdbms_rowid.rowid_block_number(rowid), count(*) block_sum_rows from latch_table2 group bydbms_rowid.rowid_block_number(rowid) order by block_sum_rows)
group byblock_sum_rows order by con_rows_sum_blocks; 2 3
'LATCH_TABLEBLOCK_SUM_ROWS CON_ROWS_SUM_BLOCKS
------------ ---------------------------------
LATCH_TABLE2 1 1
LATCH_TABLE2 4 17992
這句sql我們之前已經用過，可以計算出具有相同行數的資料塊有多少個，17992*4+1=71969，符合我們表裡的行數，我們現在知道了這些資料是存放在17993個資料塊中。我們可以看出來相同的資料量在latch_table1中和latch_table2中佔用的資料塊個數相差很大，latch_table2比latch_table1多很多。
LEO1@LEO1> setautotrace traceonly; 我們看一下2個表的執行計劃
LEO1@LEO1> altersystem flush shared_pool; 清空sharepool
System altered.
LEO1@LEO1> altersystem flush buffer_cache; 清空data_buffer_cache
System altered.
LEO1@LEO1>select * from latch_table1;
71961 rowsselected.
Execution Plan
----------------------------------------------------------
Plan hash value:3200799752
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71961 | 6816K| 287 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL | LATCH_TABLE1 |71961 | 6816K| 287 (1)| 00:00:04 |
----------------------------------------------------------------------------------
全表掃描了71961行，這71961全都在記憶體中，沒有發生物理讀
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
113968 consistent gets
0 physical reads
12676 redo size
8251368 bytes sent via SQL*Net to client
53291 bytes received via SQL*Net from client
4799 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71961 rows processed
LEO1@LEO1>select * from latch_table2;
71969 rowsselected.
Execution Plan
----------------------------------------------------------
Plan hash value:3844951557
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL | LATCH_TABLE2 | 4| 400 | 3 (0)| 00:00:01 |
全表掃描了71969行，但有18033次的物理讀
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21638 consistent gets
18033 physical reads
0 redo size
8252148 bytes sent via SQL*Net to client
53290 bytes received via SQL*Net from client
4799 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71969 rows processed
小結：我們對兩個資料量相同的表做了全表掃描執行計劃對比，但發現latch_table2比latch_table1的物理讀多了18033次，原因就是latch_table2表中的行分佈到更多的資料塊上，它在記憶體中找不到後就會去磁碟上找，導致oracle需要讀取更多的資料塊來完成查詢。這樣我們就可以減少latch在資料塊上的爭用現象。但同時也增加了讀寫資料塊的個數，缺點就是對資料庫效能有一定影響。
我們可以想象當有多個會話同時訪問資料的時候，資料越分散，會話訪問的資料塊範圍越廣，熱塊越不容易形成，latch爭用機率就會大大降低。
PDF下載版
(177.02 KB, 下載次數: 0)
2012.12.08
天津&winter
分享技術~成就夢想
Blog：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-750791/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【原創】用示例說明索引資料塊中出現熱塊&Latch的場景，並給出解決方案索引
- 區塊鏈溯源解決方案_區塊鏈溯源應用場景區塊鏈
- 【原創】說說你是如何理解Oracle資料庫中latch和enqueue lock的?Oracle資料庫ENQ
- Oracle Latch 說明Oracle
- Datagrip連線SQLserver表中出現中文亂碼解決方案SQLServer
- 區塊鏈公鏈開發,區塊鏈應用場景解決方案介紹區塊鏈
- RAG場景、資料、應用難點與解決
- 面向複雜場景的高效能表單解決方案
- 智慧安防的主要應用場景和資料採集標註解決方案 | 景聯文科技
- 資料緩衝區熱鏈和熱塊爭用及解決方法
- oracle資料塊轉儲說明Oracle
- 區塊鏈技術物流應用場景開發方案,區塊鏈資料上鍊區塊鏈
- latch free 等待事件說明事件
- 電商API原資料介面的應用場景API
- SPM適用的場景和示例
- 分散式事務解決方案與適用場景分析分散式
- 2021下半年中國金融雲（應用）解決方案市場份額（附原資料表）
- 直播場景影片和特效解決方案特效
- PHP 併發場景的幾種解決方案PHP
- 說說你對堆的理解？如何實現？應用場景？
- 用Excel做資料說明――抽樣說明工具Excel
- latch free 等待事件說明(轉)事件
- 2020年中國智慧交通管理應用及解決方案市場份額（附原資料表）
- 2021上半年中國主要金融雲（應用）解決方案市場份額（附原資料表）
- 2022上半年中國金融雲（應用）解決方案市場份額（附原資料表）
- 文字識別解決方案-OCR識別應用場景解析
- undo表空間出現壞塊導致資料庫重啟問題解決資料庫
- 聚焦五大場景，騰訊安全推出“可信城市”區塊鏈解決方案區塊鏈
- 說說對WebSocket的理解？應用場景？Web
- Oracle面對“資料傾斜列使用繫結變數”場景的解決方案Oracle變數
- 美創科技8個醫療資料安全場景化方案推出！
- 用sql語句建立表的時候給列指定說明(轉)SQL
- jboss to weblogic移值說明(原創)Web
- Activiti（一） activiti資料庫表說明資料庫
- 轉：latch wait events 說明分析AI
- 【原創】Oracle 並行原理與示例總結Oracle並行
- rsync+inotify實現實時同步（小業務場景解決方案）
- “AI鑑黃師”資料解決方案 | 景聯文科技AI