溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間的修改過(guò)程是怎樣的

發(fā)布時(shí)間：2022-01-10 15:21:40 來(lái)源：億速云 閱讀：147 作者：柒染 欄目：開(kāi)發(fā)技術(shù)

本篇文章為大家展示了oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間的修改過(guò)程是怎樣的，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。

今天是2022年1月10日今天值夜班，同事讓給優(yōu)化一個(gè)sql，優(yōu)化完成后，順便看了下新系統(tǒng)的統(tǒng)計(jì)信息情況，發(fā)現(xiàn)在晚上做數(shù)據(jù)采集的時(shí)間，系統(tǒng)資源增加，發(fā)現(xiàn)是統(tǒng)計(jì)信息在跑，在模擬環(huán)境測(cè)試，特此記錄。

      - 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>

查看狀態(tài)：

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>

更改執(zhí)行時(shí)間：

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>

更改完成。注意：每個(gè)schedule任務(wù)需要disable和enable之后才生效。

附：以下腳本可把Oracle自動(dòng)統(tǒng)計(jì)信息收集周一到周五的時(shí)間窗口從22點(diǎn)調(diào)整為2點(diǎn)。

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;
/

上述內(nèi)容就是oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間的修改過(guò)程是怎樣的，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手