> 節選自《基於Oracle的SQL最佳化》第五章

```
--收集統計資訊
--估算模式，取樣比例15%
analyze table t2 estimate statistics sample 15 percent for table;

--計算模式,只收集表的
analyze table t2 compute statistics for table;
--收集所有
analyze table t2 compute statistics;

--刪除
analyze index idx_t2 delete statistics;

--針對某列
analyze table t2 compute statistics for columns object_name,object_id;

--一次性收集所有
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);

--分析行遷移，分析索引
analyze table xxx list chained rows into yyy;
analyze index xxx validate structure;
```

###### ANALYZE和DBMS_STATS區別
1. analyze命令不能收集分割槽表統計資訊
2. analyze不能並行收集統計資訊
3. dbms_stats只能收集與cbo有關的統計資訊，其他如行遷移、行連結可以透過analyze分析。

##### 檢視統計資訊

> mos:3112.1   or http://www.dbsnake.net/books --sosi.txt

#### 表的統計資訊

```
--檢視歷史收集的統計資訊
select savtime,rowcnt,blkcnt,avgrln,analyzetime from sys.wri$_optstat_tab_history where obj#=224099;

--truncate 操作後，oracle會自動收集統計資訊
```

#### 索引的統計資訊

```
--降低索引層次
alter index idx_t1 rebuild;
analyze index idx_t1 validate structure;
select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;
```

##### 聚簇引子的含義及重要性

```
--檢視錶位置，檔案號，block號
select id,dbms_rowid.rowid_relative_fno(rowid||'_'||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id;

--手動調整索引的集簇引子
exec dbms_stats.set_index_stats(ownname='IPRA',indname=>'IDX_T1',clstfct => 100000,no_invalidate=>false);
```

#### 列的統計資訊

> 列統計資訊實際存放在 hist_head$中，包含了列的distinct值的數量，null數量、列最大值、最小值等，dba_tab_col_statistics可查

```
--檢視繫結變數的值
select snap_id,
dbms_sqltune.extract_bind(bind_data,1),value_string bind1,
dbms_sqltune.extract_bind(bind_data,2),value_string bind2,
dbms_sqltune.extract_bind(bind_data,3),value_string bind3,
dbms_sqltune.extract_bind(bind_data,4),value_string bind4 from dba_hist_sqlstat
where sql_id='' order by snap_id;

--檢查列統計資訊
select low_value,high_value from dba_tab_col_statistics where table_name='' and column_name='';
--換算成時間
var temp date;
exec dbms_stats.convert_raw_value('LOW_VALUE',:temp);

--謂詞越界，超出了列統計資訊中的範圍。最大值 最小值
```

#### 直方圖

> 在Oracle中，CBO預設認為目標列的資料在其最小值LOW_VALUE和最大值HIGH_VALUE之間是均勻分佈的。 並按照此原則計算成本，基數等。

> 直方圖存在HISTGRM$表中，dba_tab_histograms

##### 直方圖的型別

###### Frequency型別直方圖

> 目標列直方圖的Bucket的數量等於目標列distinct值的數量。 ENDPOINT_VALUE記錄了這些distinct值，ENDPOINT_NUMBER則記錄了到此distinct為止總共有多少記錄，該值為累加記錄。

> Frequency型別直方圖所對應的Bucket數量不能超過254.Oracle12c不再限制，只適用於那些目標列的distinct值數量小於或等於254的情形。

```
--收集直方圖資訊，只有用過的列才會被收集
--eg  auto自動決定使用哪種型別直方圖
select * from t where x='x';
exec dbms_stats.gather_table_stats(ownname='MYTEST',tabname=>'T',method_opt=>'for columns size auto X',cascade=>true);

--可以透過以下檢視檢查
select object_id from dba_objects where object_name='X';
select name,intcol# from sys.col$ where obj#=;
select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=;
select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='';

select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='';
```
> 如果針對文字型別收集直方圖，Oracle只會將該文字型別欄位的文字值的頭32個位元組給取出來（實際上15個位元組），將其轉化為一個浮點數。

