Oracle DBMS_AUTO_TASK_ADMIN
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose 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.
AUTHID DEFINER
Constants
Name Data Type Value
Attribute Value Flags
ATTRVAL_FALSE VARCHAR2(5) 'FALSE';
ATTRVAL_TRUE VARCHAR2(5) 'TRUE'
Mutually Exclusive Doublet
HEAVYWEIGHT VARCHAR2(16) 'HEAVYWEIGHT'
LIGHTWEIGHT VARCHAR2(16) 'LIGHTWEIGHT'
Mutually Exclusive Doublet
STABLE VARCHAR2(16) 'STABLE'
VOLATILE VARCHAR2(16) 'VOLATILE'
Mutually Exclusive Doublet
DO_NOT_KILL VARCHAR2(16) 'DO_NOT_KILL'
SAFE_TO_KILL VARCHAR2(16) 'SAFE_TO_KILL'
Option Flags
OPTFLG_DEFERRED VARCHAR2(16) 'DEFERRED'
OPTFLG_IMMEDIATE VARCHAR2(16) 'IMMEDIATE'
Task Priorities
PRIORITY_CLEAR VARCHAR2(6) 'CLEAR'
PRIORITY_HIGH VARCHAR2(6) 'HIGH'
PRIORITY_MEDIUM VARCHAR2(6) 'MEDIUM'
PRIORITY_URGENT VARCHAR2(6) 'URGENT'
Dependencies
CDB_AUTO_TASK_CLIENT DBA_RSRC_PLAN_DIRECTIVES DBMS_STANDARD
CDB_AUTO_TASK_OPERATIONS DBMS_AUTOTASK_PRVT_LIB KET$_AUTOTASK_STATUS
CDB_RSRC_PLAN_DIRECTIVES DBMS_AUTO_TASK_EXPORT KET$_CLIENT_CONFIG
DBA_AUTO_TASK_CLIENT DBMS_MANAGEMENT_PACKS X$KETCL
DBA_AUTO_TASK_OPERATIONS DBMS_RESOURCE_MANAGER  
Documented Yes
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to the DBA, DATAPUMP_IMP_FULL_DATABASE, EM_EXPRESS_ALL, and IMP_FULL_DATABASE roles
Source $ORACLE_HOME/rdbms/admin/dbmsatsk.sql
Subprograms
 
DEFAULT_RESET (new 12.1)
Resets AUTOTASK configuration settting to
factory defaults
dbms_auto_task_admin.default_reset(
client_name    IN VARCHAR2,  -- client name or ALL
operation_name IN VARCHAR2 DEFAULT ALL); -- operation name or ALL
exec dbms_auto_task_admin.default_reset('ALL', 'ALL');
 
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 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 a35
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
DBMS_SCHEDULER
Packages
System Statistics

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved