深入理解Oracle Statistic統計資訊

1.統計資訊簡介

統計資訊主要是描述資料庫中表，索引的大小，規模，資料分佈狀況等的一類資訊。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引欄位的行數，不同值的大小等，都屬於統計資訊。cbo正是根據這些統計資訊資料，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最後選擇出成本最小的計劃。

在cbo(基於代價的最佳化器模式)條件下，sql語句的執行計劃由統計資訊來決定，若沒有統計資訊則會採取動態取樣的方式決定執行計劃！可以說統計資訊關乎sql的執行計劃是否正確，屬於sql執行的指導思想，oracle的初始化引數statistics_level控制收集統計資訊的級別，有三個引數值:
basic :收集基本的統計資訊
typical：收集大部分統計資訊(資料庫的預設設定)
all：收集全部統計資訊

oracle 10g之後，query optimizer就已經將cbo作為預設最佳化器，並且oracle官方不再支援rbo服務。但是，透過最佳化器引數optimizer_mode，我們可以控制oracle最佳化器生成不同模式下的執行計劃。

關於最佳化器的請參考：《 sql效能最佳化之optimizer_mode引數原理滲透解析》

2.如何收集統計資訊
2.1 統計資訊的內容：

1）table statistics，表統計資訊
number of rows --行數量
number of blocks --block數量
average row length --平均行的長度.

2）column statistics，列統計資訊
number of distinct values (ndv) in column --列中distinct的值
number of nulls in column --列中null的值
data distribution (histogram) --資料分佈

3）index statistics，索引統計資訊
number of leaf blocks --子節點的塊數量
levels --子節點數量
clustering factor --叢集因子

4）system statistics，系統統計資訊
i/o performance and utilization --io效能和利用率
cpu performance and utilization --cpu的效能和利用率

2.2 收集統計資訊
oracle statistic 的收集，可以使用analyze 命令，也可以使用dbms_stats 包來收集，oracle 建議使用dbms_stats包來收集統計資訊，因為dbms_stats包收集的更廣，並且更準確。analyze 在以後的版本中可能會被移除。

dbms_stats常用的幾個過程如下：
dbms_stats.gather_table_stats 收集表、列和索引的統計資訊；
dbms_stats.gather_schema_stats 收集schema下所有物件的統計資訊；
dbms_stats.gather_index_stats 收集索引的統計資訊；
dbms_stats.gather_system_stats 收集系統統計資訊；
dbms_stats.gather_dictionary_stats：收集所有字典物件的統計；
dbms_stats.gather_dictionary_stats 收集所有系統模式的統計
dbms_stats.delete_table_stats 刪除表的統計資訊
dbms_stats.delete_index_stats 刪除索引的統計資訊
dbms_stats.export_table_stats 輸出表的統計資訊
dbms_stats.create_state_table dbms_stats.set_table_stats 設定表的統計 dbms_stats.auto_sample_size

analyze 命令的語法如下：
sql>analyze table tablename compute statistics ;

sql>analyze table tablename compute statistics for all indexes;

sql>analyze table tablename delete statistics

2.3 統計資訊的分類
oracle 的statistic 資訊的收集分兩種：自動收集和手工收集。
oracle 的automatic statistics gathering 是透過scheduler 來實現收集和維護的。job 名稱是gather_stats_job, 該job收集資料庫所有物件的2種統計資訊：
（1）missing statistics（統計資訊缺失）
（2）stale statistics（統計資訊陳舊）

該job 是在資料庫建立的時候自動建立，並由scheduler來管理。scheduler 在maintenance windows open時執行gather job。預設情況下，job 會在每天晚上10到早上6點和週末全天開啟。該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序，再開始進行統計資訊。

scheduler job的stop_on_window_close 屬性控制gather_stats_job 是否繼續。該屬性預設值為true. 如果該值設定為false，那麼gather_stats_job 會中斷，而沒有收集完的物件將在下次啟動時繼續收集。

gather_stats_job 呼叫dbms_stats.gather_database_stats_job_proc過程來收集statistics 的資訊。該過程收集物件statistics的條件如下：
（1）物件的統計資訊之前沒有收集過。
（2）當物件有超過10%的rows 被修改，此時物件的統計資訊也稱為stale statistics。

但是對於高度變化的表在白天的活動期間被truncate/drop並重建或者塊載入超過本身總大小10%的物件；我們可以將這些表上的統計設定為null
可以透過以下sql來檢視：
select job_name, program_name, enabled, stop_on_window_close
from dba_scheduler_jobs
where job_name = 'gather_stats_job' ;

為了決定是否對物件進行監控，oracle 提供了一個引數statistics_level。透過設定初始化引數statistic_level 為typical 或all，就可以自動收集統計資訊(預設值為typical，因此可以隨即啟用自動收集統計資訊的功能)。statistic_level 引數的值可以啟用gather_stats_job。

