本篇文章为大家展示了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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
