oracle自动统计信息时间的修改过程记录代码
作者：袖梨
2022-06-29
本篇文章小编给大家分享一下oracle自动统计信息时间的修改过程记录代码，文章代码介绍的很详细，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。
代码如下：
- 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 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;
/
相关文章
精彩推荐
-
忍者必须死34399账号登录版 最新版v1.0.138v2.0.72
下载 -
勇者秘境oppo版 安卓版v1.0.5
下载 -
忍者必须死3一加版 最新版v1.0.138v2.0.72
下载 -
绝世仙王官方正版 最新安卓版v1.0.49
下载
-
下载
Goat Simulator 3手机版 安卓版v1.0.8.2
模拟经营 Goat Simulator 3手机版 安卓版v1.0.8.2Goat Simulator 3手机版是一个非常有趣的模拟游
-
下载
Goat Simulator 3国际服 安卓版v1.0.8.2
模拟经营 Goat Simulator 3国际服 安卓版v1.0.8.2Goat Simulator 3国际版是一个非常有趣的山羊模
-
下载
烟花燃放模拟器中文版 2025最新版v1.0
模拟经营 烟花燃放模拟器中文版 2025最新版v1.0烟花燃放模拟器是款仿真的烟花绽放模拟器类型单机小游戏，全方位
-
下载
我的世界动漫世界 手机版v友y整合
模拟经营 我的世界动漫世界 手机版v友y整合我的世界动漫世界模组整合包是一款加入了动漫元素的素材整合包，
-
下载
我的世界贝爷生存整合包 最新版v隔壁老王
模拟经营 我的世界贝爷生存整合包 最新版v隔壁老王我的世界MITE贝爷生存整合包是一款根据原版MC制作的魔改整