在10g中表監控預設是啟用的，如果statistics_level設定為basic，不僅不能監控表，而且將禁掉如下一些10g的新功能：
（1）ash(active session history)
（2）assm(automatic shared memory management)
（3）awr(automatic workload repository)
（4）addm(automatic database diagnostic monitor)

sys@orcl> show parameter statistics_level;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>

當啟動物件的監控後，從上次統計資訊收集之後的的資訊，如inserts，updates，deletes 等，這些改變的資訊會記錄到user_tab_modifications 檢視。
當物件的資料發生改變之後，經過幾分鐘的延時，這些資訊寫入到user_tab_modifications檢視，然後dbms_stats.flush_database_monitoring_info過程就會發現這些資訊，並將這些資訊儲存在記憶體中，當監控的物件被修改的部分超過10%時，gather_database_stats 或者gather_schema_stats 過程就會去收集這些stale statistics

3.統計資訊的儲存位置以及常用資料字典
3.1 統計資訊常用資料字典
統計資訊收集如下資料：
（1）表自身的分析：包括表中的行數，資料塊數，行長等資訊。
（2）列的分析：包括列值的重複數，列上的空值，資料在列上的分佈情況。
（3）索引的分析：包括索引葉塊的數量，索引的深度，索引的聚合因子等。

這些統計資訊存放在以下的資料字典裡：
dba_tables
dba_object_tables
dba_tab_statistics
dba_tab_col_statistics
dba_tab_histograms
dba_indexes
dba_ind_statistics
dba_clusters
dba_tab_partitions
dba_tab_subpartitions
dba_ind_partitions
dba_ind_subpartitions
dba_part_col_statistics
dba_part_histograms
dba_subpart_col_statistics
dba_subpart_histograms

3.2 表的統計資訊
包含錶行數，使用的塊數，空的塊數，塊的使用率，行遷移和連結的數量，pctfree，pctused的資料，行的平均大小：

select num_rows, --表中的記錄數
blocks, --表中資料所佔的資料塊數
empty_blocks, --表中的空塊數
avg_space, --資料塊中平均的使用空間
chain_cnt, --表中行連線和行遷移的數量
avg_row_len --每條記錄的平均長度
from user_tables;

select num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len from user_tables;

3.3索引列的統計資訊
包含索引的深度（b-tree的級別），索引葉級的塊數量，叢集因子（clustering_factor), 唯一值的個數。

select blevel, --索引的層數
leaf_blocks, --葉子結點的個數
distinct_keys, --唯一值的個數
avg_leaf_blocks_per_key, --每個key的平均葉塊個數
avg_data_blocks_per_key, --每個key的平均資料塊個數
clustering_factor --群集因子
from user_indexes;

3.4 列的統計資訊
包含唯一的值個數，列最大小值，密度（選擇率），資料分佈（直方圖資訊），null值個數

select num_distinct, --唯一值的個數
low_value, --列上的最小值
high_value, --列上的最大值
density, --選擇率因子（密度）
num_nulls, --空值的個數
num_buckets, --直方圖的bucket個數
histogram --直方圖的型別
from user_tab_columns;

對於統計資訊的蒐集，談談個人的幾點理解：
1.統計資訊預設是存放在資料字典表中的，也只有資料字典中的統計資訊，才會影響到cbo。
2.dbms_stats 提供的create_stat_table 過程，只是生成一個使用者自定義的特定格式的表，用來存放統計資訊罷了，這個表中的統計資訊是不會影響到統計資訊的。
3.gather 系列過程中，如果指定stattab，statid，statown 引數（也可以不指定），則是蒐集的統計資訊除了更新到資料字典外，還在statown 使用者下的stattab 表中存放一份，標示為 statid;
4.export和import 系列的過程中,stattab,statid,statown 引數不能為空，分別表示把資料字典中的當前統計資訊匯出到使用者自定義的表中，以及把使用者表中的統計資訊匯入到資料字典中，很明顯可以看出，這裡的匯入操作和上面gather 操作會改變統計資訊，可能會引起執行執行計劃的改變，因此要慎重操作。
5.每次統計資訊蒐集前，將舊的統計資訊備份起來是很有必要的；特別是保留一份或多份系統在穩定時期的統計資訊也是很有必要的。
6.多長時間蒐集一次統計資訊，對於統計資訊如何備份和保留，蒐集統計資訊時如何選擇合適的取樣，並行，直方圖設定等都比較重要，需要設計一個較好的統計資訊蒐集策略。

在ocp 10g考試中會有個跟統計資訊相關的考試題：
1. in your oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm
every friday. the job has successfully completed. which three pieces of information would you check to
confirm that the statistics have been collected? (choose three.)

a. average row size
b. last analyzed date
c. size of table in bytes
d. size of table in database blocks
e. number of free blocks in the free list
f. number of extents present in the table

Answer: ABD

深入理解Oracle Statistic統計資訊

相關文章