管理Oracle統計資訊

 在CBO(基於代價的最佳化器模式)條件下，SQL語句的執行計畫由統計資訊來決定，若沒有統計資訊則會採取動態採樣的方式決定執行計畫！可以說統計資訊關乎sql的執行計畫是否正確，屬於sql執行的指導思想，oracle的初始化參數statistics_level控制收集統計資訊的層級，有三個參數值:

BASIC :收集基本的統計資訊TYPICAL：收集大部分統計資訊(資料庫的預設設定)ALL：收集全部統計資訊 統計資訊包含：行統計資訊(user_tables)：行數(NUM_ROWS)，塊數(BLOCKS)，行平均長度(AVG_ROW_LEN)；列統計資訊(user_tab_columns)：列中唯一值的數量NUM_DISTINCT），NULL值的數量(NUM_NULLS)，資料分布(HISTOGRAM)；         索引統計(user_index)：--葉塊數量(LEAF_BLOCKS)，等級(BLEVEL)，聚簇因子(CLUSTERING_FACTOR)； 統計資訊的收集時間由時間視窗來調度！

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1T240F50-0.jpg" border="0" alt="" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1T2406254-1.jpg" border="0" alt="" />

 一：10g關閉自動收集統計資訊任務

 

  
begin 
  
 DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 
  
end; 
 

二：11g關閉自動收集統計資訊任務

 

  
BEGIN 
  
DBMS_AUTO_TASK_ADMIN.DISABLE( 
  
client_name => 'auto optimizer stats collection', 
  
operation => NULL, 
  
window_name => NULL); 
  
END; 
 

查詢

 

  
SQL> select client_name ,status from dba_autotask_client; 
  
 
  
CLIENT_NAME                                                      STATUS 
  
 
  
---------------------------------------------------------------- -------- 
  
 
  
auto optimizer stats collection                                  DISABLED 
  
 
  
auto space advisor                                               ENABLED 
  
 
  
sql tuning advisor                                               ENABLED 
 

三：手動收集統計資訊，採樣10%，並行度為8，METHOD_OPT選項代表收集index列分布情況，並產生長條圖

 

  
BEGIN 
  
dbms_stats.gather_schema_stats( 
  
ownname=>'HR', 
  
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', 
  
CASCADE=>TRUE, 
  
ESTIMATE_PERCENT=>10, 
  
DEGREE=>8); 
  
END; 
 

四：查看錶或索引的統計資訊

 

  
select table_name,  
  
       last_analyzed,  
  
       num_rows,  
  
       avg_row_len,  
  
       row_movement， 
  
       monitoring 
  
from dba_tables 
  
where owner = 'HR'; 
  
 
  
select index_name, 
  
       table_name, 
  
       blevel, 
  
       status, 
  
       clustering_factor, 
  
       last_analyzed, 
  
       num_rows, 
  
       leaf_blocks, 
  
       distinct_keys, 
  
       avg_leaf_blocks_per_key 
  
from dba_indexes 
  
where owner = 'HR'; 
 

五：匯出統計資訊

 

  
begin 
  
  dbms_stats.create_stat_table(ownname => 'HR', 
  
  stattab => 'HR_STAT_BAK'); 
  
end; 
  
 
  
begin  
  
  dbms_stats.export_schema_stats(ownname => 'HR', 
  
  stattab => 'HR_STAT_BAK', 
  
  statid => 'N1', 
  
  statown => 'HR'); 
  
end; 
  
 
  
select * from HR_STAT_BAK； 
 

六：刪除統計資訊

 

  
begin 
  
  dbms_stats.delete_schema_stats(ownname => 'HR'); 
  
end; 
 

七：匯入統計資訊

 

  
begin 
  
  dbms_stats.import_schema_stats(ownname => 'HR', 
  
  stattab => 'HR_STAT_BAK', 
  
  statid => 'N1', 
  
  statown => 'HR'); 
  
end; 
 

八：異機匯入，首先需要將HR_STAT_BAK表通過expdp/impdp方式匯入到其他oracle伺服器上

 

  
SQL> exec dbms_stats.upgrade_stat_table('HR','HR_STAT_BAK'); 
  
PL/SQL 過程已成功完成。 
  
 
  
SQL> EXEC dbms_stats.import_schema_stats(ownname => 'HR',stattab => 'HR_STAT_BAK',statid => 'N1',statown=>'HR'); 
  
PL/SQL 過程已成功完成。 
 

九：鎖定與解鎖統計資訊

 

  
1：鎖定統計資訊 
  
begin 
  
 dbms_stats.lock_schema_stats(ownname=>'HR'); 
  
end; 
  
 
  
2：查詢哪些表或索引的統計資訊被鎖定 
  
select * from user_tab_statistics where stattype_locked='ALL'; 
  
select * from user_ind_statistics where stattype_locked='ALL'; 
  
 
  
3：如果在鎖定條件下收集統計資訊，則會出現如下報錯 
  
ERROR at line 1: 
  
ORA-20005: object statistics are locked (stattype = ALL) 
  
ORA-06512: at "SYS.DBMS_STATS", line 20337 
  
ORA-06512: at "SYS.DBMS_STATS", line 20360 
  
ORA-06512: at line 1 
  
 
  
4：解鎖統計資訊 
  
begin 
  
 dbms_stats.lock_schema_stats(ownname=>'HR'); 
  
end; 
 

 

 

本文出自 “斬月” 部落格，謝絕轉載！