Oracle 聚簇因子(Clustering factor)

    聚簇因子是 Oracle 統計資訊中在CBO最佳化器模式下用於計算cost的參數之一，決定了當前的SQL語句是否走索引，還是全表掃描以及是否作為嵌套串連外部表格等。如此這般，那到底什麼是聚簇因子，那些情況下會影響到聚簇因子，以及如何提高聚簇因子？本文將對此展開描述。

 

1、堆表的儲存方式
    Oralce 資料庫系統中最普通，最為常用的即為堆表。
    堆表的資料存放區方式為無序儲存，也就是任意的DML操作都可能使得當前資料區塊存在可用的空閑空間。
    處於節省空間的考慮，塊上的可用空閑空間會被新插入的行填充，而不是按順序填充到最後被使用的塊上。
    上述的操作方式導致了資料的無序性的產生。
    當建立索引時，會根據指定的列按順序來填充到索引塊，預設的情況下為升序。
    建立或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，也就是存在了差異，即表現為聚簇因子。
    
2、什麼是聚簇因子(clustering factor/CF)
    聚簇因子是基於表上索引列上的一個值，每一個索引都有一個聚簇因子。
    用於描述索引塊上與表塊上儲存資料在順序上的相似程度，也就說表上的資料行的儲存順序與索引列上順序是否一致。
    在全索引掃描中，CF的值基本上等同於物理I/O或塊訪問數，如果相同的塊被連續讀，則Oracle認為只需要1次物理I/O。
    好的CF值接近於表上的塊數，而差的CF值則接近於表上的行數。
    聚簇因子在索引建立時就會通過表上存存在的行以及索引塊計算獲得。

 

3、Oracle 如何計算聚簇因子
    執行或預估一次全索引掃描。
    檢查索引塊上每一個rowid的值，查看是否前一個rowid的值與後一個指向了相同的資料區塊，如果指向了不相同的資料區塊則CF的值增加1。
    當索引塊上的每一個rowid被檢查完畢，即得到最終的CF值。

 

4、聚簇因子圖示

a、良好的索引與聚簇因子的情形

   

b、良好的索引、差的聚簇因子的情形

       

c、差的索引、差的聚簇因子的情形

      

 

5、影響聚簇因子的情形
    當插入到表的資料與索引的順序相同時，可以提高聚簇因子(接近表上的塊數)。
    因此，任意影響該順序的情形都將導致索引列上的聚簇因子變差。
    如列的順序，反向索引，空閑列表或空閑列表組。

 

6、提高聚簇因子
    堆表的資料存放區是無序儲存，因此需要使無序變為有序。下面是提高聚簇因子的辦法。
    a、對於表上的多個索引以及複合式索引的情形，索引的建立應考慮按應該按照經常頻繁讀取的大範圍資料的讀取順序來建立索引。
    b、定期重構表(針對堆表)，也就是使得表與索引上的資料順序更接近。注意，是重構表，而不是重建索引。
       重建索引並不能顯劇提高CF的值，因為索引列通常是有序的，無序的是原始表上的資料。
       提取原始表上的資料到一個暫存資料表，禁用依賴於該表的相關約束，truncate原始表，再將暫存資料表的資料按索引訪問順序填充到原始表。

    c、使用聚簇表來代替堆表。

 

7、實戰聚簇因子隨索引結構變化的情形

