【原創】用示例說明表資料中出現熱塊&Latch的場景,並給出解決方案?

leonarding發表於2012-12-08

引言:Latch爭用就是由於多個會話同一時間訪問同一個資料塊引起的,這也是我們常說的熱塊。解決方法:把記錄打散到多個資料塊中,減少多個會話同一時間頻繁訪問一個資料塊機率,防止由於記錄都集中在一個資料塊裡產生熱塊現象。下面我們用實驗來說明熱塊是如何產生和解決的。

session19  

LEO1@LEO1>select distinct sid from v$mystat;    大家先了解一下LEO1使用者的SID19

       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;

有一致記錄數的資料塊有多少個,舉個例子好理解,上面我們看到276278塊上都用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.

我們從上面看到有676869條記錄的資料塊數是最多的(152158223),這就說明我們的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的消耗情況

session19

LEO1@LEO1>execute p1;

successfully

PL/SQL proceduresuccessfully completed.

session148

LEO1@LEO1>execute p1;

successfully

PL/SQL proceduresuccessfully completed.

session147

LEO1@LEO1>execute p1;

successfully

PL/SQL proceduresuccessfully completed.

session27

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的次數,可能是等待模式,也可能是非等待模式。

19147148會話一起執行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次的執行過程也是需要一段時間的。這我有點囉嗦了,為了給初學的朋友說明一下。

session19

LEO1@LEO1>execute p2;

successfully

PL/SQL proceduresuccessfully completed.

session148

LEO1@LEO1>execute p3;

successfully

PL/SQL proceduresuccessfully completed.

session147

LEO1@LEO1>execute p4;

successfully

PL/SQL proceduresuccessfully completed.

在另外開啟一個視窗,在上面三個儲存過程執行的過程中,我們檢視v$latchholder檢視,看看沒有沒出現latch爭用現象,在看看會話等待事件檢視中有沒有buffer busy waits等待事件發生。大家在測試的時候需要多執行幾遍,因為latch的等待時間非常短一閃而過,多抓幾遍就可以找到了。

session27

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的爭用。

session19

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_table2latch_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_table2latch_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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章