latch -buffer busy wait 之索引資料塊争用導緻熱塊

oracle資料庫會自動為主鍵列建立索引。索引或遞增或遞減（類似序列）結構為b-tree索引，這些鍵值比較接近，存儲在資料塊上也比較靠近

随着資料量的增大b-tree索引的層級也不斷增大。

在rac環境中，使用者從不同執行個體向表中插入主鍵時，有相同索引資料塊在不同執行個體記憶體中被調用，形成一種資料塊的争用。

如資料塊1被使用，其上存儲了主鍵鍵值為1，2，3，等行資訊，這樣通路1個塊導緻其他行不能被其他執行個體通路 

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index t_inx on t (object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> create table t1 as select rownum id from dba_objects;

Table created.

SQL> create index t1_inx on t1 (id) reverse;

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp;

SQL> select * from t where object_id>12340 and object_id<12350;

Execution Plan

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

Plan hash value: 1579008347

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

-----

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

    |

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

-----

|   0 | SELECT STATEMENT            |       |    10 |   930 |     3   (0)| 00:00

:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    10 |   930 |     3   (0)| 00:00

:01 |

|*  2 |   INDEX RANGE SCAN          | T_INX |    10 |       |     2   (0)| 00:00

:01 |

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

-----

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID">12340 AND "OBJECT_ID"<12350)

SQL> select * from t1 where id>12340 and id<12350;

Execution Plan

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

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      |    10 |    40 |    19   (6)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |    10 |    40 |    19   (6)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"<12350 AND "ID">12340)

SQL> select * from t1 where id>12340 and id<12350;

Execution Plan

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

Plan hash value: 3995001570

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

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

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

|   0 | SELECT STATEMENT |        |    10 |    40 |   113   (2)| 00:00:02 |

|*  1 |  INDEX FULL SCAN | T1_INX |    10 |    40 |   113   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"<12350 AND "ID">12340)

反向索引有很大局限性，隻适合rac這種環境，畢竟index range scan 不能正常掃描，強制走索引也是全索引掃描，代價比較大。