Problemas de performance nas views DBA_SCHEDULER_JOB_RUN_DETAILS - DBA_SCHEDULER_JOB_LOG

A quantidade de registros influencia diretamente na performance das consultas nas tabelas e visões do Oracle não será diferente. Por isso o Oracle possuí rotinas que são "encarregadas" de realizar a "limpeza" dos logs, de acordo com uma retenção especifica. 
Porém se este mecanismo não funcionar de forma adequada, podemos ter uma degradação na performance de consultas que utilizam estas tabelas e visões.

No exemplo abaixo, podemos identificar uma degradação na performance de algumas consultas na DBA_SCHEDULER_JOB_RUN_DETAILS e DBA_SCHEDULER_JOB_LOG. Verificando a quantidade de registros na mesmas, constatamos a existencia de registros desde 25 de janeiro de 2017.
QL> set timing on
SQL> select min(LOG_DATE) from  DBA_SCHEDULER_JOB_RUN_DETAILS;

MIN(LOG_DATE)
---------------------------------------------------------------------------
28-MAY-17 06.54.44,788384 AM -03:00

Elapsed: 00:00:00.19
SQL>
SQL>
SQL>
SQL>
SQL> SELECT count(1)
  FROM DBA_SCHEDULER_JOBS J
 WHERE (J.STATE NOT IN ('SCHEDULED', 'RUNNING') OR J.FAILURE_COUNT > 0)
   AND NOT EXISTS
 (SELECT 1
          FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD
         WHERE (JRD.ACTUAL_START_DATE =
               (SELECT MAX(AUX.ACTUAL_START_DATE)
                   FROM DBA_SCHEDULER_JOB_RUN_DETAILS AUX
                  WHERE AUX.JOB_NAME = JRD.JOB_NAME) AND
               JRD.STATUS = 'FAILED' AND JRD.JOB_NAME = J.JOB_NAME));  2    3    4    5    6    7    8    9   10   11

  COUNT(1)
----------
         7

Elapsed: 00:00:26.05
SQL> select min(LOG_DATE) from  DBA_SCHEDULER_JOB_LOG;


MIN(LOG_DATE)
---------------------------------------------------------------------------
28-MAY-17 06.00.02,007816 AM -03:00

Elapsed: 00:00:45.12
SQL> SQL> select min(LOG_DATE) from  DBA_SCHEDULER_JOB_RUN_DETAILS;

MIN(LOG_DATE)
---------------------------------------------------------------------------
28-MAY-17 06.54.44,788384 AM -03:00

Elapsed: 00:00:00.10
SQL> select count(1) from  DBA_SCHEDULER_JOB_RUN_DETAILS;

  COUNT(1)
----------
     49507

Elapsed: 00:00:01.02
SQL> select count(1) from DBA_SCHEDULER_JOB_LOG;

  COUNT(1)
----------
     52472

Elapsed: 00:00:00.09
SQL>
Por default a retenção deste log é de 30 dias (Doc 749440.1), sendo controlado pelo parâmetro log_history, utilizado pela procedure PURGE_LOG. 
Então vamos verificar se a retenção do mesmo está definida para o valor padrão ou se existe alguma alteração. Para isso, podemos utilizar a procedure abaixo (colaboração do DBA Anderson Graf).
SQL> set serveroutput on;
DECLARE
  x VARCHAR2(100);
BEGIN
   dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
   dbms_output.put_line('LH: ' || x);
END;
 /  SQL>   2    3    4    5    6    7
LH: 30

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL
Neste caso, nossa retenção está como default (30 dias). Porém a limpeza não esta sendo executado, como pode ser constatado nas evidencias acima.
Agora estaremos abordando as formas de efetuar a limpeza das mesmas.

É possivel alterar a retenção através da procedure DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE. No exemplo abaixo vamos alterar para 15 dias.
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','15');
Também podemos "personalizar" uma retenção específica de acordo com o job.
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('job_name','log_history','15'); 
Para efetuar uma limpeza de todos os logs sem considerar a retenção, basta executar a procedure DBMS_SCHEDULER.PURGE_LOG.
exec DBMS_SCHEDULER.PURGE_LOG();
Se quisermos efetuar a limpeza de acordo uma uma retenção específica, podemos executar a procedure PURGE_LOG, passando os parâmetros log_history e a retenção, conforme abaixo.
exec DBMS_SCHEDULER.PURGE_LOG(log_history => 90);