###### Height Balanced 型別直方圖

> 目標列直方圖的Bucket的數量會小於目標列的distinct值的數量。

```
--收集直方圖，指定5個Bucket
exec dbms_stats.gather_table_stats(ownname=>'MYTEST',tabname=>'T',method_opt=>'for columns size 5 x',cascade=>true,estimate_percent=>100);

```

##### 直方圖的收集方法

```
--所有有索引列收集直方圖
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto');

--SIZE {integer |REPEAT |AUTO |SKEWONLY}
--只刪除empno的直方圖資訊  integer:1-254,1表示刪除
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns empno size 1');
```

##### 直方圖對CBO的影響

> 當某個列有了直方圖統計資訊後，cbo會人為對該目標列施加的等值查詢條件是一個不安全的為此條件。尤其cursor_sharing設定為similar後。 產生更多硬解析。 shared Cursor影響比較大。

> 再個就是對可選擇率的影響。dba_tab_col_statistics DENSITY的值可以卡看做是對目標列施加等值查詢條件後的可選擇率，在沒有直方圖統計資訊時，值等於1/num_distinct.

##### 使用直方圖注意事項
1. 直方圖是為了準確評估分佈不均勻的列可選擇率、結果的Cardinality被引入的，如果目標列分佈均勻，不需要收集直方圖統計資訊。
2. 對於那些從來沒有在sql語句where條件中出現的列，無需收集
3. 直方圖統計資訊可能會影響Shared Cursor的共享，特別是有繫結變數聯用時。
4. 在配置Oracle 10g引入的自動統計資訊收集作業時，需要特別注意對直方圖統計資訊的收集策略。

#### 全域性統計資訊

> 是隻從直接物件本身這一級收集的統計資訊，只能透過dbms_stats包來收集

- 應使用DBMS_STATS包來對分割槽表收集全域性統計資訊
- 應用 DBMS_STATS包中的相關儲存過程來刪除分割槽表統計資訊
- 收集分割槽表統計資訊時應一致使用GRANULARITY引數。
- 儘量不要使用analyze命令來對分割槽表收集統計資訊，尤其不要與DBMS_STATS聯合使用。

#### 動態取樣

> 為了更準確地估算where條件出現有關聯關係的列時整個where條件的組合可選擇率，進而更準確的估算返回結果集Cardinality，Oracle引入了動態取樣。

兩方面作用
- 估算準確的選擇率
- 可一定程度上解決這種因臨時表沒有統計資訊而導致的CBO選錯執行計劃的問題。

> 動態取樣僅適用於那些針對單表或多表關聯的第一個驅動表的select、update和delete語句。

動態取樣的值(功能預設開啟,optimizer_dynamic_sampling)

- 0:不啟用
- 1：對沒有統計資訊且沒有索引、資料塊總數大於預設取樣資料塊數量
- 2：對沒有統計資訊的表啟用，使用預設取樣資料塊數量
- 3：對沒有統計資訊和有但cbo無法準確估算啟用，預設塊數
- 4：除了滿足3，再加那些針對單表且查詢條件中至少出現了兩列的表啟用。預設塊數
- 5：滿足4，且取樣塊數是2倍預設數
- 6：滿足5, 且取樣塊數是4倍預設數
- 7：滿足6，且取樣塊數是8倍預設數
- 8：滿足7，且取樣塊數是32倍預設數
- 9: 滿足8，且取樣塊數是128倍預設數
- 10：滿足9，取樣時使用所有目標表資料塊。

> 預設取樣塊數為32塊，隱含引數"_OPTIMIZER_DYN_SMP_BLKS"。 hint方式：/*+ dynamic_sampling(t2,4) */

#### 多列統計資訊

> 可以人為指定存在關聯關係的一組目標表上的一個組合列，然後使用DBMS_STATS針對組合列收集統計資訊。

```
select * from t2 where n1=3 and n2=3 and c1='a';
--建立組合列
set serveroutput on
declare
  cg_name varchar2(30);
begin
  cg_name := sys.dbms_stats.create_extended_stats('SOCTT','T2','(n1,n2)');
  dbms_output.put_line(cg_name);
end;
/

--收集統計資訊
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',method_opt=>'for columns(n1,n2) size auto',estimate_percent => 100);
```

#### 系統統計資訊

> 用來描述Oracle資料庫所在伺服器的系統處理能力的輔助統計資訊，主要包括cpu主頻、單塊讀的平均耗費時間、多塊讀的平均耗費時間等。

收集方法
1. 執行DBMS_STATS.GATHER_SYSTEM_STATS('start')開始收集
2. 接著開始實際地使用系統，最好是能以相同典型的負載實際使用一段時間
3. 最後執行DBMS_STATS.GATHER_SYSTEM_STATS('stop').

> 系統統計資訊會存在於檢視 aux_stats$

#### 資料字典統計資訊

```
exec dbms_stats.gather_dictionary_stats;
```

#### 內部物件統計資訊

> x$ 相關表,可透過執行計劃檢視訪問了哪些內部表. 記憶體結構，如果收集統計資訊，rac每個節點都需要執行。

```
exec dbms_stats.gather_fixed_objects_stats;
```

#### Oracle裡的自動統計資訊收集

```
--oracle10g  gather_stats_job
select program_action from dba_scheduler_programs 
where program_name='GATHER_STATS_PROG';

select * from dba_scheduler_wingroup_members 
where window_group_name='MAINTENANCE_WINDOW_GROUP';

--查詢執行時間
select window_name,repeat_interval,duration from dba_scheduler_windows 
where window_name in () and enabled='TRUE';

--檢視是否自願控制，如果沒有，會不限制的消耗資源
select window_name,resource_plan from dba_scheduler_windows;

--檢視實際執行情況
select actual_start_date,job_name,status from
(select *from dba_scheduler_job_run_details where
job_name='GATHER_STATS_JOB' order by log_date desc)
where rownum<=4;
```
缺點：
1. 維護視窗太少，不夠靈活
2. 沒有施加資源控制，過度消耗資源影響資料庫執行

```
--Oracle11g add:GATHER_STATS_PROG
select client_name,task_name,operation_name,status from dba_autotask_task;

--維護視窗
select window_name,autotask_status from dba_autotask_window_clients;

--執行時間
select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE';

--檢視是否資源限制
select window_name,resource_plan from dba_scheduler_windows;

--檢視歷史執行資訊
select * from dba_autotask_job_history
where client_name='auto optimizer stats collection' order by window_start_time desc;

--可以看 建立指令碼
$?/rdbms/admin/catmwin.sql

--自上次自動統計資訊收集後，mon_mods_all$記錄了dml操作的記錄數是否超過目標表在tab$總記錄數的10%
select obj#,inserts,updates,deletes,flags from sys.mon_mods_all$ where obj#=;

--手動監控
exec dbms_stats.flush_database_monitoring_info();
```

#### 禁用

```
--10g all
exec dbms_scheduler.disable('GATHER_STATS_JOB');
--11G ALL
exec dbms_auto_task_admin.disable(
client_name=>'auto optimizer stats collection',
operation=> NULL, window_name=>NULL);

--10g one of all
begin
  dbms_scheduler.disable(
  name=>'WEEKNIGHT_WINDOW',
  force=>TRUE);
end;
/
--11g one of all
begin
  dbms_scheduler.disable(
  name=>'MONDAY_WINDOW',
  force=TRUE);
  end;
  /
  
--oracle10g 預設收集直方圖，修改為止收集之前有直方圖資訊的表
select  dbms_stats.get_param('METHOD_OPT') from dual;
exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

--oracle11g
exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

```

#### Oracle裡應如何收集統計資訊
- 建議額外收集下系統統計資訊
- 系統有大量資料插入、刪除，及時收集
- 新上線系統及時收集
- 日期欄位及時收集統計資訊，避免為此越界

【統計資訊】Oracle統計資訊

相關文章