Oracle 2022-07-24

ORACLE开发：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

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

ORACLE 技术文档数据库

数据运维技术 » ORACLE开发：oracle自动统计信息时间的修改过程记录

分享到：

总结

相关推荐