oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間的修改過(guò)程記錄
更新時(shí)間：2022年01月09日 12:26:51 作者：Rhys-Oracle
這篇文章主要給大家介紹了關(guān)于oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間的修改過(guò)程,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
今天是2022年1月7日今天值夜班，同事讓給優(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;
/
總結(jié)
到此這篇關(guān)于oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間修改的文章就介紹到這了,更多相關(guān)oracle自動(dòng)統(tǒng)計(jì)信息時(shí)間修改內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
教你怎樣用Oracle方便地查看報(bào)警日志錯(cuò)誤
由于報(bào)警日志文件很大，而每天都應(yīng)該查看報(bào)警日志（查看有無(wú)“ORA-”，Error”，“Failed”等出錯(cuò)信息），故想找到一種比較便捷的方法，查看當(dāng)天報(bào)警日志都有哪些錯(cuò)誤。2014-08-08
這篇文章主要給大家介紹了關(guān)于SQL PLUS基本命令的使用方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
Oracle數(shù)據(jù)庫(kù)自定義類(lèi)型type的用法詳解
這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)自定義類(lèi)型type的用法詳解,Oracle?數(shù)據(jù)庫(kù)的概念和其它數(shù)據(jù)庫(kù)不一樣，這里的數(shù)據(jù)庫(kù)是一個(gè)操作系統(tǒng)只有一個(gè)庫(kù),可以看作是?Oracle?就只有一個(gè)大數(shù)據(jù)庫(kù),需要的朋友可以參考下2023-07-07
ORACLE實(shí)現(xiàn)字段自增示例說(shuō)明
如何實(shí)現(xiàn)oracle中字段的自增，下面用一個(gè)例子來(lái)說(shuō)明，有此需求的各位朋友可以參考下2013-11-11
這篇文章主要介紹了Oracle 死鎖的檢測(cè)查詢(xún)及處理，文章又描述關(guān)于數(shù)據(jù)庫(kù)死鎖的檢查方法的一些相關(guān)資料,需要的朋友可以參考下面文章的具體內(nèi)容2021-09-09
關(guān)于Oracle多表連接,提高效率,性能優(yōu)化操作
這篇文章主要介紹了關(guān)于Oracle多表連接,提高效率,性能優(yōu)化操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-10-10
Oracle顯示游標(biāo)的使用及游標(biāo)for循環(huán)
本篇文章給大家介紹oracle顯示游標(biāo)的使用及游標(biāo)for循環(huán)，當(dāng)查詢(xún)返回單行記錄時(shí)使用隱式游標(biāo)，查詢(xún)返回多行記錄并逐行進(jìn)行處理時(shí)使用顯式游標(biāo)，對(duì)本文感興趣的朋友一起學(xué)習(xí)吧2015-11-11
查找oracle數(shù)據(jù)庫(kù)表中是否存在系統(tǒng)關(guān)鍵字的方法
遇到列說(shuō)明無(wú)效的報(bào)錯(cuò)情況，這是由于數(shù)據(jù)庫(kù)列名起的不好引起的，名字用到了數(shù)據(jù)庫(kù)的關(guān)鍵字2014-07-07
這篇文章主要介紹了Oracle表空間不足的兩種解決辦法,需要的朋友可以參考下2017-10-10
這篇文章主要介紹了Oracle 12c安裝方法及一些使用問(wèn)題,需要的朋友可以參考下2016-09-09