Oracle Database provides advanced job scheduling capabilities through Oracle Scheduler (the Scheduler). This chapter introduces you to Scheduler concepts and includes the following topics:
Note:This chapter discusses the use of the Oracle-supplied
DBMS_SCHEDULERpackage to administer scheduling capabilities. You can also use Oracle Enterprise Manager (EM) as an easy-to-use graphical interface for many of the same capabilities.
See the Oracle Database PL/SQL Packages and Types Reference for
DBMS_SCHEDULER syntax and the Oracle Enterprise Manager documentation set for more information regarding EM.
Organizations have too many tasks, and manually dealing with each one can be daunting. To help you simplify these management tasks, as well as offering a rich set of functionality for complex scheduling needs, Oracle provides a collection of functions and procedures in the
DBMS_SCHEDULER package. Collectively, these functions are called the Scheduler, and they are callable from any PL/SQL program.
The Scheduler enables database administrators and application developers to control when and where various tasks take place in the database environment. These tasks can be time consuming and complicated, so using the Scheduler can help you to improve the management and planning of these tasks. In addition, by ensuring that many routine database tasks occur without manual intervention, you can lower operating costs, implement more reliable routines, minimize human error, and shorten the time windows needed.
Some typical examples of using the Scheduler are:
Database administrators can schedule and monitor recurring database maintenance jobs such as backups or nightly data warehousing loads and extracts.
Application developers can create programs and program libraries that end users can use to create or monitor their own jobs. In addition to typical database jobs, you can schedule and monitor jobs that run as part of an application suite.
The Scheduler provides complex enterprise scheduling functionality, which you can use to:
Schedule job execution based on time or events
The most basic capability of a job scheduler is the ability to schedule a job to run at a particular date and time or when a particular event occurs. The Scheduler enables you to reduce your operating costs by enabling you to schedule execution of jobs. For example, consider the situation where a patch needs to be applied to a database that is in production. To minimize disruptions, this task will need to be performed during non-peak hours. This can be easily accomplished using the Scheduler. Instead of having IT personnel manually carry out this task during non-peak hours, you can instead create a job and schedule it to run at a specified time using the Scheduler. See "Creating Jobs" for more information.
Schedule job processing in a way that models your business requirements
The Scheduler enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. This is accomplished in the following ways:
Jobs that share common characteristic and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This enables you to ensure that your critical jobs have priority and have enough resources to complete. For example, if you have a critical project to load a data warehouse, then you can combine all the data warehousing jobs into one class and give priority to it over other jobs by allocating it a high percentage of the available resources.
The Scheduler takes prioritization of jobs one step further, by providing you the ability to change the prioritization based on a schedule. Because your definition of a critical job can change over time, the Scheduler enables you to also change the priority among your jobs over that time frame. For example, you may consider the jobs to load a data warehouse to be critical jobs during non-peak hours but not during peak hours. In such a case, you can change the priority among the classes by changing the resource allocated to each class. See "Creating Job Classes" and "Creating Windows" for more information.
In addition to running jobs based on a time schedule, the Scheduler enables you start jobs in response to system or business events. Your applications can detect events and then signal the Scheduler. Depending on the type of signal sent, the Scheduler starts a specific job. An example of using events to align your job processing with business needs is to prepare event-based jobs for when a transaction fails, such as someone trying to withdraw more money from a bank account than is available. In this case, you could run jobs that check for suspicious activity in this account.
Manage and monitor jobs
There are multiple states that a job undergoes from its creation to its completion. Scheduler activity is logged and information such as the status of the job and the last run time of the job can be easily tracked. This information is stored in views and can be easily queried using Enterprise Manager or a SQL query. These views provide valuable information about jobs and their execution that can help you schedule and manage your jobs better. For example, a DBA can easily track all jobs that failed for user
scott. See "Monitoring and Managing the Scheduler".
Execute and manage jobs in a clustered environment
A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the database service where you want a job to run. See "Using the Scheduler in Real Application Clusters Environments" for more information.
The Scheduler offers a modular approach for managing tasks within the Oracle environment. Advantages of modularity include easier management of your database environment and reusability of scheduler objects when creating new tasks that are similar to existing tasks.
In the Scheduler, most components are database objects like a table, which enables you to use normal Oracle privileges.
The basic elements of the Scheduler are:
See Also:"Advanced Scheduler Concepts"
A Scheduler program object is a collection of metadata about what will be run by the Scheduler. It includes information such as the name of the program object, program action (for example, a procedure or executable name), program type (for example, PL/SQL and Java stored procedures or PL/SQL anonymous blocks) and the number of arguments required for the program.
A program is a separate entity from a job. Jobs run at a certain time or because a certain event occurred, and invoke a certain program. Jobs can be created that point to existing program objects, which means that different jobs can use the same program and run the program at different times and with different settings. Given the right privileges, different users can thus use the same program without having to redefine it. This enables the creation of program libraries, where users can select from a list of existing programs.
Because a Scheduler program can invoke a stored procedure or other executable that requires arguments, a means is provided to store default values for those arguments as program attributes.
A schedule specifies when and how many times a job is executed. Jobs can be scheduled for processing at a later time or immediately. For jobs to be executed at a later time, the user can specify a date and time when the job should start. For jobs that repeat over a period of time, an end date and time can be specified, which indicates when the schedule expires.
A schedule can also specify that a job be executed when a certain event occurs, such as a badge swipe or inventory dropping below a threshold. For more information on events, see "Events".
Similar to programs, schedules are objects that can be named and saved in the database. Users can then share named schedules. For example, the end of a business quarter may be a common time frame for many jobs. Instead having to define an end-of-quarter schedule each time a new job is defined, job creators can point to a named schedule.
Some examples of schedules you might use to control time-based jobs are:
Run on Wednesday, December 26th, 2001 at 2pm
Run every Monday, at 8am, starting on December 26th, 2001, and ending on January 31st, 2002
Run on every working day
See "Creating Schedules" for more information.
A job is a user-defined task that is scheduled to run one or more times. It is a combination of what needs to be executed (the action) and when (the schedule). Users with the right privileges can create jobs either by:
Specifying as job attributes both the action to perform (for example, an inline PL/SQL anonymous block) and the schedule by which to perform the action (for example, every day at noon, or when a certain event occurs)
Specifying as job attributes the names of an existing program object and an existing schedule object
Like programs and schedules, jobs are objects that can be named and saved in the database.
You can specify job arguments to customize a named program object. Job arguments override the default argument values in the program object, and provide values for those program arguments that have no default value. In addition, job arguments can provide argument values to an inline action (for example, a stored procedure) that the job specifies.
A job cannot be enabled until all required program argument values are defined, either as defaults in a referenced program object, or as job arguments.
A common example of a job is one that runs a set of nightly reports. If different departments require different reports, you can create a program for this task that can be shared among different users from different departments. The program action would be to run a reports script, and the program would have one argument: the department number. Each user can then create a job that points to this program, and can specify the department number as a job argument.
See "Creating Jobs" for more information.
A job instance represents a specific run of a job. Jobs that are scheduled to run only once will have only one instance. Jobs that have a repeating schedule will have multiple instances, with each run of the job representing an instance. For example, a job that is scheduled to run on Tuesday, Oct. 8th 2002 will have one instance. A job that runs daily at noon for a week has seven instances, one for each time the job runs.
When a job is created, only one entry is added to the Scheduler's job table to represent the job. Each time the job runs, an entry is added to the job log. Therefore, if you create a job that has a repeating schedule, you will find one entry in the job views and multiple entries in the job log. Each job instance log entry provides information about a particular run, such as the job completion status and the start and end time. Each run of the job is assigned a unique log id which is used in both the job log and job run details views.
See "How to View Scheduler Information" for more information.
An event is a message sent by one application or system process to another to indicate that some action or occurrence has been detected. An event is raised (sent) by one application or process, and consumed (received) by one or more applications or processes.
There are two kinds of events in the Scheduler:
Events raised by the Scheduler
The Scheduler can raise an event to indicate state changes that occur within the Scheduler itself. For example, the Scheduler can raise an event when a job starts, when a job completes, when a job exceeds its allotted run time, and so on. The consumer of the event is an application that takes some action in response to the event.
For example, if due to a high system load, a job is still not started 30 minutes after the scheduled start time, the Scheduler can raise an event that causes a handler application to send a notification e-mail to the database administrator.
Events raised by an application
An application can raise an event to be consumed by the Scheduler. The Scheduler reacts to the event by starting a job. You can create a schedule that references an event instead of containing date, time, and recurrence information. If a job is assigned to such a schedule (an event schedule), the job runs when the event is raised. You can also create a job that has no schedule assigned and that directly references an event as the means to start the job.
For example, when an inventory tracking system notices that the inventory has gone below a certain threshold, it can raise an event that starts an inventory replenishment job.
The Scheduler uses Oracle Streams Advanced Queuing to raise and consume events. When raising a job state change event, the Scheduler enqueues a message onto a default event queue. Applications subscribe to this queue, dequeue event messages, and take appropriate action. When raising an event to notify the Scheduler to start a job, an application enqueues a message onto a queue that was specified when setting up the job.
Oracle Streams Advanced Queuing User's Guide and Reference for more information on Advanced Queuing
"Using Events" for more information on Events
A chain is a grouping of programs that are linked together for a single, combined objective. An example of a chain might be "run program A and then program B, but only run program C if programs A and B complete successfully, otherwise run program D." A Scheduler job can point to a chain instead of pointing to a single program object.
Each position within a chain of interdependent programs is referred to as a step. Typically, after an initial set of chain steps has started, the execution of successive steps depends on the completion of one or more previous steps. Each step can point to one of the following:
Another chain (a nested chain)
A step that points to an event waits until the specified event is raised. If the event occurs, the step completes successfully.
Multiple steps in the chain can invoke the same program or nested chain.
In a sense, a chain resembles a decision tree, with many possible paths for selecting which steps run and when. A list of rules is used to decide which actions to perform at any particular stage. An example of a rule is "If step 2 fails or step 3 fails, wait an hour and then start step 4."
While a job pointing to a chain is running, the current state of all steps of the running chain can be monitored.
A typical situation where you might want to create a chain is to combine the different programs necessary for a successful financial transaction.
See "Using Chains" for more information.
To define what is executed and when, you assign relationships among programs, jobs, and schedules. Figure 26-1 illustrates examples of such relationships.
To understand Figure 26-1, consider a situation where tables are being analyzed. In this example,
P1 would be a program to analyze a table using the
DBMS_STATS package. The program has an input parameter for the table name. Two jobs,
J2, both point to the same program, but each supplies a different table name. Additionally, schedule
S1 could specify a run time of 2:00 a.m. every day. The end result would be that the two tables named in
J2 are analyzed daily at 2:00 a.m.
J4 points to no other entity, so it is self-contained with all relevant information defined in the job itself.
S2 illustrate that you can leave a program or schedule unassigned if you want. You could, for example, create a program that calculates a year-end inventory and temporarily leave it unassigned to any job.
Many Scheduler capabilities enable database administrators to control more advanced aspects of scheduling. Typically, these topics are not as important for application developers.
This section discusses the following advanced topics:
Job classes provide a way to:
Assign the same set of attribute values to member jobs
Each job class specifies a set of attributes, such as logging level. When you assign a job to a job class, the job inherits those attributes. For example, you can specify the same policy for purging log entries for all payroll jobs.
Set service affinity for member jobs
You can set the
service attribute of a job class to a desired database service name. This determines the instances in a Real Application Clusters environment that run the member jobs, and optionally the system resources that are assigned to member jobs. See "Service Affinity when Using the Scheduler" for more information.
Set resource allocation for member jobs
Job classes provide the link between the Database Resource Manager and the Scheduler, because each job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified consumer group, and are assigned resources according to settings in the current resource plan.
Alternatively, you can leave the
NULL and set the
service attribute of a job class to a desired database service name. That service can in turn be mapped to a resource consumer group.
See Chapter 24, "Using the Database Resource Manager" for more information on mapping services to consumer groups.
Group jobs for prioritization
Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the class are scheduled to start at the same time, the one with the higher priority takes precedence. This ensures that you do not have a less important job preventing the timely completion of a more important one.
If two jobs have the same assigned priority value, the job with the earlier start date takes precedence. If no priority is assigned to a job, its priority defaults to 3.
Note:Job priorities are used only to prioritize among jobs in the same class.
There is no guarantee that a high priority job in class A will be started before a low priority job in class B, even if they share the same schedule. Prioritizing among jobs of different classes depends on the current resource plan and on the designated resource consumer group or service name of each job class.
When defining job classes, you should try to classify jobs by functionality. Consider dividing jobs into groups that access similar data, such as marketing, production, sales, finance, and human resources.
Some of the restrictions to keep in mind are:
A job must be part of exactly one class. When you create a job, you can specify which class the job is part of. If you do not specify a class, the job automatically becomes part of the class
Dropping a class while there are still jobs in that class results in an error. You can force a class to be dropped even if there are still jobs that are members of that class, but all jobs referring to that class are then automatically disabled and assigned to the class
DEFAULT_JOB_CLASS. Jobs belonging to the dropped class that are already running continue to run under class settings determined at the start of the job.
You create windows to change resource allocation among jobs during various time periods of the day, week, and so on. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am".
Windows work with job classes to control resource allocation. Each window specifies the resource plan to activate when the window opens (becomes active), and each job class specifies a resource consumer group or specifies a database service, which can map to a consumer group. A job that runs within a window therefore has resources allocated to it according to the consumer group of its job class and the resource plan of the window.
Figure 26-2 shows a workday that includes two windows. In this configuration, jobs that belong to the job class that links to
Consumer Group 1 get more resources in the morning than in the afternoon. The opposite is true for jobs in the job class that links to
Consumer Group 2.
See Chapter 24, "Using the Database Resource Manager" for more information on resource plans and consumer groups.
You can assign a priority to each window. If windows overlap, the window with the highest priority is chosen over other windows with lower priorities. The Scheduler automatically opens and closes windows as window start times and end times come and go.
A job can name a window in its
schedule_name attribute. The Scheduler then starts the job when the window opens. If a window is already open, and a new job is created that points to that window, the job is not started until the next time the window opens.
See "Creating Windows" for examples of creating and using windows.
Note:If necessary, you can temporarily block windows from switching the current resource plan. For more information, see "Enabling the Database Resource Manager", or the discussion of the
DBMS_RESOURCE_MANAGER.SWITCH_PLANpackage procedure in Oracle Database PL/SQL Packages and Types Reference.
You can group windows for ease of use in scheduling jobs. If a job must run during multiple time periods throughout the day, week, and so on, you can create a window for each time period, and then add the windows to a window group. You can then set the
schedule_name attribute of the job to the name of this window group, and the job executes during all the time periods specified in the window group.
For example, if you had a window called "Weekends" and a window called "Weeknights," you could add these two windows to a window group called "Downtime." The data warehousing staff could then create a job to run queries according to this Downtime window group—on weeknights and weekends—when the queries could be assigned a high percentage of available resources.
If a window in a window group is already open, and a new job is created that points to that window group, the job is not started until the next window in the window group opens.
See "Creating Window Groups" for examples of creating window groups.
This section discusses the Scheduler's architecture, and describes:
Figure 26-3 illustrates how jobs are handled by the database.
The job table is a container for all the jobs, with one table per database. The job table stores information for all jobs such as the owner name or the level of logging. You can find this information in the
Jobs are database objects, and can therefore accumulate and take up too much space. To avoid this, job objects are automatically dropped by default after completion. This behavior is controlled by the
auto_drop job attribute.
See "How to View Scheduler Information" for the available job views and administration.
The job coordinator is a background process (
cjqNNN) that is automatically started when jobs must be run, or windows must be opened. It is automatically brought down after a sustained period of Scheduler inactivity. The job coordinator:
Controls and spawns the job slaves
Queries the job table
Picks up jobs from the job table on a regular basis and places them in a memory cache. This improves performance by avoiding going to the disk
Takes jobs from the memory cache and passes them to job slaves for execution
Cleans up the job slave pool when slaves are no longer needed
Goes to sleep when no jobs are scheduled
Wakes up when a new job is about to be executed or a job was created using the
Upon database startup after an abnormal database shutdown, recovers any jobs that were running.
You do not need to set when the job coordinator checks the job table; the system chooses the time frame automatically.
One job coordinator is used per instance. This is also the case in RAC environments.
See Also:"How to View Scheduler Information" for job coordinator administration and "Using the Scheduler in Real Application Clusters Environments" for RAC information
When a job is picked for processing, the job slave:
Gathers all the metadata needed to run the job. As an example, arguments of the program and privilege information.
Starts a database session as the owner of the job, starts a transaction, and then starts executing the job.
Once the job is complete, the slave commits and ends the transaction.
Closes the session.
Job slaves actually execute the jobs you submit. They are awakened by the job coordinator when it is time for a job to be executed. They gather metadata to run the job from the job table.
When a job is done, the slaves:
Reschedule the job if required
Update the state in the job table to reflect whether the job has completed or is scheduled to run again
Insert an entry into the job log table
Update the run count, and if necessary, failure count and retry count
Look for new work (if none, they go to sleep)
The Scheduler dynamically sizes the slave pool as required.
In a Real Application Clusters (RAC) environment, the Scheduler uses one job table for each database and one job coordinator for each instance. The job coordinators communicate with each other to keep information current. The Scheduler attempts to balance the load of the jobs of a job class across all available instances when the job class has no service affinity, or across the instances assigned to a particular service when the job class does have service affinity.
Figure 26-4 illustrates a typical RAC architecture, with each instance's job coordinator exchanging information with the others.
The Scheduler enables you to specify the database service under which a job should be run (service affinity). This ensures better availability than instance affinity because it guarantees that other nodes can be dynamically assigned to the service if an instance goes down. Instance affinity does not have this capability, so, when an instance goes down, none of the jobs with an affinity to that instance will be able to run until the instance comes back up. Figure 26-5 illustrates a typical example of how services and instances could be used.
In Figure 26-5, you could change the properties of the services and the Scheduler will automatically recognize the change.
Each job class can specify a database service. If a service is not specified, the job class belongs to an internal service that is guaranteed to be mapped to every running instance.