Overview of the Scheduler - Oracle 10g

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

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

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

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

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