4.2　統計信息操作

常見的統計信息操作包括查看、收集、修改、刪除、鎖定等。下面針對每種操作，簡單說明一下。

統計信息相關的操作中，最常見的就是查看操作。常見的場景是需要判斷統計信息是否準確，進而排除可能因為統計信息失真導致優化器制定出低效執行計劃的情況。統計信息保存在數據字典中，我們可以通過視圖去查詢相關統計信息。

收集統計信息也非常重要。在一般情況下，系統自動收集的統計信息是可以滿足我們需要的，但也有些情況是需要人為干預、手工收集統計信息。這部分比較復雜，相關命令的選項也比較多，下面會針對常見的一些情況加以說明。

修改統計信息操作很少見。常見的情況是對象非常大，做收集動作非常慢，才采取人為修改統計信息的方式；或者是在測試環境中，為了模擬某些操作行為，而又沒有那么大數據量而采取手工修改來欺騙優化器。不建議進行修改操作，除非是對各種統計信息指標非常了解。

刪除統計信息的操作，相對用得較少。常見的情況是現有統計信息有問題，只需要重新收集然后覆蓋就可以了，基本不需要刪除。一般只有在直方圖中，因錯誤地收集了直方圖信息導致問題，才需要手工刪除直方圖統計信息。

鎖定統計信息也不太常用。它主要是為了避免因統計信息變化導致執行計劃發生變化。一般我們是相信系統對統計信息的處理的，不需要鎖定處理。

下面針對不同類別的統計信息的主要操作分別加以說明。

4.2.1　系統統計信息

1.收集統計信息

我們知道，系統統計信息分為兩種，一種是非工作量統計信息（noworkload statistics）和工作量統計信息（workload statistics）。從10g開始，非工作量的統計信息總是可用的。對于工作量統計信息，則可以按照下面步驟進行收集：

---

exec dbms_stats.gather_system_stats('start')      //開始收集系統統計信息
運行一段時間                                    //最好是以系統典型負載運行一段時間
exec dbms_stats.gather_system_stats('stop')      //停止收集系統統計信息

---

除了上面的方法外，也可以用下面的方法，其中interval參數為間隔時長（單位分鐘）：

---

dbms_stats.gather_system_stats(gathering_mode=>'interval', interval=>N);

---

2.查看統計信息

系統統計信息放在aux_stats$表里面。Oracle沒有提供數據字典視圖來供外部訪問。根據sname不同，可以將統計信息劃分為三個結果集（不同類別的信息），分別如下：

·SYSSTATS_INFO：系統統計信息的狀態和收集時間。

·SYSSTATS_MAIN：系統統計信息結果集。

·SYSSTATS_TEMP：用來計算系統統計信息，只有收集工作量統計信息時才可用。

查看系統統計信息的方法如下：

---

select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

---

也可以使用dbms_stats.get_system_stats過程獲得統計信息。

3.修改統計信息

手工設置統計信息，大致操作如下：

---

begin
dbms_stats.set_system_stats(pname=>'CPUSPEED',pvalue=>772);
dbms_stats.set_system_stats(pname=>'SREADTIM',pvalue=>5.5);
dbms_stats.set_system_stats(pname=>'MREADTIM',pvalue=>19.4);
dbms_stats.set_system_stats(pname=>'MBRC',pvalue=>53);
dbms_stats.set_system_stats(pname=>'MAXTHR',pvalue=>1243434334);
dbms_stats.set_system_stats(pname=>'SLAVETHR',pvalue=>1212121);
end;
*pname為指定統計信息參數，pvalue為統計信息的值

---

4.刪除統計信息

刪除統計信息的方法如下：

---

exec dbms_stats.delete_system_stats;//調用這個過程不需要參數

---

4.2.2　對象統計信息

對象統計信息的相關操作是日常使用最多的。

1.收集統計信息

收集統計信息主要由analyze命令和dbms_stats包實現。一般建議使用dbms_stats代替analyze命令。當然這兩者不是完全等價的，有些情況必須要使用analyze，例如分析索引的結構信息。作為重點，下面主要介紹一下dbms_stats包的用法。

dbms_stats包本身很復雜，涉及統計信息方方面面的操作都可以通過它完成。下面主要針對對象統計信息的收集動作，通過幾個例子說明一下它的用法及主要參數。

收集整個庫中對象的統計信息，采樣率通過estimate_percent指定，這里為15%，命令如下：

---

exec dbms_stats.gather_database_stats(estimate_percent => 15);

---

收集指定Schema的統計信息，命令如下：

---

exec dbms_stats.gather_schema_stats('scott', estimate_percent => 15);

---

收集指定表的統計信息，命令如下：

---

exec dbms_stats.gather_table_stats('scott', 'employees', estimate_percent => 15);

---

通過method_opt指定是否收集直方圖，例子中說明為所有有索引的列收集且只會為現有的直方圖重新分析，不再搜索其他直方圖。通過granularity指定如何處理分區對象的統計信息，這里指定all代表收集對象、分區、子分區統計信息。通過cascade選項指定是否收集索引的統計信息。

---

exec dbms_stats.gather_table_stats(ownname => 'prd_user',tabname => 'prd_syi_search',method_opt => 'for all indexed columns size repeat',granularity=>'all',cascade => true);
exec dbms_stats.gather_index_stats('scott', 'employees_pk', estimate_percent => 15);

---

2.查看統計信息

根據對象的不同，其統計信息可到不同的視圖中查看。這部分涉及的數據字典比較多，如表4-2所示。

表4-2　對象統計信息數據字典

帶有星號的表主要在9i之前使用。這是因為視圖user_tab_statistics和user_ind_statistics只能在10g上使用。

下面分別針對表、列和索引進行舉例說明。