a、示範環境scott@SYBO2SZ> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Productionb、列順序對CF的影響--列順序指索引列值順序與表中的列值的順序，一致，則CF良好，不一致，CF較差。scott@SYBO2SZ> create table t as select * from dba_objects order by object_name;scott@SYBO2SZ> create index i_obj_name on t(object_name);  -->基於object_name列建立索引scott@SYBO2SZ> create index i_obj_id on t(object_id);      -->基於object_id列建立索引cott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.scott@SYBO2SZ> @idx_statEnter value for input_table_name: TEnter value for owner: SCOTT                                         AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS---- ------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------   1 I_OBJ_NAME           241      29476             1             1        675 20130418 17:00:42        695      48931   1 I_OBJ_ID             108      48931             1             1      24887 20130418 17:06:10        695      48931--從上面的查詢可以看出，索引I_OBJ_NAME的聚簇因子小於表上的塊數，一個良好的CF值，因為object_name列是有序插入的。--而索引I_OBJ_ID上的CF接近於表上行數的一半，說明該索引上的CF值不是很理想，因為object_id在插入到table時是無序的。--從上可知，一個表只能有一種有序的方式來組織資料。因此對於多出一個索引的表，且順序按照非插入時的順序時，則其他索引上的聚簇因子很難獲得理想的值。c、複合式索引對CF的影響--對於複合式索引，列的順序影響聚簇因子的大小--我們建立如下複合式索引scott@SYBO2SZ> create index i_obj_name_id on t (object_name, object_id);scott@SYBO2SZ> create index i_obj_id_name on t (object_id, object_name);scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true)PL/SQL procedure successfully completed.scott@SYBO2SZ> @idx_statEnter value for input_table_name: TEnter value for owner: SCOTT                                                     AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS---- ---------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------   1 I_OBJ_NAME              241      29476             1             1        675 20130418 17:17:17        695      48931   1 I_OBJ_ID                108      48931             1             1      24887 20130418 17:17:17        695      48931   1 I_OBJ_NAME_ID           274      48931             1             1        945 20130418 17:17:17        695      48931   1 I_OBJ_ID_NAME           274      48931             1             1      24887 20130418 17:17:18        695      48931--從上面的結果可知，--新建立的複合式索引，I_OBJ_NAME_ID(object_name, object_id),object_name是前置列，因此CF值儘管比單列是大，依然表現良好。--而索引I_OBJ_ID_NAME(object_id, object_name)，object_id作為前置列，CF值與單列索引I_OBJ_ID相同。--上面的四個索引來看，無論是單列還是符合索引，當索引列(leaf)的順序接近於表上行的順序，CF表現良好。d、反向索引對CF的影響--反轉索引主要是重新分配索引值，也就是將相連比較緊密地索引索引值分散到不同或相距比較遠的快上以避免競爭。--下面基於表t來新建立表t2scott@SYBO2SZ> create table t2 nologging as select * from t;scott@SYBO2SZ> create index i_obj_name_reverse on t2(object_name) reverse;  -->建立反向索引scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true)PL/SQL procedure successfully completed.scott@SYBO2SZ> @idx_statEnter value for input_table_name: T2Enter value for owner: SCOTT                                              AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME            LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS---- ------------------ ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------   1 I_OBJ_NAME_REVERSE        241      29476             1             1      28104 20130418 17:22:49        695      48931--上面建立的反向索引的CF較之前的都要大，因索引鍵上的值是反向的，也就是說是無序的。--在段空間管理基於手動管理的方式下，如果使用freelist可以避免段操作上DML的競爭，但索引列上將具有較比較糟糕的聚簇因子(示範省略)

8、實戰聚簇因子隨DML變化的情形

a、建立示範環境scott@SYBO2SZ> @cr_big_tb 1000000  -->建立一張百萬記錄的表Table created.scott@SYBO2SZ> @idx_stat           -->查看錶與索引相關資訊(CF為14489,TB_BLKS為14652)Enter value for input_table_name: BIG_TABLEEnter value for owner: SCOTT                                                     AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK        2088    1000000             1             1      14489 20130422 12:27:43       14652     999712b、類比DML操作--建立一個暫存資料表來儲存將要從表big_table刪除的記錄scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table where id>=10000 and id<=200000;scott@SYBO2SZ> delete from big_table nologging where id>=10000 and id<=200000;  -->從表big_table刪除一些記錄scott@SYBO2SZ> commit;     -->查看錶與索引相關資訊(從下面的查詢結果可知，刪除記錄並不使得CF發生變化)scott@SYBO2SZ> @idx_stat     Enter value for input_table_name: BIG_TABLEEnter value for owner: SCOTT                                          AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK         2088    1000000             1             1      14489 20130422 12:27:43       14652     999712scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);   -->收集統計資訊scott@SYBO2SZ> @idx_stat          -->查看錶與索引相關資訊(在收集統計資訊後，刪除記錄後CF為11732,TB_BLKS依然為14652)Enter value for input_table_name: BIG_TABLE            --(TB_BLKS塊數未發生變化是因為空白閑空間沒有釋放，需要shrink)Enter value for owner: SCOTT                                         AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK        1692     809999             1             1      11732 20130422 12:31:45       14652     808497-->接下來將刪除的資料插入到big_table以類比表上新增資料，分兩次插入，以使得id變得無序   scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000  2  order by object_name;  scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000  2  order by object_name;scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->收集統計資訊scott@SYBO2SZ> @idx_stat     -->查看錶與索引相關資訊(此時CF的值由原來的14489增大到114256，呈數量級變化)Enter value for input_table_name: BIG_TABLEEnter value for owner: SCOTT                                         AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK        2088    1000000             1             1     114256 20130422 12:33:31       14652     998513   --下面嘗試move table是否對CF有向影響scott@SYBO2SZ> alter table big_table move;    scott@SYBO2SZ> @idx_stat   -->查看錶與索引相關資訊(move table之後，無任何變化)Enter value for input_table_name: BIG_TABLEEnter value for owner: SCOTT                                          AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK         2088    1000000             1             1     114256 20130422 12:33:31       14652     998513-->嘗試收集統計資訊後，在看CF的變化-->下面的錯誤表明，move之後，索引失效scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;*ERROR at line 1:ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable stateORA-06512: at "SYS.DBMS_STATS", line 13182ORA-06512: at "SYS.DBMS_STATS", line 13202ORA-06512: at line 1scott@SYBO2SZ> alter index big_table_pk rebuild nologging;   ---->重建索引scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->再次收集統計資訊PL/SQL procedure successfully completed.scott@SYBO2SZ> @idx_stat  -->重建索引後，CF的值反而增大了Enter value for input_table_name: BIG_TABLEEnter value for owner: SCOTT                                         AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_PK        2088    1000000             1             1     118384 20130422 12:36:31       14649     999427c、重建big_table-->下面通過重建big_table來縮小CF的值，新的表名為big_table_tmpscott@SYBO2SZ> drop table big_table_tmp purge;  --->刪除之前的暫存資料表scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table order by id;scott@SYBO2SZ> create unique index big_table_tmp_pk on big_table_tmp(id);scott@SYBO2SZ> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE_TMP',cascade=>true);scott@SYBO2SZ> @idx_stat    --->表big_table_tmp上的CF值(14486)小於原始的CF值(14489)Enter value for input_table_name: big_table_tmpEnter value for owner: scott--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612                                            AVG LEAF BLKS AVG DATA BLKSBLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS---- ---------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------   2 BIG_TABLE_TMP_PK       2088    1000000             1             1      14486 20130422 12:38:37       14649     995891d、比較不同的CF對查詢效能的影響-->下面來基於表big_table與big_table_tmp來比較一下不同的CF對查詢的影響scott@SYBO2SZ> set autot trace;scott@SYBO2SZ> select * from big_table where id between 10000 and 15000;5001 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3747652938--------------------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |  5001 |   478K|   606   (0)| 00:00:08 ||   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |  5001 |   478K|   606   (0)| 00:00:08 ||*  2 |   INDEX RANGE SCAN          | BIG_TABLE_PK |  5001 |       |    13   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID">=10000 AND "ID"<=15000)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets       2993  consistent gets        531  physical reads        116  redo size     287976  bytes sent via SQL*Net to client       4155  bytes received via SQL*Net from client        335  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)       5001  rows processed--原始表上的查詢的cost為606, consistent gets與physical reads分別為2993,531scott@SYBO2SZ> select * from big_table_tmp where id between 10000 and 15000;5001 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1127920103------------------------------------------------------------------------------------------------| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                  |  4982 |   476K|    86   (0)| 00:00:02 ||   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE_TMP    |  4982 |   476K|    86   (0)| 00:00:02 ||*  2 |   INDEX RANGE SCAN          | BIG_TABLE_TMP_PK |  4982 |       |    13   (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID">=10000 AND "ID"<=15000)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets        750  consistent gets         76  physical reads          0  redo size     287976  bytes sent via SQL*Net to client       4155  bytes received via SQL*Net from client        335  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)       5001  rows processed--新建立的表的cost 為86, consistent gets與physical reads分別為750,76. 呈數量級低低於原表的開銷             -->可以將原始表big_table上的資料刪除(truncate)，刪除前禁用依賴於該表的所有約束，然後將big_table_tmp的資料使用order by插入到big_table-->註上面的create table as ..方式並不適合用於生產環境的真實操作，因為表上的一些屬性會被忽略掉.

