dbms_stats與analyze分析彙總

dbms_stats與analyze分析彙總
綜合整理，尤其感謝下面兩個連結：
http://czmmiao.iteye.com/blog/1483190
http://blog.163.com/donfang_jianping/blog/static/136473951201322303541549/


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);

常用語句：
exec dbms_stats.gather_schema_stats(ownname => 'tb',degree => 2);
exec dbms_stats.gather_table_stats(ownname => 'tb',tabname => 'obj3' ,degree => 2,cascade => true);


引數說明:
ownname:要分析表的擁有者
tabname:要分析的表名.
partname:分割槽的名字,只對分割槽表或分割槽索引有用.
estimate_percent:取樣行的百分比,取值範圍[0.000001,100],null為全部分析,不取樣. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是預設值,由oracle決定最佳取取樣值.
block_sapmple:是否用塊取樣代替行取樣.
degree:決定並行度.預設值為null.


使用method_opt建立直方圖：
method_opt控制如何建立列上的統計資訊。預設值為“for all columns size auto“，表示允許Oracle來選擇需要收集直方圖的列，併為直方圖設定合適的桶大小。
Oracle根據資料的基數和列是否在SQL快取的執行計劃中出現（即是否在過慮條件中其他謂語中出現）來建立直方圖。這種方法可能在大多數情況下執行很好，但是存在下面的一些問題：
除非所有的SQL都在快取區內，否則會缺少直方圖。Oracle在建立一個直方圖前，在列中的資料的分佈的理解是不完整的，這可能會導致一個不好的結果，也就是說Oracle在直方圖存在的情況，才意識到需要一個直言圖。
在這些情況下，我們需要手動進行直方圖的建立，

exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all indexed columns size auto')
它使用了索引，並達到了直方圖預期的目標。
關於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];
FOR [ALL {INDEXED|HIDDEN}] COLUMNS [ column_expression] [size_clause] [,[ column_expression] [size_clause] ]
column_expresson通常是一個列名。size_claues按照這個語法：SIZE [bucket_size | repeat | auto | skewonly]，
bucket_size：字義直方圖桶的數量。repeat：只更新已存在的直方圖。auto：確定是否建立珍上直方圖並根據傾斜的指示設定桶的大小，快取中的SQL可能會受益於該直方圖。skewonly：只有存在的列是傾斜的情況下才建立直方圖，與auto的區別就是不考慮快取中的SQL。

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過程外還有如下過程
GATHER_SCHEMA_STATS：分析方案資訊
GATHER_DATABASE_STATS：分析資料庫資訊
GATHER_SYSTEM_STATS：分析系統資訊
GATHER_INDEX_STATS：分析索引資訊
GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊
GATHER_SCHEMA_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：刪除固定物件的統計資訊

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：匯入資料庫分析資訊

GET_COLUMN_STATS：獲取欄位的統計資訊
GET_SYSTEM_STATS：獲取系統的統計資訊
GET_INDEX_STATS：獲取索引的統計資訊
GET_TABLE_STATS：獲取表的統計資訊

SET_COLUMN_STATS：設定欄位的統計資訊。通常應用在測試環境，也不排除在極端情況下起到奇效。
SET_SYSTEM_STATS：設定系統的統計資訊
SET_INDEX_STATS：設定索引的統計資訊
SET_TABLE_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');
再次檢視最後一次分析表T的時間，恢復成功
並不能恢復到指定的時間，只能在指定的時間之前的某個點上；
檢視當前的時間格式；
 select systimestamp from dual;
exec dbms_stats.restore_table_stats(ownname => 'TB',tabname => 't1',as_of_timestamp => '10-3月 -16 11.02.32.705660 上午 +08:00');
 

我們在收集統計資訊時，有可能由於統計資訊收集錯誤，而額導致效能下降，這時我們就要儲存之前收集的統計資訊來快速恢復統計資訊。下面就透過具體案例來貫穿dbms_stats的使用
1、首先建立一個分析表，該表是用來儲存之前的分析值：
SQL> exec dbms_stats.create_stat_table('HR',stattab=>'STAT_TABLE');

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);

3、匯出表分析資訊到stat_table中
SQL> select count(*) from stat_table;
  COUNT(*)
----------
         0
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');

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');

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
可以查到分析資料


統計資訊記錄了表、分割槽和索引中的資料列和資料分佈。
統計資訊表
dba_tab_statistics
dba_ind_statistics
dba_tab_col_statistics
表的統計資訊歷史表
dba_tab_stats_history

select * from dba_ind_statistics i where i.OWNER='TB';
select * from dba_tab_statistics s where s.TABLE_NAME='OBJ3';
SELECT * FROM DBA_TAB_COL_STATISTICS s where s.owner='TB';
select * from dba_tab_stats_history s where s.TABLE_NAME='OBJ3';

