Oracle 12cR1-TABLE_CACHED_BLOCKS FOR STATISTICS COLLECTION

今天我们主要介绍下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

ORACLE数据库服务热线：13764045638 诗檀软件

诗檀软件邮箱: service@parnassusdata.com , ORACLE QQ群号：171092051，微信公众号: askMaclean

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈，了解Oracle最新技术下载分享资源

好用的站内搜索

新浪微博

最新动态

站内相关文章

文章分类