CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_AUTO_TASK_ADMIN
Version 11.1
 
General
Note: Optimizer statistics are automatically gathered by automatic optimizer statistics collection, which gathers statistics on all objects in the database which have stale or missing statistics. Automatic optimizer statistics collection runs as part of the automated maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined maintenance windows.
Source $ORACLE_HOME/rdbms/admin/dbmsatsk.sql
First Available 11.1

Constants
Name Data Type Value
Option Flags
OPTFLG_DEFERRED

VARCHAR2(16)

'DEFERRED'
OPTFLG_IMMEDIATE

VARCHAR2(16)

'IMMEDIATE'

Task Priority

PRIORITY_MEDIUM VARCHAR2(6) 'MEDIUM'
PRIORITY_HIGH VARCHAR2(6) 'HIGH'
PRIORITY_URGENT VARCHAR2(6) 'URGENT'
PRIORITY_CLEAR VARCHAR2(6) 'CLEAR'
Mutually Exclusive Doublet
LIGHTWEIGHT VARCHAR2(16) 'LIGHTWEIGHT'
HEAVYWEIGHT VARCHAR2(16) 'HEAVYWEIGHT'
Mutually Exclusive Doublet
VOLATILE VARCHAR2(16) 'VOLATILE'
STABLE VARCHAR2(16) 'STABLE'
Mutually Exclusive Doublet
SAFE_TO_KILL VARCHAR2(16) 'SAFE_TO_KILL'
DO_NOT_KILL VARCHAR2(16) 'DO_NOT_KILL'
Attribute Value Flags
ATTRVAL_TRUE VARCHAR2(5) 'TRUE
ATTRVAL_FALSE VARCHAR2(5) 'FALSE'
Dependencies
DBA_AUTOTASK_CLIENT  DBMS_AUTOTASK_PRVT_LIB
DBA_AUTOTASK_OPERATION DBMS_AUTO_TASK_EXPORT
DBA_RSRC_PLAN_DIRECTIVES DBMS_RESOURCE_MANAGER
Security Model Execute is granted to DBA, IMP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE
 
DISABLE
Immediately disables all tasks

Overload 1
dbms_auto_task_admin.disable;
exec dbms_auto_task_admin.disable;

Prevents AUTOTASK from executing any requests from a specified client or operation

Overload 2
dbms_auto_task_admin.disable(
client_name IN VARCHAR2,
operation   IN VARCHAR2,
window_name IN VARCHAR2);
-- disable collection of optimizer statistics
desc dba_autotask_client

col client_name format a35
col mean_job_duration format a30

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;

BEGIN
  dbms_auto_task_admin.disable('auto optimizer stats collection', NULL,
  NULL);
END;
/

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;
 
ENABLE
Immediately enables all previously disabled tasks

Overload 1
dbms_auto_task_admin.enable;
exec dbms_auto_task_admin.enable;

Allows a previously disabled client, operation, target type, or individual target to be enabled under AUTOTASK control.

Overload 2
dbms_auto_task_admin.enable(
client_name IN VARCHAR2,
operation   IN VARCHAR2,
window_name IN VARCHAR2);
-- reenable collection of optimizer statistics
desc dba_autotask_client

col client_name format a35
col mean_job_duration format a30

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;

BEGIN
  dbms_auto_task_admin.enable('auto optimizer stats collection', NULL,
  NULL);
END;
/

SELECT client_name, status, mean_job_duration
FROM dba_autotask_client;
 
GET_CLIENT_ATTRIBUTES

Returns values of select client attributes
dbms_auto_task_admin.get_client_attributes(
client_name  IN  VARCHAR2,  -- name from DBA_AUTOTASK_CLIENT
service_name OUT VARCHAR2,  -- Service name for client, may be NULL
window_group OUT VARCHAR2); -- Name of the active window group
desc dba_autotask_client

set linesize 121
col client_name format a35
col consumer_group format a25
col service_name format a15
col window_group format a20

SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client;

set serveroutput on

DECLARE
 sname dba_autotask_client.service_name%TYPE;
 wgrp  dba_autotask_client.window_group%TYPE;
BEGIN
  dbms_auto_task_admin.get_client_attributes('sql tuning advisor', sname, wgrp);

  dbms_output.put_line('Service: ' || sname);
  dbms_output.put_line('Window:  ' || wgrp);
END;
/
 
GET_P1_RESOURCES
Returns percent of resources allocated to each AUTOTASK High Priority Consumer Group

Total adds to 100%
dbms_auto_task_admin.get_p1_resources(
stats_group_pct  OUT NUMBER,  -- %resources for Statistics Gathering
seg_group_pct    OUT NUMBER,  -- %resources for Space Management
tune_group_pct   OUT NUMBER,  -- %resources for SQL Tuning
health_group_pct OUT NUMBER); -- %resources for Health Checks
TBD
 
OVERRIDE_PRIORITY

Manually override task priority

