8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- In-Memory Jobs
- Job Incompatibility Definitions
- Scheduler Resource Queues
- Job NLS Environment
- Editions
- Scheduler Features List
Related articles.
- All Scheduler Articles
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
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.
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 theDEFAULT_IN_MEMORY_JOB_CLASS
job class, which has a logging level ofDBMS_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.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:
- Scheduler: In-Memory Jobs
- Scheduler: Job Incompatibilities
- Scheduler: Resource Queues
- DBMS_SCHEDULER
- All Scheduler Articles
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
Hope this helps. Regards Tim...