dbms_auto_task_admin Tips

转自 http://www.dba-oracle.com/t_packages_dbms_auto_task_admin.htm

 

Oracle 11g introduces a new feature called Automated Maintenance Tasks (AMR) and AMRstarts with some of the more common duties of a database administrator. Information gathered from the AWR repository is analyzed by Autotask and it then builds the correct tasks to be executed in next maintenance window. These tasks might include:

n  Optimizer statistics gathering

n  Automatic Segment Advisor

n  SQL Tuning Advisor

One way of changing the Autotask configuration is through the dbms_auto_task_admin  package. Alternately, the Oracle Enterprise Manager (OEM) can be used. Some important views used to collect information through the Autotask configuration are explained here:

n  dba_autotask_task: This view shows information about task execution time, current status, priority and historical data like last and best times

n  dba_autotask_window_clients:  This view displays information about current windows available in the database belonging to maintenance_window_group

n  dba_autotask_client_history:  View used to show historical information for each job execution

n  dba_autotask_operation:  View used to display operation information for each client, such as attributes and status

n  dba_autotask_job_history: This view provides information about job runs after each execution

n  dba_autotask_client:  View used to display statistical data for each task for the last seven days. It also shows an evaluation for the last 30 days.

n  dba_autotask_window_history:  Shows historical information for each maintenance task window

The next example shows how to change the Autotask  configuration as well as disable it if desired. If the Autotask feature needs to be disabled from the database, execute the disableprocedure as follows:

 

<   Code  7.2 – dbms_auto_task_admin.sql

conn sys@ora11g as sysdba

 

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

conn / as sysdba

 

--check the actual status of your task
select 
   autotask_status 
from 
   dba_autotask_window_clients;
--disable Autotask   
exec dbms_auto_task_admin.disable;

--check the status again
select 
   autotask_status 
from 
   dba_autotask_window_clients;

--enable autotask
exec dbms_auto_task_admin.enable;

 

--disable all taks for a client
set serveroutput on
declare
v_status_before varchar2(10);
v_status_after varchar2(10);
begin
select 
   status into v_status_before 
from 
   dba_autotask_client 
where 
   client_name='sql tuning advisor';
dbms_output.put_line(a => 'client status before command: '||v_status_before);   
dbms_auto_task_admin.disable('sql tuning advisor',NULL,NULL);

select 
   status into v_status_after
from 
   dba_autotask_client 
where 
   client_name='sql tuning advisor';
dbms_output.put_line(a => 'client status after command: '||v_status_after);
end;
/

 

Next, the get_p1_resources procedure is used to return the percentage of resources allocated to each Autotask included in the High Priority Group.

 

--Get information the percent of resources used by each Autotask that is in high priority group
declare 
v_stats_group_pct number;
v_seq_group_pct number;
v_tune_group_pct number;
v_health_group_pct number;
begin
dbms_auto_task_admin.get_p1_resources(v_stats_group_pct,v_seq_group_pct,v_tu

ne_group_pct,v_health_group_pct);
dbms_output.put_line(a => 
   'Percentage of resources for Statistics Gathering: '||v_stats_group_pct||chr(10)||
   'Percentage of resources for Space Management: '||v_seq_group_pct||chr(10)||
   'Percentage of resources for SQL Tuning: '||v_tune_group_pct||chr(10)||
   'Percentage of resources for Health Checks: '||v_health_group_pct);
end;
/

 

If it is necessary to change resource consumption utilization, this can be done through theset_p1_resources procedure.

 

--If it is necessary to change resource consumption utilization, then it can be done through set_p1_resources procedure
set serveroutput on
declare 
v_stats_group_pct number;
v_seq_group_pct number;
v_tune_group_pct number;
v_health_group_pct number;
begin
v_stats_group_pct := 15;
v_seq_group_pct := 30;
v_tune_group_pct := 30;
v_health_group_pct := 25;

   dbms_auto_task_admin.set_p1_resources(
      stats_group_pct => v_stats_group_pct,
      seg_group_pct => v_seq_group_pct,
      tune_group_pct => v_tune_group_pct,
      health_group_pct => v_health_group_pct);
end;
/

 

Now, assume that I have a Real Applications Cluster (RAC) environment with three nodes and we want to spread tasks between them. The service names are dbms1, dbms2 and dbms2:

 

col client_name for a35
col service_name for a15
select 
   client_name,
   service_name 
from 
   dba_autotask_client;
--Get the service name that will be assossiated with client_name
select 
   name 
from 
   dba_services;
--Make the association
begin
   dbms_auto_task_admin.set_client_service(
      client_name => 'sql tuning advisor',
      service_name => 'dbms');
end;
/

 

In order to get the attribute values of a certain client, use the get_client_attributes procedure.

 

--In order to get the attribute values of a certain client you can use get_client_attributes procedure
--Get the client name
select 
   client_name 
from 
   dba_autotask_client;
   
declare 
v_service_name varchar2(20);
v_service_name_1 varchar2(20);
v_window       varchar2(20);
v_client_name  varchar2(30);
begin
   v_client_name := 'auto space advisor';
   dbms_auto_task_admin.get_client_attributes(
      client_name => v_client_name,
      service_name => v_service_name,
      window_group => v_window);

   select decode(v_service_name,NULL,'NULL') into v_service_name_1 from dual;

   dbms_output.put_line(a => 
      ' Attributes for client '||v_client_name||chr(10)||
      ' - Service_Name is: '||v_service_name_1||chr(10)||
      ' - Window Group is: '||v_window);
end;
/

 

Make the association. If the client attribute values need to be changed, use the  set_attributeprocedure as follows:

 

--If you need to change client attribute values then use set_attribute procedure as follows:
--Get the client name and current attributes
select 
   client_name ,attributes
from 
   dba_autotask_client;
--Change some attribute values
begin
   dbms_auto_task_admin.set_attribute(
      client_name =>'auto space advisor' ,
      attribute_name =>'safe_to_kill' ,
      attribute_value =>'FALSE');
end;
/

begin
   dbms_auto_task_admin.set_attribute(
      client_name =>'auto space advisor' ,
      attribute_name =>'volatile' ,
      attribute_value =>'FALSE');
end;
/

--Get the results
select 
   client_name ,attributes
from 
   dba_autotask_client;

 

If the intent is to change the task priority of any client, operation and/or individual task level, use the override_priority procedure.

 

--To override the priority of auto space advisor client you can execute this command below:
--Get the current value
select 
   client_name, 
   priority_override
from 
   dba_autotask_client;

--Change the priority to urgent
begin
   dbms_auto_task_admin.override_priority(
      client_name => 'auto space advisor',
      priority => dbms_auto_task_admin.priority_urgent);
end;
/

--Get the new value
select 
   client_name, 
   priority_override
from 
   dba_autotask_client;