oracle自動統(tǒng)計信息時間的修改過程記錄
更新時間：2022年01月09日 12:26:51 作者：Rhys-Oracle
這篇文章主要給大家介紹了關(guān)于oracle自動統(tǒng)計信息時間的修改過程,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
今天是2022年1月7日今天值夜班，同事讓給優(yōu)化一個sql，優(yōu)化完成后，順便看了下新系統(tǒng)的統(tǒng)計信息情況，發(fā)現(xiàn)在晚上做數(shù)據(jù)采集的時間，系統(tǒng)資源增加，發(fā)現(xiàn)是統(tǒng)計信息在跑，在模擬環(huán)境測試，特此記錄。
- 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í)行時間：
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任務(wù)需要disable和enable之后才生效。
附：以下腳本可把Oracle自動統(tǒng)計信息收集周一到周五的時間窗口從22點調(diào)整為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;
/
總結(jié)
到此這篇關(guān)于oracle自動統(tǒng)計信息時間修改的文章就介紹到這了,更多相關(guān)oracle自動統(tǒng)計信息時間修改內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle數(shù)據(jù)庫中的游標(biāo)cursor使用詳解
這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫中的游標(biāo)使用的相關(guān)資料,游標(biāo)提供了一種從表中檢索數(shù)據(jù)并進行操作的靈活手段,游標(biāo)主要用在服務(wù)器上,處理由客戶端發(fā)送給服務(wù)器端的SQL語句,或是批處理、存儲過程、觸發(fā)器中的數(shù)據(jù)處理請求,需要的朋友可以參考下2023-10-10
這篇文章主要介紹了常用的Oracle doc命令(收藏)的相關(guān)資料，非常不錯，具有參考借鑒價值，需要的朋友參考下吧2017-10-10
Oracle?exadata存儲節(jié)點更換內(nèi)存操作及報錯處理方法
在進行Oracle?Exadata巡檢時,發(fā)現(xiàn)cell節(jié)點內(nèi)存報錯,需確認(rèn)內(nèi)存PN號及大小,并更換備件,這篇文章主要介紹了Oracle?exadata存儲節(jié)點更換內(nèi)存操作及報錯處理的相關(guān)資料,需要的朋友可以參考下2024-10-10
oracle 將以逗號隔開的字符串'85,86,87' 轉(zhuǎn)成行2009-06-06
這篇文章主要給大家介紹了關(guān)于Oracle如何實現(xiàn)like多個值的查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-08-08
oracle存儲過程中return和exit區(qū)別概述及測試
至于return和exit在oracle存儲過程中的應(yīng)用，有些新手朋友們還是比較容易混淆的，本文將針對這兩個關(guān)鍵字進行詳細(xì)對比下，感興趣的你可以參考下，希望可以幫助到你2013-02-02
Oracle 12CR2查詢轉(zhuǎn)換教程之表擴展詳解
Oracle 12cR2版本已經(jīng)發(fā)布有一段時間，下面這篇文章主要給大家介紹了關(guān)于Oracle 12CR2查詢轉(zhuǎn)換教程之表擴展的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-11-11
oracle net manager 數(shù)據(jù)傳輸安全步驟詳解
oracle net manager來加密客戶端與數(shù)據(jù)庫之間或中間件與 數(shù)據(jù)庫之間的網(wǎng)絡(luò)傳輸數(shù)據(jù)。這篇文章主要介紹了oracle net manager 數(shù)據(jù)傳輸安全 ,需要的朋友可以參考下2018-09-09
這篇文章主要給大家介紹了關(guān)于Oracle終極徹底卸載的完整步驟，文中通過圖文介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
前后發(fā)了好幾篇ORACLE的基礎(chǔ)知識了，主要還是自己做學(xué)習(xí)筆記的，加深印象，今天來看下ORACLE的查詢語句2014-08-08