26 Overview of 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

  The most basic capability of a job scheduler is the ability to schedule a job to run at a particular date and time. 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.

• Reuse existing programs and schedules

  A program is a collection of metadata about what will be run by the Scheduler. A schedule specifies when the program will be executed. Programs and schedules are separate entities and are stored in the database as independent database objects making them available for reuse.

  You can create a job using existing programs and schedules. Parameter values specified when creating the job will override the default values specified in the program, thus enabling other users to customize the program to meet their needs. This approach enables different users to create different jobs using existing programs and schedules without having to redefine them each time. For example, analyzing tables is a common task that most database administrators perform on a regular basis. Because the metadata of what the Scheduler needs to run is the same in all cases, you can create one program that takes the table name as a parameter to analyze tables. This way, only the table name is different. By specifying the table name when creating each job, the same program can be reused by different jobs, thus eliminating the need to redefine the program each time. Different jobs can also schedule the same program to run at different times by using different schedules. See "Creating Programs" 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 on two levels:

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

• 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 time to completion 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 service where you want a job to run. See "Using the Scheduler in RAC Environments" for more information.

General Rules for all Database Objects

The following rules apply for all objects when using the Scheduler:

• If you try to drop an object that does not exist, a PL/SQL exception is raised saying that the object does not exist.

• If you try to enable or disable an object that is already enabled or disabled, no error is generated.

• If you try to alter an object that does not exist, an exception is raised saying that the object does not exist.

The basic elements of the Scheduler are:

• Programs

• Schedules

• Jobs

Programs

A program is a collection of metadata about what will be run by the Scheduler. It includes information such as the name of the program, 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 can be created using existing programs, which means that different jobs can use the same program. Given the right privileges, different users can 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.

See "Creating Programs" for more information.

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 also has a limit associated with it, which is expressed as a time duration. The schedule limit indicates that a job should not be run if the duration has elapsed after the scheduled start time and the job has not yet been started. If the job was a repeating job, it is rescheduled.

Similar to programs, schedules are database entities and can be saved in the database. Users with the right privileges can share saved schedules. For example, the end of quarter may be a common schedule for many jobs. Instead of each user having to redefine the schedule each time, they can all point to a saved 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 (the action) needs to be executed and when (the schedule). Users with the right privileges can create jobs either by simply specifying the action and schedule at the time of job creation, or by using existing programs and schedules. To customize an existing program, users can specify values for the program arguments during job creation, which will override the defaults that were specified when the program was created.

A common example of a job is backing up data on a personal computer. This is a task that most users perform on a nightly basis. It would therefore be best to create a program for this task that can be shared among different users. The program action would be the backup script and the program would have one argument, the path where the files that need to be backed up reside. Each user would create a job that points to this program and specify the path that is unique to them during the job creation, thus customizing the program for their own needs.

See "Creating Jobs" for more information.

How Programs, Jobs, and Schedules are Related

To determine when, where, and what will be executed in the database, you need to assign relationships among programs, jobs, and schedules. You can, however, leave a program, job, or schedule unassigned if you wish. You could, for example, create a job that calculates a year end inventory and leave it unassigned. That could be J4 in Figure 26-1. Similarly, schedules and programs could exist for year end tasks and be left unassigned. Figure 26-1 illustrates the relationship among programs, jobs, and schedules.

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

Description of the illustration admin060.gif

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 takes in a variable for the table name. Two jobs, J1 and J2, both point to the same program, which specifies a different table name. Finally, schedule S1 could have a value of 2AM for when the jobs should be performed. The end result is that the two tables named in J1 and J2 would be analyzed at 2AM.

Note that P2, P9, J4, and S2 are entities that have nothing pointed to them in Figure 26-1, so, for example, J4 could be self-contained with all relevant information inside the job itself. Also note that more than one job can map to a program or schedule.

See "Examples of Using the Scheduler" for more examples.

Job Classes

A job class is a way of grouping jobs into larger entities, thus enabling you to prioritize access to the job slaves among those job classes. This means that you can, for example, make sure a CEO's request will begin before a routine job. In addition to using job classes for resource allocation, you can also use them for setting job characteristics or behavior that must be the same for all the jobs in the 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.

Within a job class, you can:

• Specify attributes at the class level. For example, you can specify the same policy for purging log entries of all payroll jobs.

