今天是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自动统计信息时间修改内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！