聚簇因子的计算方式与较正

/******************************************************************************/
--1.创建测试表

 

DROP TABLE clftr_anl PURGE;
  CREATE TABLE clftr_anl(ID INT,scatt INT) TABLESPACE dict_tabs;
  INSERT INTO clftr_anl
    SELECT LEVEL, MOD(LEVEL, 20) FROM dual CONNECT BY LEVEL <= 10000;
  COMMIT;
  --创建索引

  CREATE INDEX ind_clftr_anl ON clftr_anl(ID);
  CREATE INDEX ind_clftr_anl1 ON clftr_anl(scatt);
  --收集统计信息

  BEGIN
    dbms_stats.gather_table_stats(ownname => USER,
                                  tabname => 'clftr_anl',
                                  estimate_percent => 100,
                                  method_opt => 'for all columns size 1',
                                  cascade => TRUE);
  END;
  /

  

  --查看索引统计信息
  SELECT ui.index_name,
         ui.clustering_factor,
         ui.leaf_blocks,
         ui.avg_leaf_blocks_per_key,
         ui.avg_data_blocks_per_key
  FROM   user_ind_statistics ui
  WHERE  ui.table_name = upper('clftr_anl');
  /*
  IND_CLFTR_ANL1 340 20 1 17
  IND_CLFTR_ANL   17 21 1 1
  */

 

  SELECT blocks
  FROM   user_tab_statistics ut
  WHERE  ut.table_name = upper('clftr_anl');
  --20

 

  SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID)) bcnt
  FROM   clftr_anl;
  --17

/******************************************************************************/
--2.手工计算cluster factoring

 

WITH t1 AS
  (
    SELECT /*+index(t) no_index_ffs(t) noparallel_index(t)*/
     dbms_rowid.rowid_relative_fno(ROWID) || '.' ||
     dbms_rowid.rowid_block_number(ROWID) fbno
    FROM clftr_anl t
    WHERE ID IS NOT NULL
  ),t2 AS
  (
    SELECT fbno, lag(fbno,1,-100) over(ORDER BY NULL) AS lg_fbno FROM t1
  ),t3 AS
  (
  SELECT t2.*,
         CASE
           WHEN fbno <> lg_fbno THEN
            1
           ELSE
            0
         END AS flag
  FROM t2
  )
  SELECT SUM(flag) AS cluster_factoring FROM t3;
  --17

/******************************************************************************/
--3.使用clustering_factor函数，它来自Christian Antognini：

 

CREATE OR REPLACE FUNCTION clustering_factor
  (
    p_owner IN VARCHAR2,
    p_table_name IN VARCHAR2,
    p_column_name IN VARCHAR2
  ) RETURN NUMBER IS
    l_cursor SYS_REFCURSOR;
    l_clustering_factor BINARY_INTEGER := 0;
    l_block_nr BINARY_INTEGER := 0;
    l_previous_block_nr BINARY_INTEGER := 0;
    l_file_nr BINARY_INTEGER := 0;
    l_previous_file_nr BINARY_INTEGER := 0;
  BEGIN
    OPEN l_cursor FOR 'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, ' ||
     ' dbms_rowid.rowid_to_absolute_fno(rowid, ''' || p_owner || ''',''' || p_table_name || ''') file_nr ' ||
      'FROM ' || p_owner || '.' || p_table_name || ' ' ||
       'WHERE ' || p_column_name || ' IS NOT NULL ' || 'ORDER BY ' || p_column_name;

    LOOP

      FETCH l_cursor
        INTO l_block_nr, l_file_nr;

      EXIT WHEN l_cursor%NOTFOUND;

      IF (l_previous_block_nr <> l_block_nr OR
         l_previous_file_nr <> l_file_nr)
      THEN
        l_clustering_factor := l_clustering_factor + 1;
      END IF;

      l_previous_block_nr := l_block_nr;
      l_previous_file_nr := l_file_nr;

    END LOOP;

    CLOSE l_cursor;
    RETURN l_clustering_factor;
  END clustering_factor;
  /

  SELECT clustering_factor('SCOTT', 'CLFTR_ANL', 'ID') cluster_factoring
  FROM   dual;
  --17

 

  SELECT clustering_factor('SCOTT', 'CLFTR_ANL', 'scatt') cluster_factoring
  FROM   dual;
  --340

/******************************************************************************/
--4.使用SYS_OP_COUNTCHG计算cluster_factoring，oracle未公布的内部函数，是dbms_stats
--低层调用：

 

  1):来自psoug的说明

 

SYS_OP_COUNTCHG

 Undocumented

SYS_OP_COUNTCHG(rowid, integer_between_1_and_255)

SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid))
 FROM airplanes;

 SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1)
 FROM airplanes;

 EXPLAIN PLAN FOR
 SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid))
 FROM airplanes;

 SELECT * FROM TABLE(dbms_xplan.display);

 EXPLAIN PLAN FOR
 SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1)
 FROM airplanes;

 SELECT * FROM TABLE(dbms_xplan.display);

-- note difference between SORT GROUP BY and SORT AGGREGATE

 

  2):抓取自10046中在收集索引统计信息时的sql片段

 

SELECT /*+
    cursor_sharing_exact
    dynamic_sampling(0)
    no_monitoring
    no_expand
    index(t,IND_CLFTR_ANL1)
    noparallel_index(t,IND_CLFTR_ANL1)
  */
 sys_op_countchg(substrb(t.ROWID, 1, 15), 1) AS clf
FROM clftr_anl t
WHERE scatt IS NOT NULL;
--340

/******************************************************************************/
--5.cluster_factoring对执行计划的影响

 

BEGIN
  dbms_stats.set_index_stats(ownname => USER,
                             indname => 'ind_clftr_anl1',
                             clstfct => 20);
END;
/

 

SELECT /*+index(t) gather_plan_statisitcs*/
 *
FROM   scott.clftr_anl t
WHERE  scatt IS NOT NULL;

------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                | 10000 | 60000 |    41 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CLFTR_ANL      | 10000 | 60000 |    41 |
|*  2 |   INDEX FULL SCAN           | IND_CLFTR_ANL1 | 10000 |       |    21 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SCATT" IS NOT NULL)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        365  consistent gets
          0  physical reads
          0  redo size
      90467  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
     
这里我们看到，autotrace输出的cost此时一定是不正确的，而真实可信的是365  consistent gets，
下面我们来看看真实运行时执行计划：

 

SELECT *
FROM   TABLE(dbms_xplan.display_cursor('a0m8kxd744871', 0, 'runstats_last'));
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| CLFTR_ANL      |      1 |  10000 |  10000 |00:00:00.01 |     365 |
|*  2 |   INDEX FULL SCAN           | IND_CLFTR_ANL1 |      1 |  10000 |  10000 |00:00:00.01 |      23 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("SCATT" IS NOT NULL)
  
/******************************************************************************/
  6.总结

 

  其实说了那么多，无非就是要提醒一点；即当使用dbms_stats收集的索引统计信息时，如果
clustering_factor被计算的很高，则此时可能要人为介入以给出一个合适的值，否则这个索引

在区间扫描时被使用的机率很低。

 

  那么在什么情况下我们可以适度的人为降低clustering factor呢？其实原则很简单，我们知道

如果clustering factor值很大，如果采用传统索引访问路径，则我们要在表的不同块之间反复切

换，而这些块可能都已经在内存中了，所以不存在io开销问题；对于内存块访问，最昂贵的开销莫

过于latch，而latch直接影响并发，所以如果你使用的是OLTP系统，则不建议你调整这个值，而

如果是DSS，OLAP系统，则可以适当的下调这个值的大小。