oracle自动统计信息时间的修改过程记录
短信预约 -IT技能 免费直播动态提醒
今天是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自动统计信息时间修改内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