相關關鍵欄位解釋如下：
avg_cache_hit_ratio：適用於表、索引、分割槽，表示物件的平均快取命中率，在檢索時，物件的塊在快取區快取記憶體中被發現的慨率。
avg_cached_block：適用於表，索引，分割槽，表示緩衝區快取記憶體中出現物件塊的平均數量。
avg_col_len：表示列的平均長度（單位為位元組數）。
avg_data_blocks_per_key：適用於索引，表示每個索引鍵對應資料塊的平均數量。
avg_leaf_blocks_per_key：表示每個索引鍵對應葉子塊的平均數量。
avg_row_len：適用於表、分割槽，表示平均記錄的長度，包含開銷。
avg_space：適用於表、分割槽、索引，表示物件可用的平均空閒空間。
blevel：表示B*樹索引的層級數。
blocks：物件已使用塊的數量。
clustering_factor：表示對於鍵值對應記錄在表中排序程度的一種衡量方法。如果鍵值都打包在一起的，那麼聚簇因子將會比較低，如果聚簇鍵是隨機分佈的，聚簇因子將會比較高。它的最大值為表中的記錄數，最小值為資料塊的個數。
density：表示列的密度。這是一個判斷列選擇性的衡量指標，如果值為0，表示非常有選擇性，如果為1，表示沒有選擇懷，如果它沒有直方圖，這個值等於1/不同值的數量，如果含有直方圖，那麼將會根據直方圖的計算方式來計算。
enpty_blocks：適用於表、分割槽，表示物件中空塊的數量。
high_value：表示列中的最大值。
leaf_blocks：表示索引中葉子塊的數量。
low_value：列中的最小值。
num_distinct：列中不同值的數量。
num_nulls：列中空值的數量。
num_rows：表示表、分割槽、或索引中包含記錄數。


系統的統計資訊可以使用dbms_stats.gather_system_stats進行收集。可以在sys.aux_stats$檢視中進行系統統計資訊的檢視，主要包括如下資訊：
非工作負載統計：
CPUSPEEDNW：系統上每個CPU每秒鐘可以執行的標準操作的次數。字尾NW表示非工作負載下的統計。
IOSEEKTIM：在磁碟上定位資料的平均時間，雖然被稱為尋找道（seek）時間，但實際上是從磁碟檢索一個資料塊的總時間。包含磁碟的旋轉延時，傳輸與尋道時間。
IOTFRSPEED：磁碟的傳輸速率（單位：b/ms）。
工作負載統計：
SREDTIM：執行單塊讀取的平均時間。
MREADTIM：執行多塊讀取的平均時間。
CPUSPEED：系統上每個CPU每秒鐘可以執行的標準操作的次數




修改收集統計資訊的部分引數
exec dbms_stats.set_global_prefs(pname=>'degree',pvalue=>'2');
exec dbms_stats.set_global_prefs(pname=>'method_opt',pvalue=>'for all columns size auto');



設定自動統計的狀態開關
對於物件統計資訊的收集可以自動收集也可以手動收集。
自動收集，我們可以透過dbms_auto_task_admin包來禁止用或啟動，如下：
 begin
    dbms_auto_task_admin.disable(
        client_name=>'auto optimizer stats collection',
        operation=>null,
        window_name=>null);
 end;
 /


 begin
     dbms_auto_task_admin.enable(
        client_name=>'auto optimizer stats collection',
        operation=>null,
        window_name=>null);
  end;
  /
說明：使用disable進行禁用，用enable進行啟用。
檢視當前自動統計任務狀態；
  select * from dba_autotask_client ;
10g和11g的自動統計分析任務依然是呼叫的dbms_stats.gather_database_stats_job_proc這個內部的儲存過程。

在oracle 11g後自動統計分析的任務每次執行時都會生成一個ORA$AT_OS_OPT_XXX的作業，可透過檢視dba_scheduler_job_run_details檢視來檢視執行狀態。

只有對應的task正在執行時，dba_autktask_client_job檢視中才有資料：
檢視autotask對應的window_name的開啟狀態。

SQL> select * from dba_autotask_window_clients;


總結一下統計資訊自動收集任務執行的步驟：
首先是dba_autotask_task-->dba_autotask_client建立自動執行任務
再根據時間視窗及資源組建立自動執行作業dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows（檢視自動統計資訊任務對應的window_groups、window_name資訊）
-->dba_autotask_job_history-->dba_scheduler_job_run_details(檢視自動統計資訊任務生成job的歷史執行狀態）
-->dba_scheduler_programs （檢視自動統計資訊任務對應的program_name,program_action）


當一張表沒有統計資訊時，比較大表，或當預估出SQL消耗的時間很長，這個時候， 我們可以考慮使用動態取樣。動態取樣透過引數optimizer_dynamic_sampling來設定，也可以在SQL使用dynamic_samping提示來進行，它的值從0到10中的一個數，如果為0，表示不進行動態取樣，如果設定為10，表示當SQL涉及到的表的統計資訊丟失或不完整，或SQL在兩個或兩個以上的列中有過慮條件時，系統將讀取表的所胡塊。此引數的預設值為2，它表示指示最佳化器對沒有分析的表進行少量的統計資訊收集。
22:11:46 SQL> show parameter optimizer_dynamic_sampling

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling         integer     2





 

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 [ 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