重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 11g 12C自动任务 [打印本页]

作者: jiawang 时间: 2019-12-23 14:32
标题: Oracle 11g 12C自动任务
本帖最后由 jiawang 于 2019-12-23 14:36 编辑

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. An example is a task that gathers statistics on schema objects for the query optimizer. Automated maintenance tasks run in maintenance windows, which are predefined time intervals that are intended to occur during a period of low system load. You can customize maintenance windows based on the resource usage patterns of your database, or disable certain default windows from running. You can also create your own maintenance windows.

oracle 数据库预先定义了自动任务的三个功能：

1. Automatic Optimizer Statistics Collection
对没有统计信息或者过时统计信息的数据库对象进行收集统计信息，用来提高 sql 执行效率

2. Automatic Segment Advisor
建议回收哪些段空间可以回收

3. Automatic SQL Tuning Advisor
检测高负载的 sql 语句性能，并建议如何进行调优

SQL> select client_name, task_name, operation_name, status from dba_autotask_task;

CLIENT_NAME                         TASK_NAME                            OPERATION_NAME          STATUS
   ---------------------------------------------------------------------------------------------------------------------------------
sql tuning advisor                   AUTO_SQL_TUNING_PROG    automatic sql tuning task    ENABLED
auto optimizer stats collection    gather_stats_prog                auto optimizer stats job       ENABLED
auto space advisor                auto_space_advisor_prog          auto space advisor job       ENABLED

oracle 11g 进行自动收集统计信息的任务客户端名称为 auto optimizer stats collection，实际调用执行的是 gather_stats_prog 程序，而 gather_stats_prog 程序调用的是 dbms_stats.gather_database_stats_job_proc 存储过程。

SQL> select program_type, program_action, enabled from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
PROGRAM_TYPE             PROGRAM_ACTION                                        ENABLED
   -------------------------------------------------------------------------------------------------------------
STORED_PROCEDURE    dbms_stats.gather_database_stats_job_proc    TRUE

oracle 11g 默认的任务执行时间：
1. 星期一至星期五晚上 10 点开始，执行 4 个小时
2. 星期六，星期日早上 6 点开始，执行 20 个小时

SQL>  select window_name, repeat_interval, duration, enabled from dba_scheduler_windows where enabled='TRUE';
WINDOW_NAME                   REPEAT_INTERVAL                                                                   DUATION          ENABLED
   ------------------------------ ---------------------------------------------------------------------------------------------------------------------------
   MONDAY_WINDOW             freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0    +000 04:00:00    TRUE
   TUESDAY_WINDOW             freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0       +000 04:00:00    TRUE
   WEDNESDAY_WINDOW       freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0    +000 04:00:00    TRUE
   THURSDAY_WINDOW          freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0    +000 04:00:00    TRUE
   FRIDAY_WINDOW                freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0       +000 04:00:00    TRUE
   SATURDAY_WINDOW          freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0       +000 20:00:00    TRUE
   SUNDAY_WINDOW                freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0       +000 20:00:00    TRUE

7 rows selected.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)