8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 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.

Related 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 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.

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.LOGGING_OFF);
  DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_RUNS       =' || DBMS_SCHEDULER.LOGGING_RUNS);
  DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_FAILED_RUNS=' || DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
  DBMS_OUTPUT.put_line('DBMS_SCHEDULER.LOGGING_FULL       =' || DBMS_SCHEDULER.LOGGING_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
l_logging_level                   =128

PL/SQL procedure successfully completed.

SQL>

Before we can use the in-memory jobs we need to make sure our test user can create jobs and use the DEFAULT_IN_MEMORY_JOB_CLASS job class.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
GRANT CREATE JOB TO test;
GRANT EXECUTE ON SYS.DEFAULT_IN_MEMORY_JOB_CLASS TO test;

Create a program to associate with the jobs.

CONN test/test@pdb1

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name   => 'lightweight_program',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN NULL; END;',
    enabled        =>  TRUE);
END;
/

The following code gives a basic example of creating jobs using the LIGHTWEIGHT, IN_MEMORY_RUNTIME, IN_MEMORY_FULL and REGULAR job styles.

CONN test/test@pdb1

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'lightweight_job',
    program_name    => 'lightweight_program',
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    job_style       => 'LIGHTWEIGHT',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'in_memory_runtime_job',
    program_name    => 'lightweight_program',
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    job_style       => 'IN_MEMORY_RUNTIME',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'in_memory_full_job',
    program_name    => 'lightweight_program',
    job_style       => 'IN_MEMORY_FULL',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'regular_job',
    program_name    => 'lightweight_program',
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    job_style       => 'REGULAR',
    enabled         => TRUE);
END;
/

The persistent jobs are shown below.

-- Persistent jobs.
COLUMN job_name FORMAT A30

SELECT job_name, job_style
FROM   user_scheduler_jobs
ORDER BY 1;

JOB_NAME                       JOB_STYLE
------------------------------ -----------------
IN_MEMORY_RUNTIME_JOB          IN_MEMORY_RUNTIME
LIGHTWEIGHT_JOB                LIGHTWEIGHT
REGULAR_JOB                    REGULAR

SQL>

Clean up the persistent jobs and the program.

EXEC DBMS_SCHEDULER.drop_job('IN_MEMORY_RUNTIME_JOB');
EXEC DBMS_SCHEDULER.drop_job('LIGHTWEIGHT_JOB');
EXEC DBMS_SCHEDULER.drop_job('REGULAR_JOB');
EXEC DBMS_SCHEDULER.drop_program('lightweight_program');

Job Incompatibility Definitions

We don't always know exactly how long a job will take to complete, so if we have jobs which shouldn't overlap it can make it difficult to plan schedule times. Job incompatibility definitions allow us to define jobs that must not run at the same time. If a job is scheduled to start and an incompatible job is already running, the job will wait for the incompatible job to complete. This functionality does not guarantee a specific order. If you need to run jobs in a specific sequence you should use a job chain.

The following code creates three programs that each take 10 minutes to complete, which are in turn used by three jobs. The first job starts on the hour, the second 5 minutes later, and the third 5 minutes later again. That leaves us with overlaps.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
GRANT EXECUTE ON DBMS_LOCK TO test;


CONN test/test@pdb1

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name   => 'my_program_1',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_LOCK.sleep(600); END;',
    enabled        =>  TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_1',
    program_name    => 'my_program_1',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name   => 'my_program_2',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_LOCK.sleep(600); END;',
    enabled        =>  TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_2',
    program_name    => 'my_program_2',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=5; bysecond=0;',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name   => 'my_program_3',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_LOCK.sleep(600); END;',
    enabled        =>  TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_3',
    program_name    => 'my_program_3',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=10; bysecond=0;',
    enabled         => TRUE);
END;
/

Incompatibility definitions can be done at the job level or the program level, so both of the following examples would result in two of the jobs being incompatible.

BEGIN
  DBMS_SCHEDULER.create_incompatibility(
    incompatibility_name => 'my_prog_incomp_def', 
    object_name          => 'my_program_1,my_program_2',
    constraint_level     => 'PROGRAM_LEVEL',
    enabled              => true);
END;
/

