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

leonarding發表於2012-12-09

引言:索引的熱塊其實和資料塊的熱塊發生的原理大相徑庭,也都是因為大量會話一起訪問同一個索引塊造成的,我們的解決方案有反向索引,分割槽索引等。我們說任何一種方式都不是完美的,有優點就必然有缺點,我們把包含索引鍵值的索引塊從順序排列打散到無序排列,降低了latch爭用,同時也增加了oracle掃描塊的數量。我們在實際使用時多測試取長補短,以提高系統的整體效能為目標。


LEO1@LEO1>create table leo1 (id  number , name  varchar2(200));     建立了一個leo1

Table created.

LEO1@LEO1>insert into leo1 (id,name) select object_id,object_name from dba_objects; dba_objects2個欄位複製到leo1表中。

71966 rowscreated.

LEO1@LEO1>select id,name from leo1 where rownum<10;   好已經完成

        ID NAME

----------------------------------------------------

       673 CDC_CHANGE_SOURCES$

       674 I_CDC_CHANGE_SOURCES$

       675 CDC_CHANGE_SETS$

       676 I_CDC_CHANGE_SETS$

       677 CDC_CHANGE_TABLES$

       678 I_CDC_CHANGE_TABLES$

       679 CDC_SUBSCRIBERS$

       680 I_CDC_SUBSCRIBERS$

       681 CDC_SUBSCRIBED_TABLES$

LEO1@LEO1>create index leo1_index on leo1(id);     leo1表上id列建立一個索引

Index created.

LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true);  對錶和索引一起做一個分析,cascade=>true 指的是級聯表上的索引一起做分析

PL/SQL proceduresuccessfully completed.

LEO1@LEO1>create table leo2 (id number,name varchar2(200));      建立leo2

Table created.

LEO1@LEO1>insert into leo2 (id,name) select object_id,object_name from dba_objects;  插入71968

71968 rowscreated.

為什麼比leo1表多了2行呢,就是多了leo1leo1_index2個物件,我們剛剛建的。

LEO1@LEO1>create index leo2_index on leo2(id) reverse;        建立一個反向索引

Index created.

LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true);  做分析

PL/SQL proceduresuccessfully completed.

LEO1@LEO1>select index_name,index_type,table_name,status from dba_indexes wheretable_name in ('LEO1','LEO2');

INDEX_NAME   INDEX_TYPE      TABLE_NAME      STATUS

--------------------------------------------------------- ------------------------------ --------

LEO1_INDEX    NORMAL                LEO1           VALID

LEO2_INDEX    NORMAL/REV       LEO2           VALID  

LEO2_INDEX   是反向索引,我們使用它來把順序的索引塊反向成無序索引塊儲存,這樣我們在查詢一個區間範圍時,索引鍵值就會落在不連續的索引塊上,防止熱塊的產生,降低“latch 連結串列”爭用。這可能算是反向索引唯一被使用的情況。因為反向索引不支援index range scan功能,只支援index full scan 全索引掃描,如何理解呢,舉個簡單的例子 反向索引 不能幫你檢索出  id> 1 and id < 10 的行,但可以幫你檢索出 id=10的行,也就是說對範圍掃描效率低,等值掃描效率還是很高的。

LEO1@LEO1> set   autotrace  on;       啟動執行計劃

LEO1@LEO1>select count(*)  from leo1 whereid<100;     這是B-TREE索引執行計劃

  COUNT(*)

----------

        98

Execution Plan

----------------------------------------------------------

Plan hash value:423232053

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     1 |     5 |    2   (0)| 00:00:01 |

|   1 | SORT AGGREGATE   |           |     1|     5 |            |          |

|*  2 |   INDEX RANGE SCAN| LEO1_INDEX |    96 |  480 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

索引範圍掃描,因為我們查詢索引鍵值都是存放在連續的索引塊中,所以只有僅僅的2個一致性讀,它只掃描符合條件的索引塊就能找到相應的記錄。

PredicateInformation (identified by operation id):

---------------------------------------------------

   2 - access("ID"<100)

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

          2  consistent gets

          0 physical reads

          0 redo size

        526 bytes sent via SQL*Net to client

        523 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

LEO1@LEO1>select count(*)  from leo2 whereid<100;    反向索引執行計劃

  COUNT(*)

----------

        98

Execution Plan

----------------------------------------------------------

Plan hash value:1710468575

------------------------------------------------------------------------------------

| Id  | Operation             | Name       | Rows | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |            |     1 |    5 |    45   (0)| 00:00:01 |

|   1 | SORT AGGREGATE       |            |     1 |    5 |            |          |

|*  2 |   INDEX FAST FULL SCAN| LEO2_INDEX |    96 |  480 |    45   (0)| 00:00:01 |

------------------------------------------------------------------------------------

快速全索引掃描,因為我們查詢索引鍵值在反向索引中是存放在不連續的索引塊上,由於索引鍵值在磁碟物理塊位置上的無序,因此只能執行全索引掃描,即所有的索引塊全掃一遍抽取符合條件的記錄出來,從這裡就可以看出檢索相同行數,全索引掃描執行計劃要比索引範圍掃描執行計劃多掃了84倍的塊,那麼反過來看“latch 爭用”的機率小了84倍。

PredicateInformation (identified by operation id):

---------------------------------------------------

   2 - filter("ID"<100)

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

        168  consistent gets

          0 physical reads

          0 redo size

        526 bytes sent via SQL*Net to client

        523 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed


下面我寫個儲存過程,作用呢就是透過索引頻繁的訪問表中的記錄,當有多個會話一起執行時看看有沒有發生爭用

儲存過程

LEO1@LEO1>create or replace procedure p10

as

      l number;

begin

      for i in 1..50000

      loop

         select count(*) into l from leo1 whereid<10000;

      end loop;

      dbms_output.put_line('successfully');

end;

/

  2   3    4    5   6    7    8   9   10   11  

Procedure created.

三個會話同時反覆訪問表leo1

session19

LEO1@LEO1>execute p10;

successfully

PL/SQL proceduresuccessfully completed.

session147

LEO1@LEO1>execute p10;

successfully

PL/SQL procedure successfullycompleted.

session148

LEO1@LEO1>execute p10;

successfully

PL/SQL proceduresuccessfully completed.

session144

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 latch: cache buffers chains

       148 latch: cache buffers chains

       147 latch: cache buffers chains

從會話等待事件中出現了“latch 連結串列”爭用,在你操作的過程中可能執行一次並沒有顯示,因為latch等待非常快就結束了多多執行幾次就能看出效果。同理訪問leo2表的時候可能碰巧也會發現latch等待,由於資料分佈的比較廣,因此你碰到的機率就很小很小。


(146.71 KB, 下載次數: 0)


2012.12.08
天津&winter
分享技術~成就夢想
Blog

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26686207/viewspace-750801/,如需轉載,請註明出處,否則將追究法律責任。

相關文章