1）查看表的統計信息：

---

select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,
      global_stats,user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_tables t
where owner='xxx' and table_name='xxx';

---

主要字段說明：

·num_row：數據的行數。

·blocks：高水位下的數據塊個數。

·empty_block：高水位以上的數據塊個數。dbms_stats不計算這個值，被設置為0。

·avg_space：數據塊中平均空余空間（字節）。dbms_stats不計算這個值，被設置為0。

·chain_cnt：行鏈接和行遷移的數目。dbms_stats不計算這個值，被設置為0。

·avg_row_len：行平均長度（字節）。

·last_analyzed：最后收集統計信息時間。

2）查看索引的統計信息：

---

select index_name,uniqueness,blevel,leaf_blocks,distinct_keys,num_rows,
      avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,global_stats,
      user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_indexes t
where table_owner='xx' and table_name='xx';

---

主要字段說明：

·num_rows：索引行。

·leaf_blocks：索引葉塊數。

·distinct_keys：索引不同鍵數。

·blevel：索引的blevel分支層數（btree的深度，從root節點到leaf節點的深度。如果root節點也是leaf節點，那么這個深度就是0）。

·avg_leaf_blocks_per_key：每個鍵值的平均索引leaf塊數（每個鍵值的平均索引leaf塊數（近似取整），如果是unique index或pk，這個值總是1）。

·avg_data_blocks_per_key：每個鍵值的平均索引數據（表）塊數。

·clustering_factor：索引的群集因子（索引集群因子）。

3）查看列的統計信息：

---

select column_name,num_distinct,density,num_buckets,num_nulls,global_stats,user_stats,
histogram,num_buckets,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_tab_cols t
where owner='xx' and table_name='xx';

---

主要字段說明：

·num_distinct：不同值的數目。

·num_nulls：字段值為null的數目。

·density：選擇率。

·histogram：是否有直方圖統計信息。如果有，是哪種類型。10g以后才提供。

·NONE：沒有。

·FREQUENCY：頻率類型。

·HEIGHT BALANCED：基于高度類型。

·num_buckets：直方圖的桶數。

3.修改統計信息

可以直接通過dbms_stats包的相關方法設置對象的統計信息。下面舉例說明。

---

exec dbms_stats.set_table_stats(ownname=>'HF',tabname=>'EMP',
      numrows=>10000000,no_invalidate=>false);
/*
這個例子設置了HF.EMP表的統計項numrows為1000萬,參數no_invalidate表示不會設置相關的SQL游標失效
*/
exec dbms_stats.set_index_stats(ownname=>'HF',indname=>'IDX_EMP',
      numlblks=>100000,no_invalidate=>false);
/*
這個例子設置了HF.IDX_EMP索引的統計項numlblks為10萬，參數no_invalidate表示不會設置相關的SQL游標失效
*/

---

4.刪除統計信息

類似上面收集的方法，每一個gather_xxx_stats就對應一個delete_xxx_stats方法。此外還多一個delete_column_stats方法，專門用來刪除列的統計信息。下面舉例說明。

---

exec dbms_stats.delete_table_stats('scott', 'employees');
//上面例子刪除了scott用戶下employees表的對象統計信息
exec dbms_stats.delete_index_stats('scott', 'employees_pk');
//上面例子刪除了scott用戶下employees_pk索引的對象統計信息
exec dbms_stats.delete_column_stats(
ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM');
//上面例子刪除了當前用戶下，T表的VAL字段的直方圖信息

---

5.鎖定統計信息

從10g以后，可以明確鎖定對象的統計信息。相關的操作包括鎖定、解鎖、查看鎖定狀態。下面舉例說明。

鎖定對象的統計信息：

---

dbms_stats.lock_schema_stats(ownname=>user);
//上面例子鎖定了當前用戶的所有對象統計信息
dbms_stats.lock_table_stats(ownname=>user,tabname=>'T');
//上面例子鎖定了當前用戶的T表統計信息

---

解鎖對象的統計信息：

---

dbms_stats.unlock_schema_stats(ownname=>user);
//上面例子對當前用戶的對象統計信息取消鎖定
dbms_stats.unlock_table_stats(ownname=>user,tabname=>'T');
//上面例子對當前用戶的T表統計信息取消鎖定

---

查看對象是否鎖定了統計信息：

---

select table_name from user_tab_statistics where stattype_locked is not null;
//查看當前用戶下所有表中有鎖定統計信息的對象名稱

---

[1] 一個度量標準, 用于索引的有序度和表混亂度之間的比較。

4.2.3　數據字典統計信息

1.收集統計信息

對數據字典統計信息，可以用專門的方法收集，也可以按普通表的方式收集。下面舉例說明。

---

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TAB$',estimate_percent=> 100,cascade=>true);

---

2.刪除統計信息

類似收集的方法，刪除統計信息的方法也有兩種。

---

exec dbms_stats.delete_dictionary_stats;
//上面例子刪除字典對象統計信息

exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'TAB$');
//上面例子刪除SYS用戶TAB$表的統計信息

---

3.查看統計信息

查看統計信息就按照普通對象進行查詢即可，這里就不具體介紹了。

4.2.4　內部對象統計信息

對內部對象統計信息的收集，可以用專門的方法進行，也可以按普通表的方法進行。刪除也類似。下面舉例說明。

---

exec dbms_stats.gather_fixed_objects_stats();
//收集內部對象的統計信息

exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'X$KCCRSR',
     estimate_percent=>100,cascade=>true);
//收集某個內部對象(SYS.X$KCCRSR)的統計信息

exec dbms_stats.delete_fixed_objects_stats();
//刪除內部對象的統計信息

exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'X$KCCRSR');
//刪除某個內部對象(SYS.X$KCCRSR)的統計信息

---