http://space.itpub.net/35489/viewspace-608036
1、什么是STATISTICS：優化統計信息是為了更詳細的描述數據庫及數據庫對象而收集的數據，這些信息被用于為sql語句選擇最優的執行計劃。優化統計信息包括的項有：
Table statistics（表統計信息）：Number of rows、Number of blocks、Average row length；
Column statistics（列統計信息）：Number of distinct values (NDV) in column、Number of nulls in column、Data distribution (histogram)；
Index statistics（索引統計信息）：Number of leaf blocks、Levels、Clustering factor；
System statistics（系統統計信息）：I/O performance and utilization、CPU performance and utilization。
其中表、列和索引統計信息都可以通過統計信息自動收集功能來收集，系統統計信息在Oracle 10g中只能通過手動收集來完成。
2、如何得到STATISTICS：Orcale 10g中，STATISTICS由GATHER_STATS_JOB作業收集得到，只有當數據庫對象沒有統計信息或者統計信息已經過期（Oracle 10G中是否過期的標準是數據庫對象被修改的記錄行數超過10%，該信息由Modification Monitoring來追蹤完成）時才對該對象進行信息統計，該作業在數據庫創建或升級時由Scheduler自動創建，這些作業可以從視圖DBA_SCHEDULER_JOBS中查到。
如：SELECT d.owner,d.job_name,d.PRogram_name,d.schedule_type,d.comments FROM DBA_SCHEDULER_JOBS d
結果：
默認情況下，Scheduler在維護窗口（maintenance window，默認啟動時間為周內晚上10：00至次日早上6：00及整個周六周日）打開時運行GATHER_STATS_JOB作業，作業GATHER_STATS_JOB則是通過調用系統內部過程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC來完成信息統計的，該過程可根據數據庫對象統計信息需求的優先級（即數據庫對象被修改的多少）按先后順序來完成統計信息收集任務。GATHER_STATS_JOB作業是否隨維護窗口的關閉而關閉則由屬性stop_on_window_close決定，stop_on_window_close的默認值為TRUE，此時GATHER_STATS_JOB作業隨維護窗口的關閉而關閉。統計信息的收集是資源相當密集的工作，因此您可能希望確保它不影響數據庫的正常操作。
統計信息收集還可由過程DBMS_STATS.GATHER_DATABASE_STATS應用GATHER AUTO選項，來完成，與系統地內部過程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC相比，DBMS_STATS.GATHER_DATABASE_STATS不區分數據庫對象統計信息需求的優先級。
非默認情況時，Oracle10g可通過設置初始化參數 STATISTIC_LEVEL，來控制是否啟用統計信息自動收集功能。STATISTIC_LEVEL參數的信息如（表-1）：
參數類型 String
語法 STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}
默認值 TYPICAL
參數類別 動態ALTER session,ALTER SYSTEM
（表-1）
該參數用于控制數據庫統計信息收集的級別。當其為默認值TYPICAL時，系統將自動收集所有主要的有關自身管理的信息以使系統提供最優性能，該值適合于絕大多數情況；當取值ALL時，相對TYPICAL值系統增加timed OS statistics和plan execution statistics兩項信息統計；當取值 BASIC時：有關系統特性和功能的許多信息統計功能都將被關閉（詳細見附錄1）。因此oracle強烈建意參數STATISTICS_LEVEL的值盡量不要設成BASIC。同時當修改參數以語法“alter system set statistics_level='typical';”完成時，修改后的STATISTICS_LEVEL的作用范圍為整個系統，但當使用“ALTER SESSION”時，STATISTICS_LEVEL的作用范圍僅為本SESSION。STATISTIC_LEVEL參數所控制的所有統計、報告功能的狀態信息都可以從視圖V$STATISTICS_LEVEL中得到，具體有關視圖V$STATISTICS_LEVEL各字段的含義可參見網頁http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2135.htm#I1030264。
舉例如下：
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=all';
END;--設置本session statistics_level為‘all’
SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;
結果如下：
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=basic';
END;--設置本session statistics_level為‘basic’
SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;
結果如下：
3、保存以前版本的統計信息在優化器收集統計信息時可能出現原來的優化方法在收集統計信息之前一直工作良好，但是在此之后，由于新收集的統計信息產生了不良計劃，導致查詢突然出錯或效率降低。為避免這種情況，統計信息的收集作業在收集新信息之前保存當前的統計信息。如果出現問題，則可以返回到原有的統計信息，或者通過歷史統計檢查二者之間的不同之處，以解決問題。
例如，假設在 5 月 31 日晚上 10:00 開始運行表 emp 上的統計信息收集作業，而隨后查詢的性能變差。Oracle 保存了原有的統計信息，可以通過執行以下命令重新獲取這些信息：
begin
dbms_stats.restore_table_stats (
'ARUP',
'REVENUE',
'10-DEC-08 10.00.00.000000000 PM -04:00');
end;
此命令將統計信息恢復到 12月 10 日晚上 10:00 為止的統計信息,時間信息是以 TIMESTAMP數據類型提供。歷史統計信息能夠恢復的時間長度是由保留參數所決定的。要查看當前的保留參數，可使用以下查詢：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
表示可以保存相當于 31 天的統計信息，但并不能予以保證。要了解統計信息所覆蓋到的確切時間和日期，只需使用以下查詢：
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
10-DEC-08 09.21.33.594053000 PM -04:00
該查詢表明可用的最陳舊統計信息日期為 12 月 10 日上午9:21。 同時，也可以通過執行內建的函數將保留時間設為不同的值。例如，要將其設為 45 天，可使用：
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)
4、查看統計信息表、索引一擊列的統計信息都存儲在數據字典里，可以通過選取數據字典視圖的某些字段來查看相應統計信息，此處具體涉及到的數據字典視圖見附件2。具體各視圖各字段的含義可見網頁：http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm。
此處就以列統計信息為列，說明查看列統計信息。
列統計信息可以按柱狀統計圖的形式存儲，柱狀統計圖為列數據提供了準確的描述信息，尤其是在數據列有傾斜（列為某種值的記錄行數非常多，而某種值的記錄行數又非常少）的時候。Oracle中包含兩種柱狀統計圖，高度正方圖（height-balanced）和頻率直方圖（frequency histograms），該類型存儲在視圖*TAB_COL_STATISTICS (* 可為USER或 DBA)，其取值為HEIGHT BALANCED, FREQUENCY或 NONE
（1）高度直方圖高度直方圖中，數據列的值被分成組，每組包含的數據數據可數基本相等。比如有一個列c其值在1到100之間，當均勻分布，其列的高度直方圖如下圖：
每個間隔中都包含數據列的10行數據；當不均勻分布時其列直方圖如下圖
這時，大多數數據行的值為5。查詢時最有價值的統計信息就是各個組范圍的兩個端點的值.
查看統計信息的語法如下：
BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'scott', TABNAME => 'EMP',
METHOD_OPT => 'FOR COLUMNS SIZE 6 SAL');
END;
/
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'EMP' AND column_name = 'SAL';
結果：
SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'EMP' and column_name = 'SAL'
ORDER BY endpoint_number;
結果：
上圖中，每行對應高度直方圖中的每個間隔。
（2）頻率直方圖在頻率直方圖中，列中每個唯一數據相當于高度直方圖中的每個間隔，其高度對應該數據在列中出現的次數。當列中distinct值得個數小于或等于直方圖的分格段的個數（即num_buckets的值）時，頻率直方圖會被自動建立。查看頻率直方圖的語法如下：
BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'scott', TABNAME => 'EMP',
METHOD_OPT => 'FOR COLUMNS SIZE 12 SAL');
END;
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'EMP' AND column_name = 'SAL';
結果：
SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'EMP' and column_name = 'SAL'
ORDER BY endpoint_number;
結果：
5、使用統計信息收集功能需注意的問題注意1：上文曾提到過Modification Monitoring功能，其由statistics_level設為‘typical’或‘all’來啟動，而統計信息自動收集功能是依據這些監控信息來判斷是否對該表進行統計信息收集，如果Modification Monitoring功能關閉，則統計信息自動收集就無法確認表的統計信息是否已經過期，因此此時需要手工收集統計信息。
此外，USER_TAB_MODIFICATIONS表中記錄了所有被監控表的數據被更改的信息。該信息的更新將會稍微滯后于真實的修改，可以通過DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存儲過程來立刻將更改的信息更新到USER_TAB_MODIFICATIONS表中。對于更新之后再rollback的記錄，仍然算為已經受影響的記錄，Oracle不會在rollback之后再去更新USER_TAB_MODIFICATIONS表，因此此點需引起注意。
舉例如下（以系統自帶的練習用戶scott中的數據為例，直接在command window中運行）：
SQL> select * from user_tab_modifications where table_name='EMP';
--no rows selected
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> select * from user_tab_modifications where table_name='EMP';
--no rows selected
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SQL> select inserts,updates,deletes from user_tab_modifications where table_name ='EMP';
INSERTS UPDATES DELETES
---------- ---------- ----------
0 14 0
SQL> rollback;
Rollback complete.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SQL> select inserts,updates,deletes from user_tab_modifications where table_name ='EMP';
INSERTS UPDATES DELETES
---------- ---------- ----------
0 14 0
注意2：oracle強烈建意參數STATISTICS_LEVEL的值盡量不要設成BASIC，所以當需要將自動統計信息功能關閉時，最好采用以下方法：
方法一： SYSDBA登錄
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二：以SYSDBA身份登陸
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
Pfile可以直接修改初始化參數文件
然后重新啟動數據庫。
注意3：
不是所有的數據庫對象都適合使用統計信息自動收集功能，如當在如下情況時，統計信息自動收集功能就無法達到需求：
（1） 某些表在工作時間內被刪除（delete）或截斷（truncate）并且被重新創建；
（2） 某些重負荷表在工作時間內被大量修改（insert、update），修改量超過10%。
對于這些修改頻率較高的表，要保持其統計信息不過期，可采用如下兩個方法：
對于（1）可采用下方法：
方法1：將這些表的統計信息設為NULL，這樣Oracle會以查詢優化的一部分來動態的收集這些無統計信息表的統計信息。Oracle動態收集統計信息功能由參數OPTIMIZER_DYNAMIC_SAMPLING控制，并且只有當該參數的值大于或等于2時（默認值為2），Oracle動態收集統計信息功能才能啟動。
要將數據庫對象統計信息設為NULL，只需刪除原有的統計信息，然后給其統計信息功能加鎖，語法如下：
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');--刪除統計信息
DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS'); --不再收集統計信息
END;
類似的加鎖、解鎖還有LOCK_SCHEMA_STATS、LOCK_TABLE_STATS、UNLOCK_SCHEMA_STATS、UNLOCK_TABLE_STATS。
方法2：將數據庫表的最能代表該表狀況的典型統計信息固定，以作為該表優化統計信息。我們可以在任何時候去收集這些典型的信息并將其鎖定，這樣就可達到目的，這樣的統計信息有可能比統計信息自動收集功能在晚上所收集到的統計信息更能表達數據表的狀況。
對問題（2）可采用如下方法：
對于那些修改量較大的表，統計信息的收集最好是能緊跟在修改操作之后，而作為sql程序或者數據庫作業的一部分來手動完成；
注意4：
在下述情況時需要手工來完成統計信息的收集。
(1)對于外部表，其統計信息不會在作業GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS和統計信息自動收集作業中被收集，因為數據操作在外部表上是被禁止的，因此在對應數據改變是須手動收集外部表的統計信息。
（2）當統計信息自動收集功能停用時。
（3）另外還有的就是系統統計信息（system statistics）了。
（4）固定對象，如動態性能表（dynamic performance table）需要被收集當數據庫有明顯操作時，用GATHER_FIXED_OBJECTS_STATS過程。
6、統計信息自動收集功能oracle 11i相比10g的不同（1）Oracle 10g中，可能存在某些情況，你需要用自己的腳本來收集某些特殊對象的統計信息。但是由于你采用了自動收集統計信息，oracle就會對所有對象使用相同的選項來收集統計信息，這樣你就失去了對某個對象的控制權。當你發現缺省的統計信息收集方式對某個對象不是很合適時，你必須鎖定該對象的統計信息，并使用一個特殊的選項值對該對象來收集統計信息。
比如，某個表的列的數據傾斜的非常嚴重，這時如果采用標準的采樣率：ESTIMATE_PERCCENT=AUTO_SAMPLE_SIZE可能就不適合了。這時你就需要單獨指定該對象的采樣率。我們知道，在11g之前的收集統計信息方面，oracle提供的類似的其他選項還包括：CASCADE、DEGREE、METHOD_OPT、NO_INVALIDATE、GRANULARITY。到了11g里，則提供了更大的靈活性，從而使得你可以很簡單的處理上面所說的這種情況。在11g里，上面說的這些選項可以在不同的級別上分別設置，級別由高到低分別為：global級別、數據庫級別、schema級別、表級別。其中，低級別的選項覆蓋高級別的選項。
如，對于上面所舉的例子來說，如果要對你的一個特殊的、列上的值傾斜的很嚴重的表收集統計信息時，你只需要簡單的調用如下的存儲過程來設置該表級別上的的ESTIMATE_PERCCENT=100即可，如下所示：
SQL> exec dbms_stats.set_table_prefs('Schema_name','Table_name','ESTIMATE_PERCCENT','100');
這樣設置以后，當數據庫在自動收集統計信息時，對于其他沒有單獨設置采樣率的表來說，采樣率會采用AUTO_SAMPLE_SIZE，而對于你單獨設置的Table_name表，則會使用100的采樣率來收集統計信息。類似的，如果需要設置global級別上的選項，則調用dbms_stats.set_global_prefs；如果要設置數據庫級別上的選項，則調用dbms_stats.set_database_prefs；如果要設置schema級別上的選項，則調用dbms_stats.set_schema_prefs即可。
（2）11g中，除了上面提到的這些選項以外，還添加了另外三種新的選項：PUBLISH、INCREMENTAL、STALE_PERCENT。其中：
1) PUBLISH：收集完統計信息以后是否立即將統計信息發布到數據字典里，還是將它們存放在私有區域里。TRUE表示立即發布，FALSE表示存放到私有區域里。
2) STALE_PERCENT：確定某個對象的統計信息過時的上限，如果過時就需要重新收集統計信息，缺省為10。計算某個表的統計信息是否過時，oracle會計算自從上一次收集該表的統計信息以來，該表中被修改的數據行數占該表的總行數的百分比。然后用得出的百分比值與該選項配置的值（如果缺省，就是10）進行比較，大于10，則說明該表的統計信息過時了，需要重新收集統計信息；否則就認為該表的統計信息不過時，不用再次收集。
3) INCREMENTAL：在分區表上收集global的統計信息時（將GRANULARITY設置為GLOBAL），采用增量方式完成。使用該選項是因為對于某些分區表來說，比如按照月份進行范圍分區的分區表來說，除了代表當前月的分區里的數據會經常變化以外，其他分區里的數據不會變動。因此在收集該分區表上的global的統計信息時，就沒有必要再次掃描那些非當前月的分區了。如果你將INCREMENTAL設置為TRUE時，則在收集統計信息時，就不會掃描那些非當前月的分區里的數據，而只會掃描當前月的分區里的數據。最后將非當前月的分區上已經存在的統計信息加上當前月新算出來的統計信息合并就得出了分區表的global的統計信息。可以從視圖：DBA_TAB_STAT_PREFS里看到所有的收集統計信息時的各個選項的值。
（3）在Oracle10g版本（包括最新的10.2.0.4）中沒有已知的修改10%這個閥值的方法。但是在Oracle11g中則提供了SET_TABLE_PREFS等函數。
以下命令將指定表的STALE默認值從10%改為5%，該值可以從新的dba_tab_stat_prefs數據字典中查詢獲得。
--僅限于Oracle11g版本
BEGIN
DBMS_STATS.SET_TABLE_PREFS ( wnname =>'scott', tabname =>'EMP', pname =>'SAL', pvalue =>'5');
END;
/
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFE
---------- ---------- -------------------- -----
scott EMP SAL 5
7、待解決問題文章對oracle 10g的統計信息自動收集功能做了詳細的解讀，并附帶了部分的實際實踐。但是由于本地數據庫的數據量非常有限，關于統計信息自動收集功能如何來提高數據庫系統的運行效率方面還缺乏實驗，在后面的學習和開發中將會注意到此點，可能會以實際的應用未基礎來說明統計信息自動收集功能對系統效率的影響。
