Oracle聚簇因子导致回表逻辑读高
前言
最近优化SQL过程中，发现一个比较奇怪的现象。
通过索引回表之后的逻辑读远大于直接全表扫描的逻辑读。
16:47:39 SYS@test1(1315)> select count(*) from T_ABCDEFGG_HIJ_KLMN H where "H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03' AND "H"."CREATE_TIME"<SYSDATE-15; COUNT(*) ---------- 108757 Elapsed: 00:00:00.39 16:54:04 SYS@test1(1315)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- SQL_ID 85u2yk4xaydr2, child number 0 ------------------------------------- select count(*) from T_ABCDEFGG_HIJ_KLMN H where "H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03' AND "H"."CREATE_TIME"<SYSDATE-15 Plan hash value: 614647002 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.38 | 40357 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.38 | 40357 | |* 2 | TABLE ACCESS FULL| T_ABCDEFGG_HIJ_KLMN | 1 | 28967 | 108K|00:00:00.37 | 40357 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03' AND "H"."CREATE_TIME"<SYSDATE@!-15)) 21 rows selected. Elapsed: 00:00:00.33 16:54:05 SYS@test1(1315)> select /*+ index(H IDX_T_ABCDEFGG_HIJ_KLMN_01)*/ count(*) from T_ABCDEFGG_HIJ_KLMN H where "H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03' AND "H"."CREATE_TIME"<SYSDATE-15; COUNT(*) ---------- 108757 Elapsed: 00:00:01.13 16:54:15 SYS@test1(1315)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------- SQL_ID 6h43tgxns9up2, child number 0 ------------------------------------- select /*+ index(H IDX_T_ABCDEFGG_HIJ_KLMN_01)*/ count(*) from T_ABCDEFGG_HIJ_KLMN H where "H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03' AND "H"."CREATE_TIME"<SYSDATE-15 Plan hash value: 3917194736 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.12 | 264K| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.12 | 264K| |* 2 | TABLE ACCESS BY INDEX ROWID| T_ABCDEFGG_HIJ_KLMN | 1 | 28967 | 108K|00:00:01.12 | 264K| |* 3 | INDEX RANGE SCAN | IDX_T_ABCDEFGG_HIJ_KLMN_01 | 1 | 1013K| 1013K|00:00:00.18 | 2690 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("H"."IS_NRTX"='0' AND "H"."ADACMESH_TYPE"='03')) 3 - access("H"."CREATE_TIME"<SYSDATE@!-15) 23 rows selected. Elapsed: 00:00:00.04 16:59:03 SYS@test1(1315)> select segment_name,sum(bytes)/1024/1024 mb from dba_segments where owner='ABCDEFG_HIJKLMNO' and segment_name in ('T_ABCDEFGG_HIJ_KLMN','IDX_T_ABCDEFGG_HIJ_KLMN_01') group by segment_name; SEGMENT_NAME MB ----------------------------------- ---------- IDX_T_ABCDEFGG_HIJ_KLMN_01 25 T_ABCDEFGG_HIJ_KLMN 317 Elapsed: 00:00:00.15
从上边可以看出，强制走索引后，逻辑读达264k，即264000*8/1024/1024=2.014G。
但是实际上表和索引加起来的大小也才342M，这就很奇怪了。
探究
由于索引处的逻辑读为2690，在回表后的逻辑读变为264K，加上逻辑读（264K-2690）远大于实际表的大小，因此只有一个可能性。
那就是回表过程中某些块被重复多次读取导致的。
第一反应会以为回表读，已经读取过的块不会再次被读取，现在看来不是这么个情况。
网上百度的话其实找不到相关资料，从网上某些实验结果看，很容易让人误会就是不会再次读取已经被读取过的块。
MOS也无相关资料，那么只有自己构造环境来进行实验证明了。
环境构造
创建一张表tt并插入数据，注意插入数据需要用同一个会话运行。
create table tt (id int,name varchar2(2000)) tablespace users; insert into tt values(1,rpad('e',1804,'+')); insert into tt values(5,rpad('e',1804,'+')); insert into tt values(9,rpad('e',1804,'+')); insert into tt values(13,rpad('e',1804,'+')); insert into tt values(2,rpad('e',1804,'+')); insert into tt values(6,rpad('e',1804,'+')); insert into tt values(10,rpad('e',1804,'+')); insert into tt values(14,rpad('e',1804,'+')); insert into tt values(3,rpad('e',1804,'+')); insert into tt values(7,rpad('e',1804,'+')); insert into tt values(11,rpad('e',1804,'+')); insert into tt values(15,rpad('e',1804,'+')); insert into tt values(4,rpad('e',1804,'+')); insert into tt values(8,rpad('e',1804,'+')); insert into tt values(12,rpad('e',1804,'+')); insert into tt values(16,rpad('e',1804,'+'));
按照这样子，可以达到每4行数据在一个块，
08:44:59 SYS@zkm(51)> col id for a10 08:45:04 SYS@zkm(51)> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,wm_concat(id) id from tt group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid); FILE# BLOCK# ID ---------- ---------- ---------- 4 923 4,16,12,8 4 925 1,13,9,5 4 926 2,14,10,6 4 927 3,15,11,7 Elapsed: 00:00:00.00
即：
| 块号 | id值 | |||
| 925 | 1 | 5 | 9 | 13 |
| 926 | 2 | 6 | 10 | 14 |
| 927 | 3 | 7 | 11 | 15 |
| 923 | 4 | 8 | 12 | 16 |
在id列上创建索引，
08:48:15 SYS@zkm(51)> create index idx_id on tt(id); Index created. Elapsed: 00:00:00.01 08:48:17 SYS@zkm(51)> analyze table tt compute statistics; Table analyzed. Elapsed: 00:00:00.03
通过如下方式，更改id后的取值，统计处每次回表的逻辑读。
08:50:34 SYS@zkm(51)> set pagesize 9999 long 9999 line 500 timing on 08:50:34 SYS@zkm(51)> alter session set statistics_level=all; Session altered. Elapsed: 00:00:00.00 08:50:35 SYS@zkm(51)> select /*+ index(tt idx_id) */ count(name) from tt where id<=1; COUNT(NAME) ----------- 1 Elapsed: 00:00:00.00 08:50:38 SYS@zkm(51)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 1vb1jwbm5fuv9, child number 0 ------------------------------------- select /*+ index(tt idx_id) */ count(name) from tt where id<=1 Plan hash value: 2637792774 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | 2 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 1 | 1 |00:00:00.01 | 2 | |* 3 | INDEX RANGE SCAN | IDX_ID | 1 | 1 | 1 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<=1) 20 rows selected. Elapsed: 00:00:00.01
其中，id3访问索引的逻辑读为1，因此回表的逻辑读为2-1=1。
同样的方式，依次统计从id<=1到id<=16。
需要说明一点的是，由于数据分布的特点，id<=1是，访问925号块，id<=2则访问925,926号块，id<=3对应925,926,927，id<=4对应925,926,927,923，
而当id<=5的时候，由于id=1和id=5都在925块上，因此从这开始看后续是否逻辑读增加即可。
| id<=n | 回表逻辑读 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
从统计结果看，由于925,926,927,923这四个块在条件id<=16处各被访问了4次。
这是由于数据分布特点导致的，即聚簇因子。
如若数据分布如下，那么在id<=16的时候回表逻辑读只会产生4次。
| 块号 | id值 | |||
| 925 | 1 | 2 | 3 | 4 |
| 926 | 5 | 6 | 7 | 8 |
| 927 | 9 | 10 | 11 | 12 |
| 923 | 13 | 14 | 15 | 16 |
create table tt (id int,name varchar2(2000)) tablespace users; insert into tt values(1,rpad('e',1804,'+')); insert into tt values(2,rpad('e',1804,'+')); insert into tt values(3,rpad('e',1804,'+')); insert into tt values(4,rpad('e',1804,'+')); insert into tt values(5,rpad('e',1804,'+')); insert into tt values(6,rpad('e',1804,'+')); insert into tt values(7,rpad('e',1804,'+')); insert into tt values(8,rpad('e',1804,'+')); insert into tt values(9,rpad('e',1804,'+')); insert into tt values(10,rpad('e',1804,'+')); insert into tt values(11,rpad('e',1804,'+')); insert into tt values(12,rpad('e',1804,'+')); insert into tt values(13,rpad('e',1804,'+')); insert into tt values(14,rpad('e',1804,'+')); insert into tt values(15,rpad('e',1804,'+')); insert into tt values(16,rpad('e',1804,'+'));
09:15:00 SYS@zkm(51)> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid),wm_concat(id) from tt group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid); FILE# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) WM_CONCAT(ID) ---------- ------------------------------------ -------------------------------------------------------------------------------- 4 923 13,16,15,14 4 925 1,4,3,2 4 926 5,8,7,6 4 927 9,12,11,10 Elapsed: 00:00:00.01 09:15:04 SYS@zkm(51)> select /*+ index(tt idx_id) */ count(name) from tt where id<=16; COUNT(NAME) ----------- 16 Elapsed: 00:00:00.00 09:15:11 SYS@zkm(51)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 90x3ztbc6cd7b, child number 0 ------------------------------------- select /*+ index(tt idx_id) */ count(name) from tt where id<=16 Plan hash value: 2637792774 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 5 | | 2 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 16 | 16 |00:00:00.01 | 5 | |* 3 | INDEX RANGE SCAN | IDX_ID | 1 | 16 | 16 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<=16) 20 rows selected. Elapsed: 00:00:00.00
因此可以得出结论，在回表时，根据rowid访问对应块的时候，如果此次访问块和上次不同，则会产生新的逻辑读。
如果一样，则不会产生新的逻辑读（实际上被合并成一次取数据了）。
比如前边的当id=1,2,3,4都在925号块的时候，id<=4回表就只会有一次逻辑读。
因此，一开始前言处的问题应该可以从聚簇因子看出，是因为数据分布（索引列）太过于散乱引起的。
从下边数据看出，聚簇因子CF远大于表T_ABCDEFGG_HIJ_KLMN的块数。
09:23:58 SYS@test1(595)> select CLUSTERING_FACTOR from dba_indexes where owner='ABCDEFG_HIJKLMNO' and index_name='IDX_T_ABCDEFGG_HIJ_KLMN_01'; CLUSTERING_FACTOR ----------------- 264830 Elapsed: 00:00:00.00 09:25:20 SYS@test1(595)> select blocks from dba_tables where owner='ABCDEFG_HIJKLMNO' and table_name='T_ABCDEFGG_HIJ_KLMN'; BLOCKS ---------- 40320 Elapsed: 00:00:00.04