oracle修改统计信息收集的节点

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

The Oracle docs note this on automatic maintenance tasks:

“In Oracle 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all maintenance windows”

Also see my dba_autotask_operation tips

Oracle 11g introduces a new feature called Automated Maintenance Tasks (AMR) and AMR starts 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 disable procedure 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 the set_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_attribute procedure 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;