Oracle 11g 修改表級別的自動收集統計資訊比率

在11g之前，當表的資料量修改超過總資料量的10%，就會晚上自動收集統計資訊。
這個STALE_PERCENT=10%，是無法修改的，如果表非常大，10%是非常多的資料，非常有可能造成統計資訊不準確。
統計資訊收集視窗時間：
11g的是 週一到週五 22：00-2：00 週六週日 6：00-2：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

在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; 
查詢表的當前統計資訊收集比率：
SQL> select dbms_stats.get_prefs('STALE_PERCENT',null,'S') from dual;


DBMS_STATS.GET_PREFS('STALE_PERCENT',NULL,'S')
--------------------------------------------------------------------------------
10


SQL> select to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from DBA_TAB_STATISTICS where OWNER = 'ENTER' and TABLE_NAME = 'S';


TO_CHAR(LAST_ANALYZ
-------------------
2017-09-10 00:26:10


SQL> select * from dba_tab_modifications where table_name='S' and TABLE_OWNER='ENTER';


TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS
------------------------------ ------------------------------ ----------
   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
ENTERPRISE                     S
                                                                  360773
    280456          0 03-OCT-17 NO              0




SQL> SELECT s.table_name,s.num_rows,s.last_analyzed FROM dba_tables s where s.owner='ENTER' and  s.table_name='S' ;


TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
S                              29907073 10-SEP-17

可以看到S表有29907073行資料， INSERT 360773行，UPDATE 280456行資料，因為表的資料量大，10%是非常多的資料，非常有可能造成統計資訊不準確。
於是修改為1%。

進入指定使用者下進行修改：
SQL> conn ENTER/XXX;
Connected.
SQL> exec dbms_stats.set_table_prefs(null,'S','STALE_PERCENT',1);


PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT',null,'S') from dual; 


DBMS_STATS.GET_PREFS('STALE_PERCENT',NULL,'S')
--------------------------------------------------------------------------------
1

S表的統計資訊收集比率改成了1%。