通过一个简单的测试,对比了一下唯一索引和非唯一索引在做等值谓词查询情况下,对于cache buffers chains latch和consistent gets两个指标的影响。
测试结论:
如果表上的列符合建立唯一索引的条件,那么为了避免cache buffers chains latch的争用,最好建立唯一索引。唯一索引不但可以减少cache buffers chains latch争用,还可以一定程度上减少逻辑读。
唯一索引在等值谓词查询时,根节点、分支节点、叶节点、数据块都只需要获得一次cache buffers chains latch(简称cbc latch),在cbc latch的保护下读取buffer。
大体步骤:
1)依据数据块的地址计算出数据块所在的Hash Bucket
2)获得保护这个Hash Bucket的cbc latch(共享模式获取)
3)在这个链表上找寻我们需要的数据块,找到后,步骤4)
4)读取数据块的内容
5)释放cbc latch
上面的读取操作在oracle里有一个单独的统计量来体现:consistent gets - examination,consistent gets - examination的值最终也会被汇总到consistent gets里。
非唯一索引在等值谓词查询时,根节点、分支节点都只需要获得一次cbc latch,叶节点、数据块都需要获得两次CBC LATCH。之所以根节点和分支节点只需要获得一次cbc latch,是因为oracle做了优化,根节点和分支节点的读取量要远远大于叶节点,非常可能会引起cbc latch的争用,因此oracle对于跟节点和分支节点的操作,都使用了获取共享cbc latch不加buffer pin的方式。
叶块和数据块的大体步骤:
1)依据数据块的地址计算出数据块所在的Hash Bucket
2)获得保护这个Hash Bucket的cbc latch(排他模式获取)
3)在这个链表上查找需要的数据块,找到后,pin这个buffer
4)释放cbc latch
5)在buffer pin的保护下读取/修改数据块的内容,读取完成后步骤6)
6)再次获取cbc latch(排他模式获取)
7)unpin这个buffer
8)释放cbc latch
上面的读取操作在oracle里有一个单独的统计量来体现:consistent gets。
由上面的步骤可以看出,唯一索引的代码路径比非唯一索引短,如果表上的列符合唯一索引的创建条件,建议创建唯一索引来最小化cbc latch可能产生的争用。
我们来看下具体的测试例子:
----创建测试表
create table test as select * from dba_ocache bjects;
场景一:非唯一索引下,cbc latch的持有次数
------在object_id上创建非唯一索引
create index tt on test(object_id);
execute snap_latch.start_snap
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
execute snap_latch.end_snap
---------------------------------
Latch waits:- 05-Aug 13:17:21
Interval:- 0 seconds
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
cache buffers chains 71 0 0 0 0 0 0 0 .0
上面的代码对一个同样的SQL做了10次查询。并且打印出了这期间会话cache buffers chains latch的统计量(增量)。
cache buffers chains的获取次数为71次。
我们上面已经提到,根节点,分支节点(这里分支节点和跟节点是同一个)的cbc latch每次只需持有一次。而叶节点和数据块节点,每次读取需要持有2次。
那么执行一次SQL,需要的cbc latch数为:
【根节点】1+【叶块】2+【数据块】2=5
执行十次:
5*10=50次
但是我们看到的cbc latch是持有了71次,多了21次,其实21次中的20次,是叶节点产生的,由于是非唯一索引,ORACLE从叶块取出第一个符合的值回表取出这条记录后,还需要再读取索引块一次,因为它不确认是不是下一个索引entry是不是也符合记录,但是唯一索引就没这个问题。通过逻辑读取数也可以验证这一点:
execute snap_my_stats.start_snap
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
execute snap_my_stats.end_snap
---------------------------------
Session stats - 05-Aug 13:25:07
Interval:- 0 seconds
---------------------------------
Name Value
---- -----
consistent gets 40
consistent gets from cache 40
consistent gets from cache (fastpath) 30
consistent gets - examination 10
40次逻辑读,每次SQL的逻辑读是4,4=1次根块+2次索引块+1次数据块
如果是唯一索引的话,只需要读取一次索引块就OK了。
场景二:唯一索引下,cbc latch的持有次数
------在object_id上创建非唯一索引
drop index tt;
create unique index tt on test(object_id);
-----执行代码,略
---------------------------------
Latch waits:- 05-Aug 13:27:11
Interval:- 0 seconds
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
cache buffers chains 31 0 0 0 0 0 0 0 .0
【根节点】1+【叶块】1+【数据块】1=3
执行十次,共30次的cbc latch获取。多余的一次为????
cbc latch的获取次数比上一个场景减少了71-31=40次。这个非常棒!
同理逻辑读取数:
---------------------------------
Session stats - 05-Aug 13:27:46
Interval:- 0 seconds
---------------------------------
Name Value
---- -----
consistent gets 30
consistent gets from cache 30
consistent gets - examination 30
一次SQL的逻辑读取数:
1次根块+1次索引块+1次数据块=3
10次执行的逻辑读就为:10*3=30
由于是唯一索引,所以查询一次叶块后,不需要再次回索引块做check。
逻辑读取数也比上一个场景少了40-30=10次。