DBA Hub

📋Steps in this guide1/6

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.

oracle 12cconfigurationintermediate
by OracleDba
21 views
1

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. - : 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 job class, which has a logging level of , not none ( ) as suggested by the documentation. That means there is no logging for successful runs, hence better performance. - : 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 job class here. Before we can use the in-memory jobs we need to make sure our test user can create jobs and use the job class. Create a program to associate with the jobs. The following code gives a basic example of creating jobs using the , , and job styles. The persistent jobs are shown below. Clean up the persistent jobs and the program.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
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>

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

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;
/

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;
/

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

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');
2

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. 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. Incompatibility definitions can be amended using the and 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. Information about job incompatibility definitions can be displayed using the and views. Incompatibility definitions are removed using the procedure. Clean up the jobs and programs used in these examples.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
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;
/

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;
/

-- 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;
/

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>

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

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

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');
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 procedure. We can amend an existing resource using the procedure. The documentation suggests the status can be set altered to to prevent resource checks or to block all jobs assigned to this resource, but the resetting the "status" attribute results in a failure. Instead set the "state" attribute. Create some programs and jobs to associate with the resource. Assign resource units to programs and jobs using the procedure. This can accept a comma-separated list of objects, but all object in the list must be of the same type. Information about resource usages is displayed using the views. The procedure is used to remove the resource, even if there are jobs currently associated with this resource. Clean up the jobs and programs used in these examples.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
BEGIN
  DBMS_SCHEDULER.create_resource (
    resource_name => 'big_job_resources',
    units         => 10,
    status        => 'ENFORCE_CONSTRAINTS', -- Default
    constraint_level => 'JOB_LEVEL');       -- Default
END;
/

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

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

SQL>

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;
/

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;
/

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>

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

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');
4

Job NLS Environment

The procedure allows you to set the NLS environment for a job using the attribute. This is not documented, but you can see how it works by displaying the job DDL using the package. This uses the job_ddl.sql script. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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>
5

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

Scheduler Features List

Here are quick links to articles on scheduler features that span multiple versions. - Simple Example - Programs - Schedules - Jobs - Job Classes - Windows - Windows Groups - Enable, Disable and Attributes - Configuring The Scheduler - Calendar Syntax Examples - Extracting DDL - Scheduler Views - Time Zones - Implicit Commit - Jobs That Raise Events - Event-Based Jobs - Job Chains - Remote External Jobs - Credentials - Detached Jobs - Lightweight Jobs - Scheduler Support for Data Guard - Email Notification - File Watcher - Remote Database Jobs - Multiple Destination Jobs - Script Jobs - DBMS_CREDENTIAL - Data Guard Database Rolling Upgrade Support - Multitenant Option and the Scheduler - In-Memory Jobs - Job Incompatibility Definitions - Scheduler Resource Queues For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!