DB version: 11.2.0.4
OS : Red Hat Linux 6.5
The biggest DB in my shop is 18 TeraBytes in size. The DB level "Auto gather stats" job in this DB is not completing in time because of the below mentioned issue
Unlike 10g, 11g does not have the named scheduler job - 'GATHER_STATS_JOB'. In 11.2 , this task name has been changed to "auto optimizer stats collection" .
col CLIENT_NAME format a35
col TASK_NAME format a25
col PROGRAM_ACTION format a45
set lines 200
select ac.client_name,ac.status,
at.task_name,
sp.program_action
from dba_autotask_client ac,
dba_autotask_task at,
dba_scheduler_programs sp
where ac.client_name='auto optimizer stats collection'
and ac.client_name=at.client_name
and upper(at.task_name)=upper(sp.program_name);
CLIENT_NAME STATUS TASK_NAME PROGRAM_ACTION
----------------------------------- -------- ------------------------- ---------------------------------------------
auto optimizer stats collection ENABLED gather_stats_prog dbms_stats.gather_database_stats_job_proc
The automatic statistic collection job is scheduled to run in the ORA$AT_WGRP_OS scheduler window group and inside a scheduling window.
Because of the sheer size of this database, the Auto Gather stats job cannot complete within the Gather Stats Window.
JOB_STATUS column for Auto Gather Stats was always stopped with JOB_INFO column showing REASON="Stop job called because associated window was closed" . This means that the Gather stats didn’t finish in the chosen Window and had to be stopped.
So, I manually gathered stats for few large tables and I have noticed that the gather stats is taking very very long time (forever sometimes) for three big tables . All these tables have lots of partitions. These 3 tables could be the reason that the "Auto Gather stats Job" is not finishing in time. So, I would like to skip these 3 tables from Auto Gather stats Job. Any idea how I can skip just these 3 tables from Auto Gather stats Job ?