轉載並整理

熱點塊的定義

資料庫的熱點塊，從簡單了講，就是極短的時間內對少量資料塊進行了過於頻繁的訪問。定義看起來總是很簡單的，但實際在資料庫中，我們要去觀察或者確定熱點塊的問題，卻不是那麼簡單了。要深刻地理解資料庫是怎麼透過一些資料特徵來表示熱點塊的，我們需要了解一些資料庫在這方面處理機制的特性。

資料緩衝區的結構

我們都知道，當查詢開始的時候，程式首先去資料緩衝區中查詢是否存在查詢所需要的資料塊，如果沒有，就去磁碟上把資料塊讀到記憶體中來。在這個過程中，涉及到資料緩衝區中LRU鏈的管理（8i開始以接觸點計數為標準衡量buffer冷熱從而決定buffer是在LRU的冷端還是熱端），關於這部分內容，從oracle concepts 中就能得到詳盡的文件，我不準備去論述這部分內容，這也不是本文的重點。現在我們的重點是，到底程式是如何地去快速定位到自己所想要的block的，或者如何快速確定想要的block不在記憶體中而去進行物理讀的。

我們仔細想一想，隨著硬體的發展，記憶體越來越大，cache buffer也越來越大，我們如何才能在大量的記憶體中迅速定位到自己想要的block？總不能去所有buffer中遍歷吧！在此資料庫引出了hash的概念（oracle中快速定位資訊總是透過hash演算法的，比如快速定位sql是否在shared pool size中存在就是透過hash value來定位的，也就是說shared pool size中物件也是透過hash table來管理的），瞭解一點資料結構的基本知識就知道，hash 的一大重要功能就是快速地查詢。舉個最簡單的例子，假設我們有一個hash table 就是一個二維陣列a[200][100],現在有1000個無序數字，我們要從這1000個數字裡面查詢某個值是否存在，或者說當我們接收到某個數字的時候必須判斷是否已經存在，當然，我們可以遍歷這1000個數字，但這樣的效率就很低。但現在我們考慮這樣一種方法，那就是把1000個數字除以200，根據其餘數，放在a[200][100]裡面(假設相同餘數的最大數量不超過100)，餘數就是陣列的下標。這樣，平均來說一個陣列a[i]裡面可能有5個左右的數字。當我們要去判別一個數字是否存在的時候，對這個數字除以200(這就是一個最簡單的hash演算法)，根據餘數i作為下標去陣列a[i]中查詢，大約進行5次查詢就能判別是否已經存在，這樣透過開闢記憶體空間a[200][100]來換取了時間(當然hash 演算法的選取和hash table的大小是一個很關鍵的問題)。

明白了基本的hash原理之後，我們再來看oracle的block的管理。資料庫為這些block也開闢了hash table，假設是a,則在一維上的數量是由引數_db_block_hash_buckets 來決定的，也就是存在hash table a[_db_block_hash_buckets ],從oracle8i開始，_db_block_hash_buckets =db_block_buffers*2。而一個block被放到哪個buckets裡面，則是由block的檔案編號、塊號(x$bh.dbarfl、x$bh.dbablk對應了block的檔案屬於表空間中的相關編號和block在檔案中的編號，x$bh是所有cache buffer的header資訊，透過表格的形式可以查詢)做hash 演算法決定放到哪個bucket的，而bucket裡面就存放了這些buffers的地址。這樣當我們要訪問資料的時候，可以獲得segment的extent(可以透過dba_extents查到看，詳細的資訊來源這裡不做探討)，自然知道要訪問的檔案編號和block編號，根據檔案和block編號可以透過hash演算法計算出hash bucket,然後就可以去hash bucket裡面去找block對應的buffer。

除此之外，為了維護對這些block的訪問和更改，oracle還提供了一種latch來保護這些block。因為要避免不同的程式隨意地徑直併發修改和訪問這些block，這樣很可能會破壞block的結構的。latch是資料庫內部提供的一種維護內部結構的一種低階鎖，latch的生存週期極短(微秒以下級別)，程式加latch後快速的進行某個訪問或者修改動作然後釋放latch(關於latch不再過多的闡述，那可能又是需要另一篇文章才能闡述清楚)。這種latch數量是透過引數_db_block_hash_latches 來定義的，一個latch對應的保護了多個buckets。從8i開始，這個引數的default規則為:

當cache buffers 少於2052 buffers

