scheduler-enhancements-12cr

scheduler-enhancements-12cr
[41]Home » [42]Articles » [43]12c » Here

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 2
(12.2)

Oracle Database 12c Release 2 continues to extend the functionality of
the Oracle scheduler with in-memory jobs, job incompatibility
definitions and scheduler resource queues.
* [44]In-Memory Jobs
* [45]Job Incompatibility Definitions
* [46]Scheduler Resource Queues

Related articles.
* [47]All Scheduler Articles

In-Memory Jobs

Jobs are schema objects and as such are persisted to disk and include
comprehensive logging of job runs. This is great for conventional jobs,
but can represent an overhead if you want to create many short-lived
jobs, like when you use jobs to parallelize a process. Oracle 11gR1
introduced [48]lightweight jobs to reduce this overhead, but Oracle 12c
Release 2 introduced in-memory jobs to reduce the overhead even
further.

In-memory jobs come in two forms.
* IN_MEMORY_RUNTIME : These are used when you need an in-memory job
that has a repeat interval. This is a standard lightweight job, but
it is assigned to the DEFAULT_IN_MEMORY_JOB_CLASS job class, which
has a logging level of DBMS_SCHEDULER.LOGGING_FAILED_RUNS, not none
(DBMS_SCHEDULER.LOGGING_OFF) as suggested by the documentation.
That means there is no logging for successful runs, hence better
performance.
* IN_MEMORY_FULL : A job that must be associated with a program,
can't have a repeat interval and persists nothing to disk. These
jobs use a little more memory, but since they persist nothing to
disk they have reduced overheard and zero redo generation as a
result of the job mechanism itself. As nothing is persisted to disk
they are only present on the instance that created them.

We can see the logging level associated with the
DEFAULT_IN_MEMORY_JOB_CLASS job class here.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

SET SERVEROUTPUT ON
DECLARE
l_logging_level NUMBER;
BEGIN
DBMS_SCHEDULER.get_attribute (
name => 'SYS.DEFAULT_IN_MEMORY_JOB_CLASS',
attribute => 'logging_level',
value => l_logging_level);

DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_OFF =' || DBMS_SCHEDULER.L
OGGING_OFF);
DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_RUNS =' || DBMS_SCHEDULER.L
OGGING_RUNS);
DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_FAILED_RUNS=' || DBMS_SCHEDULER.L
OGGING_FAILED_RUNS);
DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_FULL =' || DBMS_SCHEDULER.L
OGGING_FULL);
DBMS_OUTPUT.put_line('l_logging_level =' || l_logging_level)
;
END;
/
DBMS_SCHEDULER.LOGGING_OFF =32
DBMS_SCHEDULER.LOGGING_RUNS =64
DBMS_SCHEDULER.LOGGING_FAILED_RUNS=128
DBMS_SCHEDULER.LOGGING_FULL =256
Source .... : https://oracle-base.com/articles/12c/scheduler-enhancements-12cr2
--------------------------