Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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:

<code sql>
begin
 DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');“
end;
/
</code>


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;

<code>
---------------------------- 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

</code>

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)