BEGIN
  DBMS_SCHEDULER.create_incompatibility(
    incompatibility_name => 'my_job_incomp_def', 
    object_name          => 'my_job_1,my_job_2',
    constraint_level     => 'JOB_LEVEL', -- Default
    enabled              => true);
END;
/

Incompatibility definitions can be amended using the ADD_TO_INCOMPATIBILITY and REMOVE_FROM_INCOMPATIBILITY procedures, which accept comma-separated lists of objects. The objects added or removed must be of the correct type for the incompatibility, but it will not error if duplicates are added or if objects are remove that are not in the list, but the object itself must exist.

-- Program-level.
BEGIN
  DBMS_SCHEDULER.add_to_incompatibility(
    incompatibility_name => 'my_prog_incomp_def', 
    object_name          => 'my_program_2,my_program_3');
END;
/

BEGIN
  DBMS_SCHEDULER.remove_from_incompatibility(
    incompatibility_name => 'my_prog_incomp_def', 
    object_name          => 'my_program_3');
END;
/


-- Job-level.
BEGIN
  DBMS_SCHEDULER.add_to_incompatibility(
    incompatibility_name => 'my_job_incomp_def', 
    object_name          => 'my_job_2,my_job_3');
END;
/

BEGIN
  DBMS_SCHEDULER.remove_from_incompatibility(
    incompatibility_name => 'my_job_incomp_def', 
    object_name          => 'my_job_3');
END;
/

Information about job incompatibility definitions can be displayed using the {DBA|ALL|USER}_SCHEDULER_INCOMPATS and {DBA|ALL|USER}_SCHEDULER_INCOMPAT_MEMBER views.

COLUMN incompatibility_name FORMAT A30

SELECT incompatibility_name,
       constraint_level,
       enabled,
       jobs_running_count
FROM   user_scheduler_incompats
ORDER BY 1;

INCOMPATIBILITY_NAME           CONSTRAINT_LE ENABL JOBS_RUNNING_COUNT
------------------------------ ------------- ----- ------------------
MY_JOB_INCOMP_DEF              JOB_LEVEL     YES                    0
MY_PROG_INCOMP_DEF             PROGRAM_LEVEL YES                    0

SQL>


SET LINESIZE 100
COLUMN incompatibility_owner FORMAT A20
COLUMN incompatibility_name FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A20

SELECT incompatibility_owner,
       incompatibility_name,
       object_owner,
       object_name
FROM   user_scheduler_incompat_member
ORDER BY 1,2,3,4;

INCOMPATIBILITY_OWNE INCOMPATIBILITY_NAME OBJECT_OWNER         OBJECT_NAME
-------------------- -------------------- -------------------- --------------------
TEST                 MY_JOB_INCOMP_DEF    TEST                 MY_JOB_1
TEST                 MY_JOB_INCOMP_DEF    TEST                 MY_JOB_2
TEST                 MY_PROG_INCOMP_DEF   TEST                 MY_PROGRAM_1
TEST                 MY_PROG_INCOMP_DEF   TEST                 MY_PROGRAM_2

SQL>

Incompatibility definitions are removed using the DROP_INCOMPATIBILTY procedure.

BEGIN
  DBMS_SCHEDULER.drop_incompatibility(
    incompatibility_name => 'my_prog_incomp_def');
END;
/

BEGIN
  DBMS_SCHEDULER.drop_incompatibility(
    incompatibility_name => 'my_job_incomp_def');
END;
/

Clean up the jobs and programs used in these examples.

EXEC DBMS_SCHEDULER.drop_job('my_job_1', force => TRUE);
EXEC DBMS_SCHEDULER.drop_job('my_job_2', force => TRUE);
EXEC DBMS_SCHEDULER.drop_job('my_job_3', force => TRUE);
EXEC DBMS_SCHEDULER.drop_program('my_program_1');
EXEC DBMS_SCHEDULER.drop_program('my_program_2');
EXEC DBMS_SCHEDULER.drop_program('my_program_3');

Scheduler Resource Queues

We can create a user defined resource with a maximum number of units allowed, then allocate specific numbers of units to individual programs or jobs. If the resource constraint is enforced, the scheduler will not start a new job run if it will cause the number of units currently in use to exceed the limit. Instead, new jobs will be queued until resource units are available.

