oracle自动统计信息时间的修改过程是怎样的

本篇文章为大家展示了oracle自动统计信息时间的修改过程是怎样的，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

今天是2022年1月10日今天值夜班，同事让给优化一个sql，优化完成后，顺便看了下新系统的统计信息情况，发现在晚上做数据采集的时间，系统资源增加，发现是统计信息在跑，在模拟环境测试，特此记录。

      - trc                                     get trace path            - undo                                    show undo info      - user | users                            list all users info      - version                                 show database version      - xo   [phv]                      xplan.display_awr for given sql_id (add execution order column)      - xpo  [child_number]             xplan.display_cursor for given sql_id(add execution order column)      - xp                              display_cursor for given sql_id      - x                               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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@rhys> col REPEAT_INTERVAL for a60SYS@rhys> set linesize 200SYS@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:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00WEDNESDAY_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                                  ENABLEDauto space advisor                                               ENABLEDsql 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:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0         +000 04:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0         +000 04:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0         +000 04:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0         +000 04:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0         +000 04:00:00WEDNESDAY_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自动统计信息时间的修改过程是怎样的，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。