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 /*+ index(t1,t1_inx) */* 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 不能正常扫描，强制走索引也是全索引扫描，代价比较大。