Oracle 11g系統自己主動收集統計資訊的一些知識

標籤：

    在11g之前，當表的資料量改動超過總資料量的10%，就會晚上自己主動收集統計資訊。怎樣推斷10%。之前的文章有研究過：oracle自己主動統計資訊的收集原理及實驗。這個STALE_PERCENT=10%，是無法改動的，假設表很大。10%是許多的資料，很有可能造成統計資訊不準確。

    在11g之後，STALE_PERCENT=10%是能夠改動的，分為全域（DBMS_STATS.SET_GLOBAL_PREFS ）和表層級（DBMS_STATS.SET_TABLE_PREFS）。表層級的設定：

    改動為5%（範圍從1-100）: exec dbms_stats.set_table_prefs(null,‘table_name‘,‘STALE_PERCENT‘,5);
    恢複為10%: exec dbms_stats.set_table_prefs(null,‘table_name‘,‘STALE_PERCENT‘,null);
    查詢百分比:select dbms_stats.get_prefs(‘STALE_PERCENT‘,null,‘table_name‘) from dual;

---11g的是 周一到周五 22：00-2：00 周六周日 6：00-4：00

SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name
AND c.optimizer_stats = ‘ENABLED‘;
WINDOW_NAME          REPEAT_INTERVAL                                              DURATION
-------------------- ------------------------------------------------------------ ---------------
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

--禁用自己主動收集
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection‘,operation => NULL,window_name => NULL);
--啟用自己主動收集
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => ‘auto optimizer stats collection‘,operation => NULL,window_name => NULL);
--查看job

select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

--收集當前資料庫下全部使用者的統計資訊
exec dbms_stats.gather_database_stats(user);
--收集當前資料庫使用者下全部對象的統計資訊
exec dbms_stats.gather_schema_stats(user);
--收集資料字典的統計資訊
exec dbms_stats.gather_dictionary_stats();

--當系統有非常大的分區表時，假設總是所有收集則會比較慢。11g之後能夠設定INCREMENTAL僅僅對資料有變動的分區做收集

exec dbms_stats.set_table_prefs(user,‘table_name‘,‘INCREMENTAL‘,‘TRUE‘);--僅僅收集資料變動的分區

exec dbms_stats.set_table_prefs(user,‘table_name‘,‘INCREMENTAL‘,‘FALSE‘);--都要收集

select dbms_stats.get_prefs(‘INCREMENTAL‘,null,‘table_name‘) from dual;--查看分區表INCREMENTAL的值

--擷取global的統計資訊收集設定選項
select dbms_stats.get_prefs(‘method_opt‘) from dual;
select dbms_stats.get_prefs(‘concurrent‘) from dual; 
select dbms_stats.get_prefs(‘GRANULARITY‘) from dual;

select dbms_stats.get_prefs(‘INCREMENTAL‘) from dual;
--設定global的統計資訊收集選項

exec DBMS_STATS.SET_PARAM(‘DEGREE‘,4);

exec DBMS_STATS.SET_PARAM(‘INCREMENTAL‘,‘TRUE‘);

Oracle 11g系統自己主動收集統計資訊的一些知識