=====Datenbank Auto Statistik Job überwachen - Fehlerursache bei veralteten Statistiken=====
In einer Datenbank Umgebung fällt auf, das die Statistiken nicht mehr regelmäßig erfasst werden.
Eine Kontrolle des BSLN_MAINTAIN_STATS_ JOB zeigt aber das der Job eingeplannt ist und auch regelmäßig ausgeführt wird.
Bei Kontrolle der Oracle Scheduler Windows für den Default Maintainance Plan zeigt aber, das hier ein Window seit langen immer noch "offen" ist. In diesem Fall wird der eigentliche Statisik Task aber dann nie wieder aktiv, da immer nur ein Fenster zur selben Zeit aktive sein kann, und das alte schon längst abgelaufen ist.
Mit folgenden PL/SQL kann das Fenster wieder geschlossen werden:
begin
DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');“
end;
/
Hintergrund:
Wird die DB gestoppt, während ein Maintaince Window noch "offen" ist, wird das Fenster nicht mehr geschlossen wenn die DB erneut startet!
Für die Kontrolle kann folgendes Script verwendet werden;
---------------------------- Check the Scheduler for the statistic job -------------------------------
column job_name format a30 heading "Job|Name"
column run_count format 99999 heading "Run|Count"
column failure_count format 99999 heading "Failure|Count"
column last_start_date format a18 heading "Last|run date"
column next_run_date format a18 heading "Next|run date"
column client_name format a35 heading "Job|Name"
column status format a10 heading "Job|status"
column mean_job_duration format 999G999 heading "Mean|duration"
column mdl7 format 999G999 heading "Max|duration"
column next_start_date format a38 heading "Next|run"
column window_group_name format a18 heading "Window|group"
column job_duration format 999G999 heading "Duration|Minutes"
column job_start_time format a18 heading "Job|Start time"
column log_date format a18 heading 'Log Date'
column owner format a10 heading 'Owner'
column job_name format a30 heading 'Job'
column status format a10 heading 'Status'
column actual_start_date format a32 heading 'Actual|Start|Date'
column error# format 999999 heading 'Error|Nbr'
column window_start_time format a18 heading 'Windows|Start'
column job_status format a10 heading 'Status'
column window_name format a20 heading 'Windows|Name'
column window_next_time format a38 heading 'Window|next Time'
ttitle left "Job Scheduler Information -- Oracle Statistic Auto Job " skip 2
select OWNER
,JOB_NAME
,RUN_COUNT
,FAILURE_COUNT
,to_char(LAST_START_DATE, 'DD.MM.YYYY HH24:MI') as LAST_START_DATE
,to_char(NEXT_RUN_DATE, 'DD.MM.YYYY HH24:MI') as NEXT_RUN_DATE
from dba_scheduler_jobs
where job_name like '%STAT%'
/
prompt ... GATHER_STATS_JOB 10g job should not run in 11g!
prompt ... to delete use as sys user: exec dbms_scheduler.drop_job(job_name => 'SYS.GATHER_STATS_JOB');
prompt
ttitle left "Job Scheduler BSLN_MAINTAIN_STATS_JOB History " skip 2
select log_id
,to_char(log_date, 'DD.MM.YYYY HH24:MI') as log_date
,owner
,job_name
,status
,to_char(actual_start_date, 'DD.MM.YYYY HH24:MI') as actual_start_date
,error#
from dba_scheduler_job_run_details
where JOB_NAME = 'BSLN_MAINTAIN_STATS_JOB'
order by actual_start_date
/
ttitle left "Job Scheduler Window Settings " skip 2
prompt
prompt check if the window is not activ in the past!
prompt
column check_active format a10 heading 'Check|if ok'
select window_name
,to_char(last_start_date, 'DD.MM.YYYY HH24:MI') as last_start_date
,enabled
,active
,decode(active, 'TRUE', '<==CHECK IF POSSIBLE', '-') as check_active
from dba_scheduler_windows
order by last_start_date
/
prompt
prompt ... if a window is still open in the past, close the window manually
prompt ... with : EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');
prompt ..
ttitle left "Check Window history" skip 2
prompt
prompt check Window history
prompt
select window_name
,optimizer_stats
,window_next_time
,autotask_status
from dba_autotask_window_clients
/
ttitle left "Check Auto tasks " skip 2
prompt
prompt if autotask is really enabled
prompt
select client_name
,status
from dba_autotask_task
/
ttitle left "Check Auto tasks Settings" skip 2
select c.client_name
,c.status
,w.window_group_name
,w.next_start_date as next_start_date
,extract(hour from c.mean_job_duration) * 60 + extract(minute from c.mean_job_duration) as mean_job_duration
,extract(hour from c.max_duration_last_7_days) * 60 + extract(minute from c.max_duration_last_7_days) as mdl7
from dba_autotask_client c
,dba_scheduler_window_groups w
where w.window_group_name = c.window_group
order by 1
/
prompt .... if task is disabled
prompt .... exec DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL)
prompt ....
prompt
ttitle left "Check Auto tasks history" skip 2
prompt
prompt if empty no history!!
prompt
select client_name
,window_name
,to_char(window_start_time, 'dd.mm.yyyy hh24:mi') as window_start_time
--, window_duration
--, job_name
,job_status
,to_char(job_start_time, 'dd.mm.yyyy hh24:mi') as job_start_time
,extract(hour from job_duration) * 60 + extract(minute from job_duration) as job_duration
,job_error
--, job_info
from dba_autotask_job_history
/
ttitle off
siehe auch: [[http://orapowershell.codeplex.com/SourceControl/latest#sql/statistic.sql| statistic.sql aktuellste Version]]
==== Quellen ====
* http://www.oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1.php
* http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_autotaskadm.htm#CHDJCFCI
* http://docs.oracle.com/cd/E25178_01/server.1111/e16638/stats.htm#i41448
Support:
* Why Auto Optimizer Statistics Collection May Appear to be "Stuck"? (Doc ID 1320246.1)