Shulou(Shulou.com)06/02 Report--

This article shows you how the oracle automatic statistical information time modification process is, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

Today is the night shift on January 10th, 2022. My colleague asked to optimize a sql. After the optimization was completed, I took a look at the statistics of the new system. I found that during the time of data collection at night, the resources of the system increased. I found that the statistical information was running. It is hereby recorded in the simulated environment test.

-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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@rhys > col REPEAT_INTERVAL for a60SYS@rhys > set linesize 200SYS@rhys > select t1.Wingroupies members t2 2 where t1.window_name=t2.window_name and t2.windowboxes groupname names WINDOW_NAME REPEAT_INTERVAL DURATION -FRIDAY_WINDOW freq=daily Byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 + 000 20:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 000 20:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0 Bysecond=0 + 000 04:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 + 000 04:00:00 7 rows selected. SYS@rhys >

View status:

SYS@rhys > select client_name,status from dba_autotask_client CLIENT_NAME STATUS-----auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLED SYS@rhys >

Change the execution time:

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.windowflowers intervalrect t1.repeatables intervalrecy t1.The from dba_scheduler_windows t1reparations dbathing trainerwingroupnames members t22 where t1.window_name=t2.window_name and t2.windowtrees groupnames WINDOW_NAME REPEAT_INTERVAL DURATION -FRIDAY_WINDOW freq=daily Byday=FRI;byhour=1;byminute=0; bysecond=0 + 000 04:00:00MONDAY_WINDOW freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0 + 000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0 + 000 04:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0 + 000 04:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=1;byminute=0 Bysecond=0 + 000 04:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0 + 000 04:00:00WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0 + 000 04:00:00 7 rows selected. SYS@rhys >

The change is complete. Note: each schedule task requires disable and enable before it takes effect.

Note: the following script adjusts the time window for Oracle automatic statistics collection from 22:00 to 2 o'clock from Monday to Friday.

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;/ the above is how the oracle automatic statistical information time is modified. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.