Oracle 11g 自动任务
来源:互联网 发布:迅雷未知错误种子 mac 编辑:程序博客网 时间:2024/05/14 21:04
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.
1. 停用整个自动收集统计任务
exec dbms_auto_task_admin.disable( client_name=>’auto optimizer stats collection’,
operation => null,
window_name => null);
2. 只禁用星期一的自动任务(sys 用户登录),除了禁用 auto optimizer stats collection,还会禁用 automatic segment advisor 和 automatic sql tuning advisor
exec dbms_scheduler.disable( name => ‘MONDAY_WINDOW’,
force => TRUE);
SQL> select window_name, enabled from dba_scheduler_windows;
WINDOW_NAME ENABLED
------------------------------ ----------------------
MONDAY_WINDOW FALSE
TUESDAY_WINDOW TRUE
WEDNESDAY_WINDOW TRUE
THURSDAY_WINDOW TRUE
FRIDAY_WINDOW TRUE
SATURDAY_WINDOW TRUE
SUNDAY_WINDOW TRUE
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
9 rows selected.
3. 启用星期一自动任务
exec dbms_scheduler.enable( name => ‘MONDAY_WINDOW’);
4. 只禁用星期一的 auto optimizer stats collection 作业
exec dbms_auto_task_admin.disable( client_name => ‘auto optimizer stats collection’,
operation => null,
window_name => ‘MONDAY_WINDOW’);
SQL> select window_name,optimizer_stats,segment_advisor,sql_tune_advisor from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZE_STATS SEGMENT_ ADVISOR SQL_TUNE_ADVISOR
------------------------------ -------- -------- -------------------------------------------------------------------------------
MONDAY_WINDOW DISABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
7 rows selected.
5. 修改自动任务,必须先 disable,然后再 enable
例如,修改星期一自动任务的执行时间为晚上 11 点
exec dbms_scheduler.disable( name => ‘MONDAY_WINDOW’, force => TRUE);
exec dbms_scheduler.set_attribute( name => ‘MONDAY_WINDOW’, attribute => ‘repeat_interval’,
value => ‘freq=daily;byday=MON;byhour=23;byminute=0;bysecond=0’);
exec dbms_scheduler.enable( name => ‘MONDAY_WINDOW’);
6. 查看自动任务执行情况历史记录
SQL> select client_name, window_name, window_start_time, window_duration from dba_autotask_client_history where rownum < 5;
CLIENT_NAME WINDOW_NAME WINDOW_START_TIME WINDOW_DURATION
----------------------------------- -------------------- -------------------------------------------------- ---------------------------------------------------------------------------
auto optimizer stats collection FRIDAY_WINDOW 24-OCT-14 11.10.54.202768 PM +08:00 +000000000 07:39:41.282770
auto optimizer stats collection FRIDAY_WINDOW 07-NOV-14 10.00.00.099508 PM +08:00 +000000000 08:58:30.592880
auto optimizer stats collection FRIDAY_WINDOW 14-NOV-14 10.00.00.091594 PM +08:00 +000000000 08:49:15.341343
auto optimizer stats collection FRIDAY_WINDOW 21-NOV-14 10.00.00.076015 PM +08:00 +000000000 00:33:43.797989
7. 查看正在执行的自动任务
SQL> select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB;
参考:《基于 oracle 的 sql 优化》
- Oracle 11g 自动任务
- oracle 11g 自动维护任务
- 【Oracle】Oracle 11g 中的自动数据库维护任务管理
- Oracle 11g 中的自动数据库维护任务管理
- oracle 11g 自动维护任务说明 示例
- Oracle 11g New 自动维护任务以及I/O校准
- orale 10g和11g中的自动统计任务
- 关于oracle 11g自动分区+分区改名+定时任务综合使用实现自动分区后可以进行分区查询
- Oracle 11g 的 自动内存管理
- 关闭 oracle 11g自动分析
- oracle 11g 自动内存管理。
- oracle 11G utl_mail 自动发邮件
- Linux配置Oracle 11g自动启动
- oracle 11g 自动内存管理
- Oracle 11g 的 自动内存管理
- Linux配置Oracle 11g自动启动
- [Oracle] 11G自动收集统计信息
- Oracle 11G自动收集统计信息
- 比较不错的js效果笔记
- 得到新打开Activity 关闭后返回的数据
- 两分钟彻底让你明白Android Activity生命周期(图文)!
- Sleuth.js - 想用啥就用啥
- ViewFlipper中每个子View的点击事件
- Oracle 11g 自动任务
- 周记
- Linux下Office, PPT, Excel的快捷操作,
- 此心安处是吾乡
- ios系统版本的适配
- [C++]gcc中的原子操作
- 关于监听的基本用法
- 浅谈C++中函数调用的底层机制
- 为Odoo安装PDF报表运行环境