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: statistic.sql aktuellste Version
Quellen
Support:
- Why Auto Optimizer Statistics Collection May Appear to be „Stuck“? (Doc ID 1320246.1)