Sunday, February 24, 2013

0 Automated Database Maintenance Task (Auto task)& Automatic SQL Tuning (AST)

automated database maintenance task also called auto task. DBMS_AUTO_TASK_ADMIN package is used to manage the tasks.

  • View the AST report from OEM
  • Disable the AST
  • Enable the AST
  • Change the AST parameter
  • Show the AST report

Related tables

  • DBA_AUTOTASK_TASK
  • DBA_AUTOTASK_CLIENT
  • DBA_AUTOTASK_CLIENT_HISTORY
  • DBA_AUTOTASK_CLIENT_JOB
  • DBA_AUTOTASK_JOB_HISTORY
  • DBA_AUTOTASK_OPERATION
  • DBA_AUTOTASK_WINDOW_CLIENTS

 

View the AST report from OEM

image

image

image

Disable the AST

EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
select client_name,status from DBA_AUTOTASK_CLIENT;

image

Enable the AST

EXEc DBMS_AUTO_TASK_ADMIN.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
select client_name,status from DBA_AUTOTASK_CLIENT;

image

Change the AST parameter

EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

image

Show the AST report

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON
SPOOL C:\temp\AST.txt
SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(NULL,NULL,'TEXT','TYPICAL','ALL', NULL,NULL ) FROM   dual;
SPOOL OFF

 

List all the auto task

select CLIENT_NAME,TASK_NAME,TASK_TARGET_TYPE,TASK_TARGET_NAME,OPERATION_NAME,ATTRIBUTES,STATUS,CURRENT_JOB_NAME from DBA_AUTOTASK_TASK;

image

SELECT A.CLIENT_NAME,A.TASK_NAME,A.TASK_TARGET_TYPE,A.TASK_TARGET_NAME,A.OPERATION_NAME,A.ATTRIBUTES,A.STATUS,A.CURRENT_JOB_NAME,B.CONSUMER_GROUP,B. MEAN_JOB_DURATION
FROM DBA_AUTOTASK_TASK A,DBA_AUTOTASK_CLIENT B
where A.CLIENT_NAME = B.CLIENT_NAME

image

DBA_AUTOTASK_WINDOW_CLIENTS

List work window

select * from DBA_AUTOTASK_WINDOW_CLIENTS

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates