#### 檢查統計資訊是否過期
``` sql
--重新整理監控資訊
exec dbms_stats.flush_database_monitoring_info;
--檢查
set lines 200 pages 999
col owner for a20
col table_name for a30
col object_type for a15
select owner,table_name,object_type,stale_stats,to_char(last_analyzed,'yyyy/mm/dd hh24:mi')  last_analyzed from dba_tab_statistics
where stale_stats='YES' 
and owner in (select username from dba_users where created>(select created from v$database));

--or 12c+ 檢查
set lines 200 pages 999
col pdb_name for a15
col owner for a20
col table_name for a30
col object_type for a15
break on pdb_name skip 1
select p.name pdb_name,t.owner,t.table_name,t.object_type,t.stale_stats,to_char(t.last_analyzed,'yyyy/mm/dd hh24:mi')  last_analyzed
from cdb_tab_statistics t,v$pdbs p 
where stale_stats='YES'  and t.con_id=p.con_id
and (t.con_id,t.owner) in (select con_id,username from cdb_users where created>(select created from v$database));

--檢視統計資訊過期原因
select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications
where table_owner='' and table_name='';

--檢查統計資訊過期閾值
select dbms_stats.get_prefs('STALE_PERCENT','MYTEST','T1') stale_percent from dual;

--檢視錶的取樣比例
SELECT DBMS_STATS.GET_PREFS ('estimate_percent', 'mytest','t1') AS "STAT_PREFS" FROM DUAL;

--設定統計資訊過期閾值
exec dbms_stats.set_table_prefs(user,'T1','STALE_PERCENT','5');

--12c+ 檢視優先順序引數
SELECT DBMS_STATS.GET_PREFS ('preference_overrides_parameter', 'mytest','t1') AS "STAT_PREFS" FROM DUAL;
--設定
EXEC DBMS_STATS.SET_TABLE_PREFS ('mytest','t1','preference_overrides_parameter', 'true');
```

使用dbms_stats.gather_*_stats,優先順序如下
- 1.gather_%_stats
- 2.table preference
- 3.global Preference

使用preference_overrides_parameter=true，優先順序
- 1.table preference
- 2.global Preference
- 3.gather_%_stats

#### 收集統計資訊
``` sql
--收集統計資訊,如想立即生效，加 "no_invalidate=>false"
exec dbms_stats.gather_table_stats('MYTEST','T_OBJECTS',degree => 4,cascade => true);  
--收集使用者
begin
dbms_stats.gather_schema_stats('DBO_ZS',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size auto',cascade=>TRUE,degree=>4);
end;
/

--收集資料庫,注意直方圖
begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>8);
end;
/
```

#### 直方圖
```sql
-- 對所有索引列收集直方圖, or 自動收集 for all indexed columns size auto
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',estimate_percent => auto,method_opt => 'for all indexed columns',cascade => true);

--對某一列收集直方圖 自動，只對用過的列收集
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',method_opt => 'for columns size auto DEPTNO',cascade => true,estimate_percent => 100);

--檢視
select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='DEPT';

--檢視錶柱狀圖詳細資訊
col OWNER for a15
col column_name for a20
col ENDPOINT_ACTUAL_VALUE for a20
select * from dba_tab_histograms where table_name='DEPT';
```
#### 遷移統計資訊
```sql
-- eg：遷移使用者
-- 1.建立臨時表
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (ownname => 'scott',stattab => 'opt_stats');
END;
/

--2.手動收集資訊
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');

--3.匯出統計資訊
BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS (ownname => 'scott',stattab => 'opt_stats');
END;
/

-- 4.匯出到dmp
expdp scott/tiger DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats

-- 5.目標端匯入
impdp scott/tiger DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats

--6.匯入統計資訊到資料字典
BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => 'scott',stattab => 'opt_stats');
END;
/

-- 遷移資料庫
--建表
exec dbms_stats.create_stat_table( ownname => 'system',stattab =>'stat_table'); 
--匯出
exec dbms_stats.export_database_stats(stattab=>'stat_table',statown=>'SYSTEM');
--匯入
exec dbms_stats.import_databsae_stats(stattab=>'stat_table',statown=>'SYSTEM');
```

【SCRIPT】Oracle統計資訊相關SQL

相關文章