Tuesday, February 19, 2013

0 Automated Database Maintenance Task & optimizer stats collection

 

List automated database maintenance task

image

image

select client_name,status from DBA_AUTOTASK_CLIENT

image

 

Disable automated database maintenance task

select client_name,status from DBA_AUTOTASK_CLIENT;
 
 exec DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

image

Enable automated database maintenance task

 exec DBMS_AUTO_TASK_ADMIN.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

image

DBA_TAB_STATS_HISTORY

DBA_TAB_STATS_HISTORY store the table statistic meta data.

image

By default the table stats retention date is 30 days but we can change.

select dbms_stats.get_stats_history_retention from dual;
exec  dbms_stats.alter_stats_history_retention (60);

image

We can even restore the table stats to the earlier days as long as it is still within retention date.

Below demo show the table “MGMT_DB_FEATURE_LOG” has 6 stats collection. We change it to Feb14th from Feb 16th.

col table_name format a30
col stats_update_time format a40
select table_name,stats_update_time from dba_tab_stats_history where table_name = 'MGMT_DB_FEATURE_LOG';
exec dbms_stats.restore_table_stats ( OWNNAME=>'DBSNMP',tabname=>'MGMT_DB_FEATURE_LOG',as_of_timestamp => '14-FEB-13 10.00.28.333693000 PM -05:00');
select table_name,last_analyzed from dba_tables where table_name = 'MGMT_DB_FEATURE_LOG';

 

 image

 

Reference

http://www.oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1.php

0 comments:

Post a Comment

 

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