_db_block_hash_latches = power(2,trunc(log(2, db_block_buffers - 4) - 1))

當cache buffers多於131075 buffers

_db_block_hash_latches = power(2,trunc(log(2, db_block_buffers - 4) - 6))

當cache buffers位於2052與131075 buffers之間

_db_block_hash_latches = 1024

透過這個規則我們可以看出，一個latch大約可以維護128個左右的buffers。由於latch使得對block的操作的序列化(9i中有改進，讀與讀可以並行，但讀與寫、寫與寫依然要序列)，很顯然我們可以想到一個道理，如果大量程式對相同的block程式進行操作，必然在這些latch上造成競爭，也就是說必然形成latch的等待。這在宏觀上就表現為系統級的等待。明白了這些原理，為我們下面的在資料庫中的診斷奠定了基礎

如何確定熱點物件

如果我們經常關注statspack報告，會發現有時候出現cache buffer chains的等待。這個cache buffer chains就是_db_block_hash_latches所定義的latch的總稱，透過查詢v$latch也可得到：

select latch#,name,gets,misses,sleeps

from v$latch

where name like 'cache buffer%';

LATCH#       NAME                           GETS        MISSES      SLEEPS
---------- ------------------------------ ---------- ---------- ----------
93          cache buffers lru chain         54360446    21025       238
98          cache buffers chains            6760354603 1680007     27085
99          cache buffer handles            554532      6           0

在這個查詢結果裡我們可以看到記錄了資料庫啟動以來的所有cahce buffer chains的latch的狀況，gets表示總共有這麼多次請求，misses表示請求失敗的次數(加鎖不成功)，而sleeps 表示請求失敗休眠的次數，透過sleeps我們可以大體知道資料庫中latch的競爭是否嚴重，這也間接的表徵了熱點塊的問題是否嚴重。由於v$latch是一個聚合資訊，我們並不能獲得哪些塊可能存在頻繁訪問。那我們要來看另一個view資訊，那就是v$latch_children,v$latch_children.addr記錄的就是這個latch的地址。

select addr,LATCH#,CHILD#,gets,misses,sleeps

from v$latch_children
where name = 'cache buffers chains'

and rownum < 21;

ADDR      LATCH#      CHILD#      GETS        MISSES      SLEEPS
-------- ---------- ---------- ---------- ---------- ----------
91B23B74 98          1024        10365583    3957        33
91B23374 98          1023        5458174     964         25
91B22B74 98          1022        4855668     868         15
91B22374 98          1021        5767706     923         22
91B21B74 98          1020        5607116     934         31
91B21374 98          1019        9389325     1111        25
91B20B74 98          1018        5060207     994         31
91B20374 98          1017        18204581    1145        18
91B1FB74 98          1016        7157081     920         23
91B1F374 98          1015        4660774     922         22
91B1EB74 98          1014        6954644     976         32
91B1E374 98          1013        4881891     970         19
91B1DB74 98          1012        5371135     971         28
91B1D374 98          1011        5154497     990         26
91B1CB74 98          1010        5013796     936         18
91B1C374 98          1009        5667446     939         25
91B1BB74 98          1008        4673421     883         14
91B1B374 98          1007        4589646     986         17
91B1AB74 98          1006        10380781    1020        20
91B1A374 98          1005        5142009     1110        19

20 rows selected.

到此我們可以根據v$latch_child.addr關聯到對應的x$bh.hladdr(這是buffer header中記錄的當前buffer所處的latch地址)，透過x$bh可以獲得塊的檔案編號和block編號。

select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
      from v$latch_children
      order by sleeps desc)
      where rownum < 11);

DBARFIL     DBABLK
---------- ----------
4           6498
40          14915
15          65564
28          34909
40          17987
1           24554
8           21404
39          29669
28          46173
28          48221

……………………

由此我們就打通了cache buffers chains和具體block之間的關係，那再繼續下來，知道了block，我們需要知道究竟是哪些segment。這個可以透過dba_extents來獲得。

select distinct a.owner,a.segment_name

from dba_extents a,
     (select dbarfil,dbablk
     from x$bh
     where hladdr in
       (select addr
        from (select addr
             from v$latch_children
             order by sleeps desc)
       where rownum < 11)

) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk

and a.block_id + a.blocks > b.dbablk;

