oracle自动统计信息时间的修改过程记录

今天是2022年1月7日今天值夜班，同事让给优化一个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自动统计信息时间修改的文章就介绍到这了,更多相关oracle自动统计信息时间修改内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！