SQL> exec DBMS_SCHEDULER.PURGE_LOG(log_history => 90);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.44
SQL> select min(LOG_DATE) from  DBA_SCHEDULER_JOB_LOG;

MIN(LOG_DATE)
---------------------------------------------------------------------------
20-OCT-17 11.00.01,016819 PM -02:00

Elapsed: 00:00:00.10
SQL>  select min(LOG_DATE) from  DBA_SCHEDULER_JOB_RUN_DETAILS;

MIN(LOG_DATE)
---------------------------------------------------------------------------
20-OCT-17 11.24.16,675612 PM -02:00

Elapsed: 00:00:00.09
SQL> 
A limpeza também pode ser realizada de acordo com um job específico. Para isso basta passar os parametros log_history e job_name para a procedure PURGE_LOG. No exemplo abaixo, estaremos realizando uma limpeza dos logs para o job KILL_SESSION_AUTO_JOB.
exec DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'KILL_SESSION_AUTO_JOB, sys.DEFAULT_JOB_CLASS');

SQL> select job_name, JOB_CLASS from dba_scheduler_jobs where job_name='KILL_SESSION_AUTO_JOB';

JOB_NAME                       JOB_CLASS
------------------------------ ------------------------------
KILL_SESSION_AUTO_JOB          DEFAULT_JOB_CLASS

Elapsed: 00:00:00.18
SQL>  select min(LOG_DATE) from DBA_SCHEDULER_JOB_LOG where job_name='KILL_SESSION_AUTO_JOB';

MIN(LOG_DATE)
---------------------------------------------------------------------------
18-DEC-17 09.02.01,125723 PM -02:00

Elapsed: 00:00:00.13

SQL> exec DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'KILL_SESSION_AUTO_JOB, sys.DEFAULT_JOB_CLASS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.02
SQL> select min(LOG_DATE) from DBA_SCHEDULER_JOB_LOG where job_name='KILL_SESSION_AUTO_JOB';

MIN(LOG_DATE)
---------------------------------------------------------------------------
08-JAN-18 09.40.01,921620 AM -02:00

Elapsed: 00:00:00.07
SQL> 
Após Definir a retenção para 30 dias e realizar um limpeza, mantendo os dados dos últimos 10 dias, vamos verificar a execução do nosso processo que estava tendo lentidão novamente.
SQL> SELECT count(1)
  FROM DBA_SCHEDULER_JOBS J
 WHERE (J.STATE NOT IN ('SCHEDULED', 'RUNNING') OR J.FAILURE_COUNT > 0)
   AND NOT EXISTS
 (SELECT 1
          FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD
         WHERE (JRD.ACTUAL_START_DATE =
               (SELECT MAX(AUX.ACTUAL_START_DATE)
                   FROM DBA_SCHEDULER_JOB_RUN_DETAILS AUX
                  WHERE AUX.JOB_NAME = JRD.JOB_NAME) AND
               JRD.STATUS = 'FAILED' AND JRD.JOB_NAME = J.JOB_NAME));  2    3    4    5    6    7    8    9   10   11

  COUNT(1)
----------
         7

Elapsed: 00:00:00.24
Conforme teste acima o tempo de execução passou de 26,05 segundos para 0,24 segundos após a limpeza.

Referencias: 
Queries against some DBMS_SCHEDULER Views Hang. DBA_AUTOTASK_CLIENT - DBA_SCHEDULER_JOB_RUN_DETAILS - DBA_SCHEDULER_JOB_LOG (Doc ID 2189895.1)
DBMS_SCHEDULER.PURGE Not Removing Entries from DBA_SCHEDULER_JOB_RUN_DETAILS (Doc ID 749440.1)
SHARE

Jhonata Lamim

  • Image
  • Image
  • Image
  • Image
  • Image