OWNER                           SEGMENT_NAME                    SEGMENT_TYPE
---------------------------- ------------------------------ ------------------
ALIBABA                         BIZ_SEARCHER                    TABLE
ALIBABA                         CMNTY_USER_MESSAGE              TABLE
ALIBABA                         CMNTY_VISITOR_INFO_PK           INDEX
ALIBABA                         COMPANY_AMID_IND                INDEX
ALIBABA                         COMPANY_DRAFT                   TABLE
ALIBABA                         FEEDBACK_POST                   TABLE
ALIBABA                         IM_BLACKLIST_PK                 INDEX
ALIBABA                         IM_GROUP                        TABLE
ALIBABA                         IM_GROUP_LID_IND                INDEX
ALIBABA                         MEMBER                          TABLE
ALIBABA                         MEMBER_PK                       INDEX
ALIBABA                         MLOG$_SAMPLE                    TABLE

……………………

由此我們就打通了cache buffers chains和具體block之間的關係，那再繼續下來，知道了block，我們需要知道究竟是哪些segment。這個可以透過dba_extents來獲得。

select distinct a.owner,a.segment_name

from dba_extents a,
     (select dbarfil,dbablk
     from x$bh
     where hladdr in
       (select addr
        from (select addr
             from v$latch_children
             order by sleeps desc)
       where rownum < 11)

) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk

and a.block_id + a.blocks > b.dbablk;

OWNER                           SEGMENT_NAME                    SEGMENT_TYPE
---------------------------- ------------------------------ ------------------
ALIBABA                         BIZ_SEARCHER                    TABLE
ALIBABA                         CMNTY_USER_MESSAGE              TABLE
ALIBABA                         CMNTY_VISITOR_INFO_PK           INDEX
ALIBABA                         COMPANY_AMID_IND                INDEX
ALIBABA                         COMPANY_DRAFT                   TABLE
ALIBABA                         FEEDBACK_POST                   TABLE
ALIBABA                         IM_BLACKLIST_PK                 INDEX
ALIBABA                         IM_GROUP                        TABLE
ALIBABA                         IM_GROUP_LID_IND                INDEX
ALIBABA                         MEMBER                          TABLE
ALIBABA                         MEMBER_PK                       INDEX
ALIBABA                         MLOG$_SAMPLE                    TABLE

我們還有另外一種方式

