设为首页 收藏本站
查看: 371|回复: 0

[经验分享] Oracle 11g Automated Maintenance Tasks(原创)

[复制链接]

尚未签到

发表于 2016-7-24 09:20:55 | 显示全部楼层 |阅读模式
  Overview
You’re familiar with the concept of automated maintenance tasks from the Oracle Database 10g release. These are jobs that are run automatically by the database to perform maintenance operations. In Oracle Database 10g, you had two automatic maintenance tasks: the Automatic Optimizer Statistics collection and the Automatic Segment Advisor. In Oracle Database 11g, there is a third automatic  maintenance task named Automatic SQL Tuning Advisor. The Automatic SQL Tuning Advisor reviews all high resource consuming SQL statements in the database and provides recommendations to tune them. If you want, you can configure the database so it automatically implements some types of recommendations, such as SQL profiles.This artical will revolve around Automatic Sql Tuning Task to introduce the Automated Maintenance Task.
  The Automatic SQL Tuning Advisor runs during the default system maintenance window on a nightly basis, just as the other two automated maintenance tasks do. A maintenance window is an Oracle Scheduler window that’s part of the MAINTENANCE_WINDOW_GROUP. You choose low system load time interval periods for the maintenance windows. A Scheduler resource plan specifies how the database will allocate resources during the duration of a window. When a Scheduler window opens, the database automatically enables the resource plan associated with that window. 
  Predefi ned Maintenance Windows
  In Oracle Database 10g, you had two maintenance windows: weeknight_window and weekend_window. In Oracle Database 11g, there are seven predefined daily maintenance windows, one for each day of the week. Here are the predefined maintenance windows and their descriptions: 
  

  • MONDAY_WINDOW               Starts 10 P.M. on Monday ends at 2 A.M.
  • TUESDAY_WINDOW              Starts 10 P.M. on Tuesday ends at 2 A.M.
  • WEDNESDAY_WINDOW        Starts 10 P.M. on Wednesday ends at 2 A.M.
  • THURSDAY_WINDOW            Starts 10 P.M. on Thursday ends at 2 A.M.
  • FRIDAY_WINDOW                  Starts 10 P.M. on Friday ends at 2 A.M.
  • SATURDAY_WINDOW            Starts 6 A.M on Saturday ends at 2.A.M
  • SUNDAY_WINDOW                Starts 6 A.M. on Sunday ends at 2 A.M.
  Note that the first five windows that run during the weekdays are open for 4 hours and the two weekend maintenance windows are open for 20 hours. By default, all seven daily windows belong to the MAINTENANCE_WINDOW_GROUP group. You can change the time and duration of the daily maintenance windows, create new maintenance windows, or disable or remove the default maintenance windows. 
  Managing the Automatic Maintenance Tasks
  In Oracle Database 10g, you had to use the DBMS_SCHEDULER package to enable and disable the automatic maintenance tasks. The ENABLE procedure lets you enable an automatic maintenance job such as the automatic statistics collection job, and the DISABLE procedure lets you disable it, if you wanted to manually collect the optimizer statistics. In Oracle Database 11g, use the new DBMS_AUTO_TASK_ ADMIN package to manage the automatic maintenance tasks. You can also use the Enterprise Manager to access the controls for the automatic maintenance tasks. The DBMS_AUTO_TASK_ADMIN package provides a more fine-grained management capability to control the operation of the automatic maintenance tasks. For example, the DBMS_SCHEDULER package only lets you enable or disable an automatic task. With the new DBMS_AUTO_TASK_ADMIN package, you can now disable a task only in selected maintenance windows instead of completely disabling the entire task. Before you start looking into the management of the automatic maintenance tasks, it’s a good idea to get familiar with two new views that provide information you might need to manage the tasks: the DBA_AUTOTASK_CLIENT view and the DBA_AUTOTASK_OPERATION view. The two views contain several identical columns. The DBA_AUTOTASK_CLIENT view shows data for all three automated tasks over a 1-day and a 30-day period. The following query shows details about the automatic maintenance tasks: 
  SQL> select client_name, status,
  2  attributes, window_group,service_name
  3  from dba_autotask_client;
