Oracle | DBMS_AUTO_TASK_ADMIN Demos, Syntax, and Example Code

Oracle DBMS_AUTO_TASK_ADMIN Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_AUTOTASK_CLIENT	DBA_RSRC_PLAN_DIRECTIVES	DBMS_STANDARD
CDB_AUTOTASK_OPERATION	DBMS_AUTOTASK_PRVT_LIB	KET$_AUTOTASK_STATUS
CDB_RSRC_PLAN_DIRECTIVES	DBMS_AUTOTASK_EXPORT	KET$_CLIENT_CONFIG
DBA_AUTOTASK_CLIENT	DBMS_MANAGEMENT_PACKS	X$KETCL
DBA_AUTOTASK_OPERATION

Documented Yes: Packages and Types Reference

First Available 11.1

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to the DBA, DATAPUMP_IMP_FULL_DATABASE, and IMP_FULL_DATABASE roles

Source $ORACLE_HOME/rdbms/admin/dbmsatsk.sql

Subprograms

DEFAULT_RESET	GET_CLIENT_ATTRIBUTES	SET_ATTRIBUTE
DISABLE	GET_P1_RESOURCES	SET_CLIENT_SERVICE
DISABLE_TRACING	MODIFY_AUTOTASK_SETTINGS	SET_P1_RESOURCES
ENABLE	OVERRIDE_PRIORITY	SKIP_SUSPENSION
ENABLE_TRACING	RUN_TASK_MANUAL

DEFAULT_RESET

Resets AUTOTASK configuration setting 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;

DISABLE_TRACING (new 23c)

Disable Autotask layer tracing dbms_auto_task_admin.disable_tracing; PRAGMA SUPPLEMENTAL_LOG_DATA(DISABLE_TRACING, READ_ONLY);

exec dbms_auto_task_admin.disable_tracing;

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;

ENABLE_TRACING (new 23c)

Enable Autotask layer tracing dbms_auto_task_admin.enable_tracing; PRAGMA SUPPLEMENTAL_LOG_DATA(ENABLE_TRACING, READ_ONLY);

exec dbms_auto_task_admin.enable_tracing;

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%

Deprecated as of 12cR1 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

MODIFY_AUTOTASK_SETTINGS

Use to modify AUTOTASK attributes dbms_auto_task_admin.modify_autotask_settings( client_name IN VARCHAR2, -- Name of the client as found in DBA_AUTOTASK_CLIENT View. attribute_name IN VARCHAR2, -- Attribute to be set attribute_value IN NUMBER -- Attribute value in numeric form

exec dbms_auto_task_admin.modify_autotask_settings('SQL_TUNING_ADVISOR', 'INTERVAL', 12);

      

      exec dbms_auto_task_admin.modify_autotask_settings('AUTO_SPACE_ADVISOR', 'MAX_RUN_TIME', 60);

OVERRIDE_PRIORITY

Manually override task priority
Deprecated as of 12cR1

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;

Deprecated as of 12cR1

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;

RUN_TASK_MANUAL (new 23c)

Runs the specified task in the current session. If an ongoing job is running for the same task in the background, this API will fail dbms_auto_task_admin.run_task-manual(task_name IN VARCHAR2);

TBD

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%

Deprecated as of 12cR1 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%');

RUN_TASK_MANUAL (new 23c)

Rruns the specified task in the current session. If there is an ongoing job running for the same task in the background, this API will fail dbms_auto_task_admin.skip_suspension(task_name IN VARCHAR2);

TBD