oracle反转索引查询,反转键索引模拟实验（reverse key index）

在zlhis系统中，一些数据表基于序列的索引是热块争用的重灾区，如病人医嘱记录_PK，病人信息_PK等，我们知道这种由序列产生的列一般是单调递增，其上的这些索引信息就会集中分布在同一个索引叶块，形成不对称的索引，因此导致该索引块容易产生争用。

ORACLE针对基于序列的索引的热块争用其实有一套非常可行的优化措施—反转键索引(reverse key index)，这是一种十分著名的索引，其在存储键值的时候，先将键值进行翻转。比如'1234'存储在索引中的键值是'4321'，如下图

本来要插入的索引值‘101’，‘102’，‘103’全部集中在A叶块，现在通过反转变成了‘101’，‘201’，‘301’后，就分别存储在了A，B，C三个索引叶块中，从而避免了热块集中在A点上，如果是在RAC环境中就可以避免由于对索引的修改集中在一小部分叶块上的性能下降 ，通过使索引的键值逆序排列，可以使插入操作分布在索引的全部叶块中，从而打散热点，避免热块争用。oracle官方文档对反转键索引的描述如下：

Reverse Key Indexes

Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.

Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed.

接着我们就以反转键索引做模拟实验，看看是否有对降低索引热点有效果，效果多少？

实验设计思路

在进行实验前我们先做点功课，了解怎么判断热块争用以及如何评估热块争用是否严重。

cache buffers chains锁存器

首先需要了解下cache buffers chains锁存器争用，当cache buffers chains等待事件出现时，意味着出现了cache buffers chains锁存器争用，通常情况下，有两种原因会出现cache buffers chains锁存器。

原因一. 低效率的SQL语句

在某些环境中，应用程序打开执行相同的低效率SQL语句的多个并发会话，这些SQL语句都设法得到相同的数据集。每次执行都带有高 BUFFER_GETS(逻辑读取)的SQL语句是主要的原因。

原因二：热块争用

当多个会话重复访问一个或多个受cache buffers chains锁存器保护的块时，就出现了热块争用，因此我们就是通过这一特性，来定位热块。

本例中的SQL相对比较简单，因此不存在复杂低效率SQL的情况，可以判断所有cache buffers chains都为热块争用导致，其可以通过动态视图V$LATCH_CHILDREN进行查询，其中sleeps 表示请求失败休眠的次数，通过sleeps我们可以大体知道数据库中latch的竞争是否严重，这也间接的表征了热点块的问题是否严重。

要确定导致热块争用的会话在X$BH视图中，其中接触点(touch count)来作为block是冷热的标志，那在短时间内从某种意义上讲，touch count 大的block可能暗示着在当前某个周期内被访问次数比较多。更多这方面的内容，可以参考Oracle官方文档《How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]》。

了解了判断热块争用的依据，接着搭建模拟环境，具体的实验设计思路如下：

1.创建 TEST_普通索引表(id)表，在ID列创建普通b-tree索引test_idx索引。

2.创建TEST_反转索引表(id)表，在ID列创建反转键索引test_idx_r索引。

3.按顺序插入指定量的数据。

4.创建个过程，更新数据表指定行数据，创建JOB，同一时间点通知执行该过程，保证更新的行在同一数据块上。

5.查询相关性能视图，查看热块争用情况。

实验步骤

1.创建测试表

Create Table TEST_反转索引表(Id Number(18));

Create Table TEST_普通索引表(Id Number(18));

2.创建索引

Create Index TEST_INDEX_R On TEST_反转索引表(Id) Reverse;

Create Index TEST_INDEX  On TEST_普通索引表(Id);

3.按顺序插入表中1000条记录，保证连续的数据存放在相同的数据块上。

insert into TEST_反转索引表  select rownum from dba_objects, dba_objects  where rownum<=1000000  and rownum<1000000   order by rownum;

insert into TEST_普通索引表  select rownum from dba_objects where rownum<=1000 order by rownum;

4.为了模拟索引的争用，我们创建一个过程，对指定的数据集中更新10万次，在对数据更新的时候，我们知道索引的操作是先删除旧的索引块，然后生成新的索引块。

create or replace procedure TEST_反转循环更新( p_in Number)  As

n Number;

Begin

For n In 1..100000 Loop

Update Test_反转索引表  Set  Id = p_in  where    Id = p_in;

Commit;

End Loop;

End;

同理创建更新普通索引表的过程。

create or replace procedure TEST_普通循环更新( p_in Number)  As

n Number;

Begin

For n In 1..100000 Loop

Update Test_普通索引表  Set  Id = p_in  where    Id = p_in;

Commit;

End Loop;

End;

5.分别创建6个作业执行调用前面的过程，并同时执行模拟并发操作，为了保证更新的数据在同一数据块上，我们这里连续的5条记录，注意在执行前保证job_queue_processes参数大于设置模拟并发的数量，否则会导致作业不能同时执行。

------生成5个并发操作

var job_no Number;

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(999995);');

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(999996);');

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(999997);');

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(999998);');

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(999999);');

Exec dbms_job.submit(:job_no,' TEST_普通循环更新(1000000);');

Commit;

同理，创建6个并发的作业，调用TEST_反转循环更新过程。

这时观察到系统的资源消耗，待执行完成后，根据《How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]》提供的SQL脚本，结合本实验的环境，最终整理了如下SQL定位热块对象。

Select /*+ RULE */

e.Owner || '.' || e.Segment_Name Segment_Name, sum(e.Extent_Id) Extent#, sum(x.Dbablk - e.Block_Id + 1) Block#, sum(x.Tch) As Tch

From Sys.V$latch_Children l, Sys.X$bh x, Sys.Dba_Extents e

Where x.Hladdr In (Select Distinct Saddr

From (Select Child# "cCHILD", Addr "SADDR", Gets "sGETS", Misses "sMISSES", Sleeps "sSLEEPS"From V$latch_ChildrenWhere Name = 'cache buffers chains' And Sleeps <> 0

Order By 5, 1, 2, 3)) And e.File_Id = x.File# And x.Hladdr = l.Addr And

x.Dbablk Between e.Block_Id And e.Block_Id + e.Blocks - 1 And e.Owner = 'SYS' And e.Segment_Name Like '%TEST%'

Group By  e.Owner || '.' || e.Segment_Name

Order By  sum(x.Tch) Des

实验结果

为了保证实验结果的可靠性，我们使用的新建立实例，而且在实验前，每次操作前都对数据库进行了重启，为的是保证v$latch_children动态性能视图的数据真实反应实验的数据。

通过前面的实验设计，我们分别模拟并发会话为1~6个会话下，统计TCH的次数，最终得到的实验结果如下图所示。

我们可以比较直观的看到TEST_INDEX普通索引上的接触点(touch count)数据比TEST_INDEX_R反转键索引大，而数据表的TCH却差不多，也就是说同样的操作普通索引上的热块争用比采用反转键索引严重，而且随着并发会话的增多，热块争用越发的明显，充分了证明了反转键索引在打散索引上的热点确实有效果。

结论

不过正如官方文档对反转键索引的描述一样，反转键索引虽然在分散热点上有其优势，但是其毕竟有局限性如无法使用索引区间扫描，这点也限制了其使用的范围，因此是否适合ZLHIS系统，需要根据应用程序设计综合评估。