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.
SQL> 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)
%name Problemas de performance nas views DBA SCHEDULER JOB RUN DETAILS   DBA SCHEDULER JOB LOG

Autor: Jhonata Lamim

MBA em Banco de Dados Oracle, formado pelo Centro Universitário de Araraquara (UNIARA), graduado em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE).

Atua com Banco de Dados Oracle desde Junho de 2010 pela Teiko Soluções em Tecnologia da Informação.

Principais atividade:
  • Implementação, migração, gerenciamento e suporte a produtos Oracle (10g, 11g, 12c, RAC), multiplataforma;
  • Monitoramento de ambientes 24×7;
  • Backup e Recovery;
  • Performance e Tuning;
  • Alta disponibilidade (HA);
  • EM database/grid/cloud control 12c/13c;
  • Conversão de databases;
  • Standby database / Oracle Data Guard;
  • Migração de dados para Oracle;

Experiência:

  • DBA Oracle, Outsourcing – Marfrig Group – Set/2013 – Atual
  • DBA Oracle, Outsourcing – Grupo Notre Dame – Intermédica – Mar/2017 – Atual
  • DBA Oralce, Outsourcing – Hospital Beneficiência Portuguesa de São Paulo – Set/2015 – Atual
  • DBA Oracle, Outsourcing – Fundação São Francisco Xavier –  Set/2015 – Fev/2017
  • DBA Oracle, Outsourcing – Unimed Grande Florianopolis – Set/2014 – Jul/2016
  • DBA Oracle, Outsourcing – Hospital Moinhos de Vento – Set/2014 – Set/2015
  • DBA Oracle, Outsourcing – Santa Casa de Misericórdia de Porto Alegre – 2013

Certificações:

  • OCS 12C – Oracle Real Application Clusters 12c Certified Implementation Specialist
  • OCS 12C – Oracle Database 12c Certified Implementation Specialist
  • OCE 11G – Oracle Database 11g: Performance Tuning
  • OCA 11G – Oracle Certified Associate Administrator
  • OCP 11G/12C – Oracle Certified Professional Administrator
  • OCS 11G – Oracle Certified Specialist
  • OPNCS 11G – Oracle Partner Network Certified Specialist
  • OCS – Oracle Linux 6 Implementation Essentials

Linkedin: https://www.linkedin.com/in/jhonata-lamim-dba-oracle-61366484/