Overload 1
dbms_auto_task_admin.override_priority(
client_name IN VARCHAR2,
priority    IN VARCHAR2);
SELECT client_name, priority_override
FROM dba_autotask_client;

exec dbms_auto_task_admin.override_priority('sql tuning advisor', dbms_auto_task_admin.PRIORITY_HIGH);

SELECT client_name, priority_override
FROM dba_autotask_client;

Overload 2

A bug was reported during the beta and does not appear to have been fixed in 11.1.0.6. It is recommended that you use the Overload 1 syntax
dbms_auto_task_admin.override_priority(
client_name IN VARCHAR2,
operation   IN VARCHAR2,  -- as shown in DBA_AUTOTASK_OPERATION
priority    IN VARCHAR2);
desc dba_autotask_operation

set linesize 121
col client_name format a40
col operation_name format a30

SELECT client_name, operation_name, priority_override
FROM dba_autotask_operation;

exec dbms_auto_task_admin.override_priority('sql tuning advisor', 'automatic sql tuning task', dbms_auto_task_admin.PRIORITY_MEDIUM);

SELECT client_name, operation_name, priority_override
FROM dba_autotask_operation;
 
SET_ATTRIBUTE

Set Boolean attributes for a client, operation, or task

Overload 1
dbms_auto_task_admin.set_attribute(
client_name     IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2);
desc dba_autotask_client

set linesize 121
col attributes format a60

SELECT client_name, attributes
FROM dba_autotask_client;

exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'DO_NOT_KILL', 'TRUE');

SELECT client_name, attributes
FROM dba_autotask_client;

exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'SAFE_TO_KILL', 'TRUE');


SELECT client_name, attributes
FROM dba_autotask_client;

Overload 2
dbms_auto_task_admin.set_attribute(
client_name     IN VARCHAR2,
operation       IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2);
desc dba_autotask_operation

set linesize 131
col client_name format a35
col attributes format a60

SELECT client_name, operation_name, attributes
FROM dba_autotask_operation;

exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'automatic sql tuning task', 'SAFE_TO_KILL', 'TRUE');

SELECT client_name, operation_name, attributes
FROM dba_autotask_operation;
 
SET_CLIENT_SERVICE

Associates an AUTOTASK Client with a specified Service
dbms_auto_task_admin.set_client_service(
client_name  IN  VARCHAR2,   -- from DBA_AUTOTASK_CLIENT
service_name IN VARCHAR2);  -- Service name for client, may be NULL
SELECT client_name, service_name
FROM dba_autotask_client;

SELECT name
FROM dba_services;

DECLARE
 sname dba_autotask_client.service_name%TYPE;
BEGIN
  dbms_auto_task_admin.set_client_service('sql tuning advisor', 'orabase');
END;
/

SELECT client_name, service_name
FROM dba_autotask_client;
 
SET_P1_RESOURCES

Sets percentage-based resource allocation for each High Priority Consumer Group used by AUTOTASK Clients

Must total 100%
dbms_auto_task_admin.set_p1_resources(
stats_group_pct  IN NUMBER,   -- %resources for Statistics Gathering
seg_group_pct    IN NUMBER,   -- %resources for Space Management
tune_group_pct   IN NUMBER,   -- %resources for SQL Tuning
health_group_pct IN NUMBER);  -- %resources for Health Checks
desc dba_rsrc_consumer_groups

SELECT consumer_group_id, consumer_group
FROM dba_rsrc_consumer_groups;

desc resource_plan_directive$

SELECT plan, group_or_subplan, mgmt_p1
FROM resource_plan_directive$
WHERE (group_or_subplan LIKE '%STATS%'
   OR  group_or_subplan LIKE '%SPACE%'
   OR  group_or_subplan LIKE '%SQL%'
   OR  group_or_subplan LIKE '%HEALTH%');

exec dbms_auto_task_admin.set_p1_resources(10,20,30,40);

SELECT plan, group_or_subplan, mgmt_p1
FROM resource_plan_directive$
WHERE (group_or_subplan LIKE '%STATS%'
   OR  group_or_subplan LIKE '%SPACE%'
   OR  group_or_subplan LIKE '%SQL%'
   OR  group_or_subplan LIKE '%HEALTH%');

exec dbms_auto_task_admin.set_p1_resources(25,25,25,25);

SELECT plan, group_or_subplan, mgmt_p1
FROM resource_plan_directive$
WHERE (group_or_subplan LIKE '%STATS%'
   OR  group_or_subplan LIKE '%SPACE%'
   OR  group_or_subplan LIKE '%SQL%'
   OR  group_or_subplan LIKE '%HEALTH%');
 

Related Topics

DBMS_AUTO_TASK

DBMS_AUTO_TASK_EXPORT

DBMS_AUTO_TASK_IMMEDIATE

DBMS_RESOURCE_MANAGER

DBMS_RESOURCE_MANAGER_PRIVS

System Statistics

 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [202 users online]    © 2010 psoug.org