| 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%'); |