9、小結
  a、任意情形下(堆表)，表上資料的儲存只能按照一種特定的順序進行儲存。
  b、由上面的特性決定了表上的只有一個特定的索引列(單索引或複合式索引)具有最佳的CF值。
  c、索引的建立應考慮按應該按照經常頻繁讀取的大範圍資料的讀取順序來建立索引，以保證得到最佳的CF值。
  d、索引在被建立之時，基於該索引列上的CF值即被產生，但表上的DML操作後需要收集統計資訊才可以更新CF的值。
  e、基於表上頻繁的DML操作，尤其是delete後再新增記錄，可用空閑空間被填充，將使得CF的值呈增大趨勢。
  f、alter table move tabname並不會影響CF的值，該功能只是移動高水位線，且不釋放空間。
  g、重建索引對CF的值收效甚微，因為原始表資料存放區順序未發生根本變化。
  h、CF的值是影響查詢分析器對執行計畫的評估與產生的因素之一(即是否走索引還是全表掃描，嵌套串連時哪個表為驅動表等)。
  i、通過重建表或使用聚簇表來改進CF的值，建議將原始表資料填充到暫存資料表，禁用依賴於該表的所有約束後truncate該表，再從暫存資料表導回資料(按順序)，啟用約束。
  j、不推薦使用create table as select(CTAS)，因為表上的一些特性會被忽略，可以用SQL*Plus copy方式來實現。具體參考: 當心 CREATE TABLE AS 

 

更多參考

有關Oracle RAC請參考
     使用crs_setperm修改RAC資源的所有者及許可權
     使用crs_profile管理RAC資源設定檔
     RAC 資料庫的啟動與關閉
     再說 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 串連到指定執行個體
     Oracle RAC 負載平衡測試(結合伺服器端與用戶端)
     Oracle RAC 伺服器端串連負載平衡(Load Balance)
     Oracle RAC 用戶端串連負載平衡(Load Balance)
     ORACLE RAC 下非預設連接埠監聽配置(listener.ora tnsnames.ora)
     ORACLE RAC 監聽配置 (listener.ora tnsnames.ora)
     配置 RAC 負載平衡與容錯移轉
     CRS-1006 , CRS-0215 故障一例 
     基於Linux (RHEL 5.5) 安裝Oracle 10g RAC
     使用 runcluvfy 校正Oracle RAC安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊
     配置sqlnet.ora限制IP訪問Oracle
     Oracle 監聽器日誌配置與管理
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)