Statistics

To check tables need to gather a new statistics 
vi check_tables_needed_stats.sql
set line 400
col TABLE_OWNER format a
col TABLE_NAME format a30
col PARTITION_NAME format a20
col SUBPARTITION_NAME format a20
col TRUNCATED format a20
COL TABLE_OWNER FORMAT A20

select TABLE_OWNER, TABLE_NAME, TRUNCATED, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED, DROP_SEGMENTS from DBA_TAB_MODIFICATIONS;
To check  the statistics scheduler 
vi scheduler_view.sql
set line 300
COL OWNER FORMAT A15
COL JOB_NAME FORMAT A30
COL JOB_CLASS FORMAT A30
COL NEXT_RUN_DATE FORMAT A35
COL REPEAT_INTERVAL FORMAT A65
SET PAGESIZE 200
select
   owner,
   job_name,
   job_class,
   enabled,
   next_run_date,
   repeat_interval
from dba_scheduler_jobs
where owner = decode(upper('&1'), 'ALL', owner, upper('&1'));

To enable automatic statistics collection

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;

/
or 

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

To disable automatic statistics collection

Option 1
BEGIN
   dbms_auto_task_admin.disable
             (client_name=> 'auto optimizer stats collection',
              operation        => NULL,
              window_name      => NULL
             );
END;


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






To check the duration of the jobs 


col client_name format a20
col job_name format a30
col job_status format a15

col job_duration format a20
set line 400
set pagesize 100
select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION 
from DBA_AUTOTASK_JOB_HISTORY 
where JOB_START_TIME >systimestamp -7 and 
client_name='sql tuning advisor';

































Posted by