dbms_stats
DBMS_STATS.GATHER_TABLE_STATS的文法如下:
DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
參數說明:
ownname:要分析表的擁有者
tabname:要分析的表名.
partname:分區的名字,隻對分區表或分區索引有用.
estimate_percent:采樣行的百分比,取值範圍[0.000001,100],null為全部分析,不采樣. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是預設值,由oracle決定最佳取采樣值.
block_sapmple:是否用塊采樣代替行采樣.
method_opt:決定histograms資訊是怎樣被統計的
通過設定 method_opt參數可以智能地生成直方圖,具體取值如下:
for all columns:10g預設值(根據版本的不同，預設值也會有所差異)，統計所有列的histograms.
for all indexed columns:統計所有indexed列的histograms.
for all hidden columns:統計你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:統計指定列的histograms.N的取值範圍[1,254];
REPEAT上次統計過的histograms;
AUTO由oracle決定N的大小;
SKEWONLY選項會耗費大量處理時間，因為它要檢查每個索引中的每個列的值的分布情況。
假如dbms_stat發現一個索引的各個列分布得不均勻，就會為那個索引建立直方圖，幫助基于代價的SQL優化器決定是進行索引通路，還是進行全表掃描通路。
degree:決定并行度.預設值為null.
granularity:設定分區表收集統計資訊的粒度，分别有
all：對表達全局，分區，子分區的資料都做分析
auto：Oracle根據分區的類型，自動決定做哪一種粒度的分析
global：隻做全局級别的分析
global and partition：隻對全局和分區級别做分析，對子分區不做分析，這是和all的一個差別
partition：隻做分區級别做分析
subpartition：隻做子分區做分析
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T3', GRANULARITY => 'SUBPARTITION', CASCADE => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T2', GRANULARITY => 'PARTITION', CASCADE => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T1', GRANULARITY => 'GLOBAL', CASCADE => TRUE);
其中，T1為全表，T2為分區，T3為子分區
cascace:是收集索引的資訊.預設為falase.
stattab指定要存儲統計資訊的表；statid如果多個表的統計資訊存儲在同一個stattab中用于進行區分；statown存儲統計資訊表的擁有者。以上三個參數若不指定,統計資訊會直接更新到資料字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表鎖住了也收集統計資訊.
dbms_stats的使用
dbms_stats包除了gather_table_stats過程外還有如下過程
EXPORT_COLUMN_STATS：導出列的分析資訊
EXPORT_INDEX_STATS：導出索引分析資訊
EXPORT_SYSTEM_STATS：導出系統分析資訊
EXPORT_TABLE_STATS：導出表分析資訊
EXPORT_SCHEMA_STATS：導出方案分析資訊
EXPORT_DATABASE_STATS：導出資料庫分析資訊
IMPORT_COLUMN_STATS：導入列分析資訊
IMPORT_INDEX_STATS：導入索引分析資訊
IMPORT_SYSTEM_STATS：導入系統分析資訊
IMPORT_TABLE_STATS：導入表分析資訊
IMPORT_SCHEMA_STATS：導入方案分析資訊
IMPORT_DATABASE_STATS：導入資料庫分析資訊
GATHER_INDEX_STATS：分析索引資訊
GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊
GATHER_SCHEMA_STATS：分析方案資訊
GET_COLUMN_STATS：擷取字段的統計資訊
GET_SYSTEM_STATS：擷取系統的統計資訊
GET_INDEX_STATS：擷取索引的統計資訊
GET_TABLE_STATS：擷取表的統計資訊
SET_COLUMN_STATS：設定字段的統計資訊。通常應用在測試環境，也不排除在極端情況下起到奇效。
SET_SYSTEM_STATS：設定系統的統計資訊
SET_INDEX_STATS：設定索引的統計資訊
SET_TABLE_STATS：設定表的統計資訊
DELETE_COLUMN_STATS：删除字段的統計資訊
DELETE_SYSTEM_STATS：删除系統的統計資訊
DELETE_INDEX_STATS：删除索引的統計資訊
DELETE_TABLE_STATS：删除表的統計資訊
DELETE_DATABASE_STATS：删除資料庫的統計資訊
DELETE_DICTIONARY_STATS：删除資料字典的統計資訊
DELETE_SCHEMA_STATS：删除使用者方案的統計資訊
DELETE_FIXED_OBJECTS_STATS：删除固定對象的統計資訊
GATHER_SCHEMA_STATS：分析方案資訊
GATHER_DATABASE_STATS：分析資料庫資訊
GATHER_SYSTEM_STATS：分析系統資訊
CREATE_STAT_TABLE：建立存放統計資訊的表
DROP_STAT_TABLE：删除存放統計資訊的表
LOCK_TABLE_STATS：鎖定表的統計資訊。當覺得目前統計資訊非常好，且表資料幾乎不變化時，可以考慮鎖定統計資訊，鎖定之後相關的所有資料分析，包括表級，列級，直方圖、索引的分析都将被鎖定，不允許被更新。
LOCK_SCHEMA_STATS：鎖定使用者方案的統計資訊
UNLOCK_TABLE_STATS：解鎖表的統計資訊
UNLOCK_SCHEMA_STATS：解鎖使用者方案的統計資訊
RESTORE_SYSTEM_STATS：還原系統的統計資訊
RESTORE_INDEX_STATS：還原索引的統計資訊
RESTORE_TABLE_STATS：還原表的統計資訊
RESTORE_DATABASE_STATS：還原資料庫的統計資訊
RESTORE_DICTIONARY_STATS：還原資料字典的統計資訊
RESTORE_SCHEMA_STATS：還原使用者方案的統計資訊
RESTORE_FIXED_OBJECTS_STATS：還原固定對象的統計資訊
統計資訊還原過程如下
通過dbms_stats.get_stats_history_availability查找分析資料恢複到最早時間點，隻有在這個時間點之後的分析資料才可以被恢複。
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
12-MAR-12 10.58.17.552941000 AM +08:00
檢視最後一次分析表T的時間
SQL> select last_analyzed from user_tables where table_name='T';
LAST_ANALYZED
------------------
12-APR-12
恢複表T的統計資訊
SQL> exec dbms_stats.rEstore_table_stats('HR','T','11-APR-12 10.58.17.552941000 AM +08:00');
PL/SQL procedure successfully completed.
再次檢視最後一次分析表T的時間，恢複成功
SQL> select last_analyzed from user_tables where table_name='T';
LAST_ANALYZED
------------------
11-APR-12
我們在收集統計資訊時，有可能由于統計資訊收集錯誤，而額導緻性能下降，這時我們就要儲存之前收集的統計資訊來快速恢複統計資訊。下面就通過具體案例來貫穿dbms_stats的使用
1、首先建立一個分析表，該表是用來儲存之前的分析值：
SQL> exec dbms_stats.create_stat_table('HR',stattab=>'STAT_TABLE');
PL/SQL procedure successfully completed.
2、收集表的統計資訊:
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'FOR ALL INDEXED COLUMNS',DEGREE=>4,CASCADE=>TRUE);
PL/SQL procedure successfully completed.
3、導出表分析資訊到stat_table中
SQL> select count(*) from stat_table;
COUNT(*)
----------
SQL> exec dbms_stats.export_table_stats(ownname=>'HR',TABNAME=>'T',STATTAB=>'STAT_TABLE');
PL/SQL procedure successfully completed.
SQL> select count(*) from stat_table;
COUNT(*)
----------
4
4、删除分析資訊
SQL> exec dbms_stats.delete_table_stats(ownname=>'HR',TABNAME=>'T');
PL/SQL procedure successfully completed.
SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner = 'TEST'
AND table_name = 'T1';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
沒有查到分析資料
5、導入統計資訊
SQL> exec dbms_stats.import_table_stats(ownNAME=>'HR',TABNAME=>'T',STATTAB=>'STAT_TABLE');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,empty_blocks,avg_space,chain_cnt from user_tables where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT
---------- ---------- ------------ ---------- ----------
10104 20 0 0 0
可以查到分析資料
ANALYZE
analyze文法如下
ANALYZE
{ TABLE [ schema.]table
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| INDEX [ schema. ]index
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| CLUSTER [ schema. ]cluster
}
{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]
| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]
| validation_clauses
| LIST CHAINED ROWS [ into_clause ]
| DELETE [ SYSTEM ] STATISTICS
} ;
PARTITION | SUBPARTITION：對分區表或索引進行分析
CLUSTER cluster:對簇進行分析，分析的結果會放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS.
compute_statistics_clause
文法：COMPUTE [ SYSTEM ] STATISTICS [for_clause]
對分析對像進行精确的統計，然後把資訊存儲的資料字典中。可以選擇對表或對字段進行分析。
computed和estimated這兩種方式的統計資料都被優化器用來影響sql的執行計劃
如果指定system選項就隻統計系統産生的資訊
for_clause
FOR TABLE：隻統計表
FOR COLUMNS：隻統計某個字段
FOR ALL COLUMNS：統計所有字段
FOR ALL INDEXED COLUMNS：統計索引的所有字段，如
analyze table t compute statistics for table for all indexed columns size 25; #size為直方圖的桶數
estimate_statistics_clause
ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]
隻是對部分行做一個大概的統計。适用于大表
SAMPLE：指定具體統計多少行，如果忽略這個參數的話，oracle會預設為1064行
ROWS causes：行數 Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1.
PERCENT causes：百分數，如
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
validation_clauses
分析REF(遊标,動态關聯結果集的臨時對象)或是對像的結構，如
ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE customers VALIDATE REF UPDATE;
analyze的限制
不可以分析資料字典表
Oracle 9i中不可以分析外部表，但可以用DBMS_STATS來實作這個目的
不可以分析臨時表
不可以計算或估計下列字段類型
REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.
統計資訊相關的視圖
對索引進行分析後，分析的結果預設會放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中
分析的内容：
Depth of the index from its root block to its leaf blocks (BLEVEL)
Number of leaf blocks (LEAF_BLOCKS)
Number of distinct index values (DISTINCT_KEYS)
Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY)
Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)
Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)
對表進行分析後，分析的結果預設會放在USER_TABLES, ALL_TABLES, and DBA_TABLES表中，在分析表的時候，oracle也會分析基于函數的index所引用的表達式
分析的内容：
Number of rows (NUM_ROWS) *
Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to
receive data, regardless whether they currently contain data or are empty) (BLOCKS)
* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) Average available free
space in each data block in bytes (AVG_SPACE)
Number of chained rows (CHAIN_COUNT) Average row length, including the row's overhead, in bytes (AVG_ROW_LEN)
USER_TAB_COL_STATISTICS：用于存儲與列相關的統計資訊。
USER_HISTOGRAMS :用于存儲與直方圖相關的統計資訊。
dbms_stats和analyze的使用場景
自dbms_stats推出後，Oracle就強烈建議在收集CBO統計資訊時用dbms_stats替代analyze，原因如下：
1、dbms_stats可以并行分析,可以并行進行，對多個使用者，多個Table
2、dbms_stats有自動分析的功能(alter table monitor )
3、analyze 分析分區表時統計資訊不準确
4、對于oracle 9裡面的External Table，Analyze不能使用，隻能使用DBMS_STATS來收集資訊。
關于第3點原因是，dbms_stats會實在的去分析表全局統計資訊（當指定參數）；而analyze是将表分區（局部）的statistics 彙總計算成表全局statistics ,可能導緻誤差。
如果想分析整個使用者或資料庫，還可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以後提供的工具包)，如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
既然dbms_stats相對于analyze有如此之多的優勢，是否可以完全廢棄analyze指令呢？答案是否定的，現在關于analyze的定位Oracle解釋：
Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer,for example:
1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3、Identify migrated and chained rows of a table or cluster.
可以看到analyze已經不是用來收集與CBO相關的統計資訊了，而側重于對象結構的分析。故通常我們會這樣使用analyze:
1、通過Validate Structure來分析對象的結構資訊, dbms_stats還不能分析對象結構
2、收集CHAINED ROWS, 收集CLUSTER TABLE的資訊，這兩個仍舊需要使用Analyze語句。可以通過analyze….list chained rows收集塊中行連結的資訊到chained_rows表中。
注：必須先在執行analyze語句所在的schema内執行$ORACLE_HOME/rdbms/admin/utlchain.sql(或utlchn1.sql)腳本建立chained_rows表。在chained_rows建立之後﹐才能收集行連結資訊
3、Analyze table compute statistics 收集表上的 empty_blocks、avg_space 空間使用資訊
4、Analyze Cluster 收集簇的資訊，其實cluster上唯一可統計的資訊是DBA_CLUSTERS.AVG_BLOCKS_PER_KEY(Number of blocks in the table divided by number of cluster keys) ，
三個注意點
1、當某個索引處于monitoring usage的時候，如果使用dbms_stats去分析表并且同時分析索引，oracle會調用gather_index_stat來分析索引，需要用到索引名，故會将該索引的v$object_usage.USED設定為TRUE。analyze 雖然分析了索引，但是其實隻需要obj#，不會将索引狀态設定為USE = TRUE
2、dbms_stats無法分析cluster表，分析cluster表仍然需要analyze
3、如果無法執行analyze指令，請驗證是否建立了 存放驗證資訊的表，建立指令如下
@?/rdbms/admin/utlvalid.sql
![SQL性能調優--去除索引函數修改時間判斷邏輯[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)