CLIENT_NAME            STATUS     ATTRIBUTES
--------------------  --------    ------------------------------
auto optimizer        ENABLED     ON BY DEFAULT, VOLATILE,
statistics collection             SAFE TO KILL
auto space advisor    ENABLED     ON BY DEFAULT, VOLATILE,
                                  SAFE TO KILL
sql tuning advisor    ENABLED     ONCE PER WINDOW,ON BY DEFAULT,
                                  VOLATILE, SAFE TO KILL
You can see that all three of the automatic maintenance tasks are enabled. When the maintenance window opens, Oracle Scheduler automatically creates the automatic maintenance jobs and runs them. If the maintenance window is long, Oracle restarts the automatic optimizer statistics collection and the automatic segment advisor jobs every four hours. However, the automatic SQL advisor job runs only once per maintenance window, as evidenced by the ONCE PER WINDOW attribute for that job. The attributes column shows only ON BY DEFAULT as the value for the other two automated maintenance tasks. Each of the automatic maintenance tasks is called a client and is given a client name. The actual Scheduler job associated with each of the three clients is called an operation and is given an operation name. The following query on the DBA_AUTO_TASK_OPERATION view shows the operation names:
  SQL> select client_name, operation_name from
dba_autotask_operation;
CLIENT_NAME                OPERATION_NAME
----------------------     -------------------------
auto optimizer             auto optimizer stats job
stats collection
auto space advisor         auto space advisor job
sql tuning advisor         automatic sql tuning task
Enabling a Maintenance task
  Use the DBMS_AUTO_ADMIN.ENABLE procedure to enable a client, operation, target type, or individual target that you previously disabled. Before you can do this, you must first query the DBA_AUTOTASK_CLIENT and the DBA_AUTOTASK_ OPERATION views to find the values for the client_name and operation_ name attributes of the procedure.
  SQL> begin
  2  dbms_auto_task_admin.enable
  3  (client_name  => 'sql tuning advisor',
  4  operation     => 'automatic sql tuning task',
  5  window_name   => 'monday_window');
  6* end;
PL/SQL procedure successfully completed.
  Disabling a Maintenance Task
  By default, all three maintenance jobs will run in every maintenance window. You can use the DBMS_AUTO_ADMIN.DISABLE procedure to disable a client or operation for a specific window, as shown here:
  SQL> begin
  2     dbms_auto_task_admin.disable(
  3          client_name => 'sql tuning advisor',
  4          operation   => 'automatic sql tuning task',
  5          window_name => 'monday_window');
  6  end;
  7  /
PL/SQL procedure successfully completed.
  Maintenance Window Configuration
  Before you configure the window attributes,you need to get the current window attributes by querying DBA_SCHEDULER_WINDOWS view.
  SQL> SELECT WINDOW_NAME, DURATION
  2  FROM DBA_SCHEDULER_WINDOWS
  3  WHERE WINDOW_NAME = 'TUESDAY_WINDOW';
WINDOW_NAME      DURATION
---------------- --------------------
TUESDAY_WINDOW   +000 03:00:00
  To configure window attributes,you can use following command:
  SQL> BEGIN
  2      DBMS_SCHEDULER.set_attribute(
  3      name    =>  'TUESDAY_WINDOW',
  4  attribute => 'DURATION',
  5  value   => numtodsinterval(120,'minute'));
  6  END;
  7  /
PL/SQL procedure successfully completed.
  SQL> SELECT WINDOW_NAME, DURATION
  2   FROM DBA_SCHEDULER_WINDOWS
  3   WHERE WINDOW_NAME = 'TUESDAY_WINDOW';
WINDOW_NAME      DURATION
---------------- --------------------
TUESDAY_WINDOW   +000 02:00:00
  The other window attributes you can find from following diagram.
