1.統計信息簡介 統計信息主要是描述數據庫中表，索引的大小，規模，數據分布狀況等的一類信息。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引字段的行數，不同值的大小等，都屬于統計信息。CBO正是根據這些統計信息數據，計算出不同訪問路
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常用的幾個過程如下：
|
1
2
3
4
5
6
7
8
9
10
11
12 |
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 命令的語法如下：
|
1
2
3 |
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來查看：
|
1
2
3 |
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)
|
1
2
3
4
5 |
sys@ORCL> show parameter statistics_level;
NAME TYPE VALUE
statistics_level string TYPICAL
|
當啟動對象的監控后，從上次統計信息收集之后的的信息，如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）索引的分析： 包括索引葉塊的數量，索引的深度，索引的聚合因子等。
這些統計信息存放在以下的數據字典里：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
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的數據，行的平均大小：
|
1
2
3
4
5
6
7 |
SELECT NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN
FROM USER_TABLES
|
3.3索引列的統計信息
包含索引的深度（B-Tree的級別），索引葉級的塊數量，集群因子（clustering_factor), 唯一值的個數。
|
1
2
3
4
5
6
7 |
SELECT BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR
FROM USER_INDEXES
|
3.4 列的統計信息
包含唯一的值個數，列最大小值，密度（選擇率），數據分布（直方圖信息），NUll值個數
|
1
2
3
4
5
6
7
8 |
SELECT NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
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