今天我们主要介绍下Oracle在12c收集统计信息的时候推出的新选项“TABLE_CACHED_BLOCKS”。该选项挺有意思的。在介绍这个知识点之前,我们先来看一下聚簇因子的知识。
一张表,如果我们按照索引排序来扫描。如果第一行排序rowid指向的块和第二行排序rowid指向的块相同,则聚簇因子不会增加,如果第二行排序的rowid指向了另外一个数据块,则聚簇因子会+1。因此,索引和列的排序应该使聚簇因子足够低。它的值越低,使用索引排序的效率也就越高。因为按照顺序可以扫描更少的块来检索到数据。
下面我们来建立一张表演示一下。该表有2个列,其中列RNUM_UNQ是唯一值,数字1-10000,每一行都是唯一的值,而列RANDOM_NUMBER是非唯一值,选取的范围是1-100,随机重复。
create table s1 as with a1 as (select * from all_objects where rownum between 1 and 10000) select rownum rnum_unq, round(dbms_random.value(1,100),0) random_number from a1 a, a1 b where rownum between 1 and 10000; SQL> select * from s1 where rownum<=10; RNUM_UNQ RANDOM_NUMBER ---------- ------------- 1 98 2 84 3 96 4 98 5 43 6 82 7 61 8 99 9 13 10 64 SQL> exec dbms_stats.gather_table_stats('U1','S1'); PL/SQL procedure successfully completed. SQL> select owner, num_rows, blocks from dba_tables where table_name='S1'; OWNER NUM_ROWS BLOCKS -------------------------------------------------- ---------- ---------- U1 10000 21
总共21个block。接下来我们创建四个索引,第一个是以RNUM_UNQ创建的索引,里面的值是唯一的。第二个是以rnum_unq,RANDOM_NUMBER创建的索引。前导列是唯一值。第三个是以RANDOM_NUMBER创建的索引,是非唯一的,很多重复的值。第四个索引是以RANDOM_NUMBER,rnum_unq创建的索引,前导列是非唯一值。
create index s1_rnum on s1(rnum_unq); create index s1_rnum_random on s1(rnum_unq, RANDOM_NUMBER); create index s1_random on s1(RANDOM_NUMBER); create index s1_random_rnum on s1(RANDOM_NUMBER, rnum_unq); SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor 2 from dba_indexes 3 where table_name='S1'; INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR ----------------------------------- ---------- ---------- ----------- ------------- ----------------- S1_RNUM 10000 1 21 10000 17 S1_RNUM_RANDOM 10000 1 26 10000 17 S1_RANDOM 10000 1 20 100 1695 S1_RANDOM_RNUM 10000 1 26 10000 1695
这里可以看到唯一值,S1_RNUM的聚簇因子是17,而S1_RANDOM,非唯一值的聚簇因子是1695。而前导列是唯一值的也是17。非唯一值的是1695。那么这个是怎么计算出来的呢?
我们来看一下怎么计算的,我们先按照RANDOM_NUMBER,RNUM_UNQ来排序,获取它每一行的rowid对应的block。结果如下:
select dbms_rowid.rowid_block_number(rowid)||'.'||dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,RANDOM_NUMBER, RNUM_UNQ from s1 order by RANDOM_NUMBER, RNUM_UNQ, block_fno; BLOCK_FNO RANDOM_NUMBER RNUM_UNQ --------------- ------------- ---------- 11385.14 100 8233 11385.14 100 8254 11385.14 100 8309 11385.14 100 8342 11386.14 100 8459 11386.14 100 8886 11386.14 100 9002 11387.14 100 9449 11387.14 100 9458 11387.14 100 9633
有了这个结果之后,我们就知道块和索引排序情况的对应了。接下来我们需要计算每一行前面的一个块号,使用lag函数。
select block_fno,RANDOM_NUMBER, RNUM_UNQ, lag(block_fno) over (order by RANDOM_NUMBER, rnum_unq) prev_bfno from ( select dbms_rowid.rowid_block_number(rowid)||'.'||dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,RANDOM_NUMBER, RNUM_UNQ from s1 order by RANDOM_NUMBER, RNUM_UNQ, block_fno ) BLOCK_FNO RANDOM_NUMBER RNUM_UNQ PREV_BFNO ------------ ------------- ---------- -------------- 11385.14 100 8233 11385.14 11385.14 100 8254 11385.14 11385.14 100 8309 11385.14 11385.14 100 8342 11385.14 11386.14 100 8459 11385.14 11386.14 100 8886 11386.14 11386.14 100 9002 11386.14 11387.14 100 9449 11386.14 11387.14 100 9458 11387.14 11387.14 100 9633 11387.14
这里可以发现,100,9449这个是11387块。而如果查询它前面的一行,它的块就是11386块。那么接下来我们用case when来判断,按照RANDOM_NUMBER, RNUM_UNQ这个顺序来。它的每一行和它前面的一行是在一个块内吗,如果是那就是连续的,如果不是那就不是连续的。最终我们统计了一下,按照RANDOM_NUMBER, RNUM_UNQ这个顺序来查看块,你会发现总共是1695个差异。
SQL> select sum(block_change) from ( 2 select block_fno, RANDOM_NUMBER, RNUM_UNQ, prev_bfno, 3 (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from ( 4 select block_fno, RANDOM_NUMBER, RNUM_UNQ, lag(block_fno) over (order by RANDOM_NUMBER, rnum_unq) prev_bfno from ( 5 select dbms_rowid.rowid_block_number(rowid)||'.'|| 6 dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno, 7 RANDOM_NUMBER, RNUM_UNQ 8 from s1 9 order by RANDOM_NUMBER, RNUM_UNQ, block_fno) 10 ) 11 ); Enter value for schema: U1 Enter value for table_name: S1 old 6: dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno, new 6: dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'U1','S1') block_fno, SUM(BLOCK_CHANGE) ----------------- 1695
那么我们换一下顺序,排序是RNUM_UNQ,RANDOM_NUMBER。这样先是唯一值,然后是非唯一值,这么查下来就是17。
SQL> select sum(block_change) from ( 2 select block_fno, RNUM_UNQ, RANDOM_NUMBER, prev_bfno, 3 (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from ( 4 select block_fno, RNUM_UNQ, RANDOM_NUMBER, lag(block_fno) over (order by rnum_unq, RANDOM_NUMBER) prev_bfno from ( 5 select dbms_rowid.rowid_block_number(rowid)||'.'|| 6 dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno, 7 RANDOM_NUMBER, RNUM_UNQ 8 from S1 9 order by RNUM_UNQ, RANDOM_NUMBER, block_fno) 10 ) 11 ); Enter value for schema: U1 Enter value for table_name: S1 old 6: dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno, new 6: dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'U1','S1') block_fno, SUM(BLOCK_CHANGE) ----------------- 17
可以看到,我们的聚簇因子就是这么计算出来的。但是事实上存在一个比较显著的问题,就是我总共才21个block。当我一个block进入到缓存中,它实际上是映射对应了多个索引的条目。而还存在一个比较显著的问题是,要扫描的一部分数据存储在块1,另外一部分存储在块2。当需要这一行时。块1和块2都会被读入到高速缓存当中。而如果这样的现象发生30%以上,则目前的聚簇因子显然也是不正确的。为了解决这两个问题,在12c推出了TABLE_CACHED_BLOCKS。它的作用就是当一个块被缓存到了内存当中,它其实映射了对应了多个索引的条目。我们不应该用上述的计算方法去计算聚簇因子,我们应该使用下面的一种算法。
这里我们做一个小计算。这里计算RANDOM_NUMBER这个列rowid的block和它前面的block,如果相等则=1,否则就是null。我把计算出的结果存放在cluster_factor表里面。
create table cluster_factor as select RANDOM_NUMBER, blkno, lag(blkno,1,blkno) over(order by RANDOM_NUMBER) prev_blkno, case when blkno!=lag(blkno,1,blkno) over(order by RANDOM_NUMBER) or rownum=1 then 1 else null end cluf_ft from (select RANDOM_NUMBER, rnum_unq, dbms_rowid.rowid_block_number(rowid) blkno from s1 where RANDOM_NUMBER is not null order by RANDOM_NUMBER);
按照前面,我们的RANDOM_NUMBER值为1-100,这里使用了大概56个块,最多一个块上有611行,这样当这个块被读到缓存当中,对应了611个索引的条目。而我总共有17个数据块。
SQL> select blkno, count(*) cnt from cluster_factor group by blkno order by 1; BLKNO CNT ---------- ---------- 11371 611 11372 603 11373 603 11374 603 11375 603 11376 603 11377 603 11378 603 11379 603 11380 603 11381 603 11382 603 11383 603 11385 603 11386 603 11387 603 11388 344
我们先看一下我们的TABLE_CACHED_BLOCKS现在设置的多大,现在默认设置是1。
SQL> select dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS',ownname=>'U1',tabname=>'S1') preference from dual; PREFERENCE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
这个参数可以设置成1-255之间。这个参数,我查了一下资料。在文档Clustering Factor (Doc ID 39836.1)里面也有介绍,对小表有作用,对于大表几乎没什么作用。
From 12c, the SET_GLOBAL_PREFS Procedure allows you to set a value for TABLE_CACHED_BLOCKS. The default is 1 and the max is 255. This parameter is used when gathering the index clustering factor and it tells the gathering engine the average number of blocks cached in the buffer cache for any table. With small tables this could have quite a significant effect, less so with larger objects since 255 will be a less significant proportion of the objects.
SQL> exec dbms_stats.set_table_prefs(ownname=>'U1',tabname=>'S1',pname=>'TABLE_CACHED_BLOCKS',PVALUE=>255); PL/SQL procedure successfully completed.
设置完成之后,再一次收集统计信息,发现所有的聚簇因子都下降到了17。
exec dbms_stats.gather_table_stats('U1','S1',method_opt=>'for all columns size 1', no_invalidate=>false); SQL> select index_name, leaf_blocks, clustering_factor from dba_indexes where table_name = 'S1'; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR ------------------- ----------- ----------------- S1_RNUM 21 17 S1_RNUM_RANDOM 26 17 S1_RANDOM 20 17 S1_RANDOM_RNUM 26 17