select object_name
from dba_objects
where data_object_id in
      (select obj
       from x$bh
       where hladdr in
            (select addr
             from (select addr
                   from v$latch_children
                   order by sleeps desc)
             where rownum < 11

)

;

OBJECT_NAME
------------------------------------
I_CCOL2
RESOURCE_PLAN$
DUAL
FGA_LOG$
AV_TRANSACTION
COMPANY_DRAFT
MEMBER
SAMPLE
SAMPLE_GROUP
VERTICAL_COMPONENT
MEMBER_PK
SAMPLE_GROUP_PK
IM_BLACKLIST_PK
IM_CONTACT
IM_GROUP
CMNTY_USER_MESSAGE
CMNTY_VISITOR_INFO_PK
IM_OFFLINEMSG_TID_IND
OFFER
OFFER_PK
OFFER_EMAIL_IND
OFFER_DRAFT
CMNTY_USER_MESSAGE_TD_BSM_IND
CMNTY_MESSAGE_NUM_PK
BIZ_EXPRESS_MEMBER_ID_IND

……………………

到這裡我們基本能找到熱點塊對對應的物件。但實際上還有另外一個途徑來獲取這些資訊，那就是和x$bh.tch 相關的一種方法。對於8i開始oracle提供了接觸點(touch count)來作為block是冷熱的標誌，在一定條件滿足的情況下block被程式訪問一次touch count 增加一，到某個標準之後被移動到LRU熱端(關於touch count 在這裡不做詳細介紹，那又將是一大篇文章)。那在短時間內從某種意義上講，touch count 大的block可能暗示著在當前某個週期內被訪問次數比較多。

select distinct a.owner,a.segment_name,a.segment_type

from dba_extents a,
      (select dbarfil,dbablk
       from (select dbarfil,dbablk
             from x$bh order by tch desc)

where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk

and a.block_id + a.blocks > b.dbablk;

OWNER                           SEGMENT_NAME                    SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
ALIBABA                         CMNTY_USER_MESSAGE              TABLE
ALIBABA                         MEMBER_PK                       INDEX
ALIBABA                         OFFER_DRAFT_GMDFY_IND           INDEX

同上面一樣還有這個方法

select object_name
from dba_objects
where data_object_id in
       (select obj
        from (select obj
              from x$bh order by tch desc)

where rownum < 11) ;

OBJECT_NAME
---------------------------------------------------
DUAL
MEMBER_PK
SAMPLE_GROUP_PK
CMNTY_USER_MESSAGE_TD_BSM_IND
OFFER_DRAFT_MID_GMDFY_IND
OFFER_MID_GPOST_IND
OFFER_DRAFT_PK
MEMBER_GLLOGIN_IND
OFFER_MID_STAT_GEXPIRE_IND
SAMPLE_MID_STAT_IND

10 rows selected.

到這裡，我們尋找熱點塊和熱點物件的工作算是完成了，但我們還並沒有解決問題。

熱點問題的解決

熱點塊和熱點物件我們都找到了，但是我們該怎麼來解決這個問題呢？一般來說，熱點塊會導致cache buffers chains競爭等待，但並不是說cache buffer chains一定是因為熱點塊而起，在特別情況下有可能是因為latch數量的問題導致的，也就是一個latch管理的buffers數量太多而導致競爭激烈。但是latch數量我們一般是不會輕易去設定的，這是oracle的隱藏引數。

實際上最有效的辦法，是從最佳化sql入手，不良的sql往往帶來大量的不必要的訪問，這是造成熱點塊的根源。比如本該透過全表掃描的查詢卻走了索引的range scan，這樣將帶來大量的對塊的重複訪問。從而形成熱點問題。再或者比如不當地走了nested loops的表連線，也可能對非驅動表造成大量的重複訪問。那麼在這個時候，我們的目標就是找出這些sql來並嘗試最佳化。在statspack報告中，根據報告中sql列表，我們如果是透過dba_extents確定的熱點物件而不是透過dba_objects確定的，則可以透過查詢出的熱點segment轉換為對應的表，對於非分割槽的索引，index_name就是segment_name,透過dba_indexes很容易的找到對應的table_name,對於分割槽表和分割槽索引也能透過和dba_tab_partition和dba_ind_partitions找到segment和table的對應關係。透過這些table到statspack報告中去找相關的sql。

select sql_text
from stats$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type

       from dba_extents a,
           (select dbarfil,dbablk
            from (select dbarfil,dbablk
                  from x$bh order by tch desc)

            where rownum < 11) b
       where a.RELATIVE_FNO = b.dbarfil
       and a.BLOCK_ID <= b.dbablk

and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
order by a.hash_value,a.address,a.piece;

SQL_TEXT
----------------------------------------------------------------
SELECT SEQ_SMS_TRANSACTION.nextval FROM DUAL
SELECT SEQ_BIZ_EXPRESS.nextval FROM DUAL
SELECT bizgroup.seq_grp_post.NextVal FROM DUAL
SELECT SEQ_SAMPLE.nextval FROM DUAL
SELECT bizgroup.seq_grp_user.NextVal FROM DUAL
SELECT SEQ_BIZ_SEARCHER.nextval FROM DUAL
SELECT SEQ_OFFER_DRAFT.nextval FROM DUAL
select seq_Company_Draft.NextVal from DUAL
SELECT SEQ_SAMPLE_GROUP.nextval FROM DUAL
SELECT SEQ_CMNTY_USER_MESSAGE.nextval FROM DUAL
SELECT SYSDATE FROM DUAL
select seq_News_Forum.NextVal from DUAL
SELECT SEQ_SMS_USER.nextval FROM DUAL
select seq_Biz_Member.NextVal from DUAL
select seq_Pymt_Managing.NextVal from DUAL
E= '+08:00' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSX
SELECT SEQ_COMPANY_DRAFT.nextval FROM DUAL
SELECT 1 FROM DUAL
select seq_offer_draft.NextVal from DUAL
select seq_Biz_Express_Category.NextVal from DUAL

20 rows selected.

除了最佳化sql外，當然對於熱點的表或者索引來說，如果小的話，我們可以考慮cache在記憶體中，這樣可能降低物理讀提高sql執行速度(這並不會減少cache buffer chains的訪問次數)，對於序列，我們可以對序列多設定一些cache。如果是並行伺服器環境中的索引物件，並且這個索引是系列遞增型別，我們可以考慮反向索引(關於反向索引這裡就不過多地做介紹了)。

<SCRIPT. language=javascript. <!-- var hstr="/xingyundaocao/brwstat?key1=1"; document.write(" </SCRIPT. </SCRIPT.

熱點塊競爭和解決--cache buffers chains

相關文章