• Specify the order in which a job is started. You can assign priority values of 1-5 to individual jobs so that jobs with a higher relative priority start before jobs with a lower priority. If two jobs have the same assigned value, the first job enqueued takes precedence.

• Ensure that a job is started only if there are no runnable jobs with a higher priority in that class. In the case of resource contention, this ensures that you do not have a less important job preventing the timely completion of a more important one.

Some of the restrictions to keep in mind are:

• A job must be part of exactly one class. When a job is created, you must specify which class the job is part of. If you do not specify which class the job is part of, it automatically becomes part of the default 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 will automatically be disabled. In this case, the class for these jobs will be reset to the default system class. Jobs belonging to the dropped class that are already running will continue to run under class settings determined at the start of the job.

  
  

  Note: Job priorities are only used 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 is purely done on a class resource allocation basis.

  
  

Windows

A window enables you to change resource allocation during a time period such as time of day or time of the sales year. You do this so you can control which groups of users have what level of priority. For example, you could control access to a printer so product managers have a lower priority than executives. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am".

You can assign a priority with each window. If windows overlap, the window with the highest priority is chosen over other windows with lower priorities. See "Scheduler Architecture" for details.

Figure 26-2 illustrates a simple scenario where separate windows have been assigned their own priorities. In Figure 26-2, Class A represents users with more need for resources in the morning while Class B represents users with more need for resources in the afternoon.

Figure 26-2 Windows

Description of the illustration admin061.gif

See "Creating Windows" for examples of creating windows.

Overlapping Windows

You use windows to guarantee that resources will be available when you need them. Therefore, you should try to avoid having more than one window active at the same time to minimize the potential for resources going to a less important task. It is not an error, however, for windows to overlap.

Figure 26-3 illustrates a simple case of windows overlapping.

Figure 26-3 Overlapping Windows

Description of the illustration admin065.gif

There are three windows in Figure 26-3, so some rules need to be assigned for choosing which window will take precedence. The order of precedence for windows is as follows:

1. A high priority is chosen over a low priority. Oracle recommends that you choose low as the normal setting so you can guarantee resources for important high priority jobs.

2. A window gets to finish before the Scheduler switches to another window.

3. When a window finishes, the window with the highest percentage of its duration remaining is chosen.

If at the end of a window there is still overlap with one or more windows, the Scheduler tries to switch to the window that has the highest priority and then, if the windows have the same priorities, to the one with the highest percentage of its duration remaining.

Overlapping windows are handled differently based on whether they have the same priority or not. When two or more windows of the same priority overlap, Oracle continues to run the window that was already running. After this window finishes running, Oracle switches to the one that still has the highest percentage of its duration left.

When high and low priority windows overlap, the Scheduler switches to the high priority window if it is currently running a low priority window. Jobs currently running that had a schedule naming the low priority window may be stopped depending on the behavior you assigned when you created the job. In addition, whenever two windows overlap, an entry is written in the Scheduler log.

Window Groups

You can group jobs for ease of use. An example would be to combine weekends, times from 12AM to 7AM, and holidays into something called "downtime". This downtime window group offers you more control over when jobs are run. In this case, perhaps the data warehousing department would only want their queries run during this time when they might be assigned a high percentage of available resources.

A window group is only to combine windows and is only for jobs that use a window as a schedule.

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, a type of job called transient is available and is the default. Jobs created as transient are deleted by a background process after the jobs are finished. You can, of course, choose to use the persistent type of job, which allows for jobs to be kept for additional logging purposes.

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

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:

• update the entry in the job table to the COMPLETED status

• insert an entry into the job log table

• update the run 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 RAC Environments

All Scheduler functionality performs the same when using RAC as in a single-instance environment. Just as in other environments, the Scheduler in a RAC environment 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. Figure 26-5 illustrates a typical RAC architecture, with each instance's job coordinator exchanging information with the others. Note that there is only one job table for the database.

Figure 26-5 RAC Architecture and the Scheduler

Description of the illustration admin067.gif

Service Affinity when Using the Scheduler

The Scheduler enables you to specify the service on 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-6 illustrates a typical example of how services and instances could be used.

Figure 26-6 Service Affinity and the Scheduler

Description of the illustration admin068.gif

In Figure 26-6, you could change the properties of the services and the Scheduler will automatically recognize the change.

Each job class maps to a service. If a service is not specified, the job class will belong to a default service.