本篇文章為大家展示了oracle自動統計信息時間的修改過程是怎樣的，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。
今天是2022年1月10日今天值夜班，同事讓給優化一個sql，優化完成后，順便看了下新系統的統計信息情況，發現在晚上做數據采集的時間，系統資源增加，發現是統計信息在跑，在模擬環境測試，特此記錄。
- trc get trace path - undo show undo info - user | users list all users info - version show database version - xo <sql_id> [phv] xplan.display_awr for given sql_id (add execution order column) - xpo <sql_id> [child_number] xplan.display_cursor for given sql_id(add execution order column) - xp <sql_id> display_cursor for given sql_id - x <sql_id> display_awr for given sql_id NOTE ================ - Set environment variable DBUSER to change default connect string which is "/ as sysdba" - Set environment variable ORA_TMP to the default temp directory (default if /tmp when not set) [oracle@rhys ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 7 01:25:45 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@rhys> col REPEAT_INTERVAL for a60 SYS@rhys> set linesize 200 SYS@rhys> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP'; WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------- FRIDAY_WINDOW freq=daily;byday=FRI;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 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;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 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 7 rows selected. SYS@rhys>
查看狀態：
SYS@rhys> select client_name,status from dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED SYS@rhys>
更改執行時間：
SYS@rhys> begin 2 dbms_scheduler.disable( name => 'SUNDAY_WINDOW', force => TRUE); 3 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0'); 4 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 5 dbms_scheduler.enable( name => 'SUNDAY_WINDOW'); 6 dbms_scheduler.disable( name => 'SATURDAY_WINDOW', force => TRUE); 7 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0'); 8 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 9 dbms_scheduler.enable( name => 'SATURDAY_WINDOW'); 10 dbms_scheduler.disable( name => 'FRIDAY_WINDOW', force => TRUE); 11 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."FRIDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0'); 12 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."FRIDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 13 dbms_scheduler.enable( name => 'FRIDAY_WINDOW'); 14 dbms_scheduler.disable( name => 'THURSDAY_WINDOW', force => TRUE); 15 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."THURSDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0'); 16 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 17 dbms_scheduler.enable( name => 'THURSDAY_WINDOW'); 18 dbms_scheduler.disable( name => 'WEDNESDAY_WINDOW', force => TRUE); 19 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0'); 20 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 21 dbms_scheduler.enable( name => 'WEDNESDAY_WINDOW'); 22 dbms_scheduler.disable( name => 'TUESDAY_WINDOW', force => TRUE); 23 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0'); 24 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 25 dbms_scheduler.enable( name => 'TUESDAY_WINDOW'); 26 dbms_scheduler.disable( name => 'MONDAY_WINDOW', force => TRUE); 27 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0'); 28 DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(240, 'minute')); 29 dbms_scheduler.enable( name => 'MONDAY_WINDOW'); 30 end; 31 / PL/SQL procedure successfully completed. SYS@rhys> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP'; WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------- FRIDAY_WINDOW freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0 +000 04:00:00 MONDAY_WINDOW freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0 +000 04:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0 +000 04:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0 +000 04:00:00 7 rows selected. SYS@rhys>
更改完成。注意：每個schedule任務需要disable和enable之后才生效。
附：以下腳本可把Oracle自動統計信息收集周一到周五的時間窗口從22點調整為2點。
begin dbms_scheduler.disable(name => 'MONDAY_WINDOW'); dbms_scheduler.set_attribute(name => 'MONDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0'); dbms_scheduler.enable(name => 'MONDAY_WINDOW'); end; / begin dbms_scheduler.disable(name => 'TUESDAY_WINDOW'); dbms_scheduler.set_attribute(name => 'TUESDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=TUE;byhour=2;byminute=0; bysecond=0'); dbms_scheduler.enable(name => 'TUESDAY_WINDOW'); end; / begin dbms_scheduler.disable(name => 'WEDNESDAY_WINDOW'); dbms_scheduler.set_attribute(name => 'WEDNESDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=WED;byhour=2;byminute=0; bysecond=0'); dbms_scheduler.enable(name => 'WEDNESDAY_WINDOW'); end; / begin dbms_scheduler.disable(name => 'THURSDAY_WINDOW'); dbms_scheduler.set_attribute(name => 'THURSDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=THU;byhour=2;byminute=0; bysecond=0'); dbms_scheduler.enable(name => 'THURSDAY_WINDOW'); end; / begin dbms_scheduler.disable(name => 'FRIDAY_WINDOW'); dbms_scheduler.set_attribute(name => 'FRIDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=FRI;byhour=2;byminute=0; bysecond=0'); dbms_scheduler.enable(name => 'FRIDAY_WINDOW'); end; /
上述內容就是oracle自動統計信息時間的修改過程是怎樣的，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
