26 Scheduler Concepts

What Can the Scheduler Do?

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.

Programs

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.

See "Creating Programs" for more information about programs, and "Jobs" for an overview of jobs.

Schedules

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.

Jobs

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.

Job Arguments

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.

Events

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.

Chains

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:

• A program

• Another chain (a nested chain)

• An event

  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.

How Programs, Jobs, and Schedules are Related

To define what is executed and when, you assign relationships among programs, jobs, and schedules. Figure 26-1 illustrates examples of such relationships.

Figure 26-1 Relationships Among Programs, Jobs, and Schedules

Description of "Figure 26-1 Relationships Among Programs, Jobs, and Schedules"

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, J1 and 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 J1 and J2 are analyzed daily at 2:00 a.m.

Note that J4 points to no other entity, so it is self-contained with all relevant information defined in the job itself. P2, P9 and 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.

Job Classes

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 resource_consumer_group attribute 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 DEFAULT_JOB_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.

Windows

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.

Figure 26-2 Windows help define the resources that are allocated to jobs

Description of "Figure 26-2 Windows help define the resources that are allocated to jobs"

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.

Window Groups

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.

The Job Table

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 *_SCHEDULER_JOBS views.

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

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

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

How Jobs Execute

When a job is picked for processing, the job slave:

1. Gathers all the metadata needed to run the job. As an example, arguments of the program and privilege information.

2. Starts a database session as the owner of the job, starts a transaction, and then starts executing the job.

3. Once the job is complete, the slave commits and ends the transaction.

4. Closes the session.

Job Slaves

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

• Clean up

• Look for new work (if none, they go to sleep)

The Scheduler dynamically sizes the slave pool as required.

Using the Scheduler in Real Application Clusters Environments

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.

Figure 26-4 RAC Architecture and the Scheduler

Description of "Figure 26-4 RAC Architecture and the Scheduler"

Service Affinity when Using the Scheduler

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.

Figure 26-5 Service Affinity and the Scheduler

Description of "Figure 26-5 Service Affinity and the Scheduler"

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.