Create a new resource using the CREATE_RESOURCE procedure.

BEGIN
  DBMS_SCHEDULER.create_resource (
    resource_name => 'big_job_resources',
    units         => 10,
    status        => 'ENFORCE_CONSTRAINTS', -- Default
    constraint_level => 'JOB_LEVEL');       -- Default
END;
/

We can amend an existing resource using the SET_ATTRIBUTE procedure.

-- Change the units.
BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'big_job_resources',
    attribute => 'units',
    value     => 6);
END;
/

The documentation suggests the status can be set altered to IGNORE_CONSTRAINTS to prevent resource checks or BLOCKED_ALL_JOBS to block all jobs assigned to this resource, but the resetting the "status" attribute results in a failure. Instead set the "state" attribute.

-- Change the units.
BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'big_job_resources',
    attribute => 'state',
    value     => 'IGNORE_CONSTRAINTS');
END;
/

SQL>

Create some programs and jobs to associate with the resource.

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name   => 'my_program_1',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_LOCK.sleep(60); END;',
    enabled        =>  TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_1',
    program_name    => 'my_program_1',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_2',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_LOCK.sleep(60); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_job_3',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_LOCK.sleep(60); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
    enabled         => TRUE);
END;
/

Assign resource units to programs and jobs using the SET_RESOURCE_CONSTRAINT procedure. This can accept a comma-separated list of objects, but all object in the list must be of the same type.

BEGIN
  DBMS_SCHEDULER.set_resource_constraint (
    object_name   => 'my_program_1',
    resource_name => 'big_job_resources',
    units         => 4);
END;
/

BEGIN
  DBMS_SCHEDULER.set_resource_constraint (
    object_name   => 'my_job_2,my_job_3',
    resource_name => 'big_job_resources',
    units         => 2);
END;
/

Information about resource usages is displayed using the {DBA|ALL|USER}_SCHEDULER_RESOURCES views.

COLUMN resource_name FORMAT A20

SELECT resource_name,
       status,
       resource_units,
       units_used,
       jobs_running_count
FROM   user_scheduler_resources
ORDER BY 1, 2;

RESOURCE_NAME        STATUS              RESOURCE_UNITS UNITS_USED JOBS_RUNNING_COUNT
-------------------- ------------------- -------------- ---------- ------------------
BIG_JOB_RESOURCES    ENFORCE_CONSTRAINTS              6          6                  2

SQL>

The DROP_RESOURCE procedure is used to remove the resource, even if there are jobs currently associated with this resource.

BEGIN
  DBMS_SCHEDULER.drop_resource (
    resource_name => 'big_job_resources',
    force         => TRUE);
END;
/

Clean up the jobs and programs used in these examples.

EXEC DBMS_SCHEDULER.drop_job('my_job_1', force => TRUE);
EXEC DBMS_SCHEDULER.drop_job('my_job_2', force => TRUE);
EXEC DBMS_SCHEDULER.drop_job('my_job_3', force => TRUE);
EXEC DBMS_SCHEDULER.drop_program('my_program_1');

Job NLS Environment

The SET_ATTRIBUTE procedure allows you to set the NLS environment for a job using the NLS_ENV attribute. This is not documented, but you can see how it works by displaying the job DDL using the DBMS_METADATA package. This uses the job_ddl.sql script.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/


SQL> @job_ddl TEST TEST_FULL_JOB_DEFINITION


BEGIN
dbms_scheduler.create_job('"TEST_FULL_JOB_DEFINITION"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN NULL; END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('31-DEC-2018 11.41.21.209703000 AM +00:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0; bysecond=0;'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"TEST_FULL_JOB_DEFINITION"','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('"TEST_FULL_JOB_DEFINITION"');
COMMIT;
END;
/

SQL>

Prior to this the only way to alter the NLS environment for the job was to drop the job, edit the NLS environment of the session, then recreate the job.

Thanks for Gary in the comments for pointing out this functionality.

Editions

In Oracle 11gR2 an edition can be associated with a service. Since a job class can specify the service it belongs to, this means a job class can be associated with an edition. This in turn means jobs can be associated with specific editions. You can read more about editions and services here.

Scheduler Features List

Here are quick links to articles on scheduler features that span multiple versions.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.