NameDescription  comments
  An optional comment about the window.
  duration
  The duration of the window.
  end_date
  The date after which the window will no longer open. If this is set, schedule_name must be NULL.
  repeat_interval
  A string using the calendaring syntax. PL/SQL date functions are not allowed. If this is set, schedule_name must be NULL. See "Calendaring Syntax" for more information.
  resource_plan
  The resource plan to be associated with a window. When the window opens, the system will switch to this resource plan. When the window closes, the original resource plan will be restored. If a resource plan has been made active with the force option, no resource plan switch will occur.
  Only one resource plan can be associated with a window. It may be NULL or the empty string (""). When it is NULL, the resource plan that is in effect when the window opens stays in effect for the duration of the window. When it is the empty string, the resource manager is disabled for the duration of the window.
  schedule_name
  The name of a schedule to use with this window. If this is set, start_date, end_date, and repeat_interval must all be NULL.
  start_date
  The next date and time on which this window is scheduled to open. If this is set, schedule_name must be NULL.
  window_priority
  The priority of the window. Must be one of 'LOW' (default) or 'HIGH'.
  
  Implementing Automatic Maintenance Task
  The database doesn’t assign any permanent Scheduler jobs to the three automated maintenance tasks. You therefore can’t manage the jobs with the usual DBMS_ SCHEDULER package. Use the new DBMS_AUTO_TASK_ADMIN package instead to manage the automated maintenance tasks. The new background process, Autotask Background Process (ABP), implements the automated maintenance tasks. The ABP maintains a history of all automated maintenance task executions in the repository that it maintains in the SYSAUX tablespace. Another background process, MMON, spawns (usually when a maintenance window opens), monitors, and restarts the ABP process. The ABP is in charge of converting tasks into Scheduler jobs. The ABP creates a task list for each maintenance job and assigns them a priority. There are three levels of job priorities: urgent, high, and medium. The ABP creates the urgent priority jobs first, after which it creates the high priority and the medium priority jobs. Various Scheduler job classes are also created, in order to map a task’s priority consumer group to the corresponding job class. The ABP is in charge of assigning the jobs to the job classes. The job classes map the individual jobs to a consumer group, based on the job priority. The ABP stores its data in the SYSAUX tablespace. You can view the ABP repository by querying the DBA_AUTOTASK_TASK view.  
  Configuring Resource Allocation for Automatic Tasks
  You can control the percentage of resources allocated to the maintenance tasks during a given maintenance window. The default resource plan for each predefined maintenance window is the DEFAULT_MAINTENANCE_PLAN. When a maintenance window opens, the DEFAULT_MAINTENANCE_PLAN is activated to control the amount of CPU used by the various automatic maintenance tasks. The three default maintenance tasks run under the ORA$AUTOTASK_SUB_PLAN, which is a subplan of the DEFAULT_MAINTENANCE_PLAN, with all three plans sharing the resources equally. ORA$AUTOTASK_SUB_PLAN gets 25 percent of the resources at the priority level 2. The consumer group SYS_GROUP takes priority in the DEFAULT_MAINTENANCE_PLAN resource plan, getting 100 percent of the level 1 resources in the DEFAULT_MAINTENANCE_PLAN. If you want to change the resource allocation for the automatic tasks in a specific window, you must change the resource allocation to the subplan ORA$AUTOTASK_SUB_PLAN in the resource plan for that window.
  Priorities for the various tasks that run during the maintenance window (three tasks altogether) are determined by assigning different consumer groups to the DEFAULT_ MAINTENANCE_PLAN. For example, the new Automatic SQL Tuning task is assigned to the ORA$AUTOTASK_SQL_GROUP consumer group. The Optimizer Statistics Gathering task is part of the ORA$AUTOTASK_STATS_GROUP, and the Segment Advisor task belongs to the ORA$AUTOTASK_SPACE_GROUP.
  
  参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
  http://www.oracle-base.com/articles/11g/awr-baseline-enhancements-11gr1.php
  http://www.oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1.php
  http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHDAIIH
  本文原创,转载请注明出处、作者
  如有错误,欢迎指正
  邮箱:czmcj@163.com
  
  

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-248495-1-1.html 上篇帖子: Oracle ORA_ROWSCN 伪列 说明 下篇帖子: Oracle EBS WMS功能介绍(二)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表