14
Using the Scheduler for Task Automation
CERTIFICATION OBJECTIVES
14.01 Create a job, program, and schedule
14.02 Use a time-based or event-based schedule for executing Scheduler jobs
14.04 Use job chains to perform a series of related tasks
14.05 Create Windows and Job Classes
14.06 Use advanced Scheduler concepts to prioritize jobs
On many occasions, you, as a database administrator, or your users will need to automate scheduling and running jobs of many kinds—for example, performing maintenance work such as database backups, data loading and validation routines, generating reports, collecting optimizer statistics, or executing business processes. The Scheduler facility can be used to schedule tasks to be run at some point in the future.
The Scheduler can be coupled with the Resource Manager. It can activate Resource Manager plans and run jobs with priorities assigned to various Resource Manager consumer groups.
In earlier releases of the database, job scheduling capabilities were provided through the DBMS_JOB facility. This facility is still supported for backward compatibility, but it is not nearly as versatile as the Scheduler.
In this chapter, you’ll learn how to leverage some of the advanced Scheduler capabilities, such as job chains and job prioritization. Job chains are more than just one job running after another. In other words, the success of job A in the chain can trigger job B, or the failure of job A can trigger job C instead. Job prioritization uses job classes and the Resource Manager to provide more resources for one job over another. For example, one class of database users can be allocated a higher percentage of resources, such as CPU time, when more than one class of users is competing for the same resources.
CERTIFICATION OBJECTIVE 14.01
Create a Job, Program, and Schedule
Oracle provides a simple to use, yet advanced scheduling capability to permit application users, developers, and DBAs to run periodic tasks at a specific time. As a DBA, you may want to schedule a nightly backup without using less granular operating system scheduling capabilities. Developers may schedule summary reports to be run on the last day of the month, in the early morning hours, after receiving the last sales receipts for the day, and so on. Oracle Scheduler provides these capabilities and more.
To use and maintain Oracle Scheduler effectively, you need to understand its architecture, including its components—jobs, programs, schedules, job classes, and windows.
Understanding the Scheduler Architecture
The data dictionary includes a table that is a storage point for all Scheduler jobs. You can query the table through the DBA_SCHEDULER_JOBS view. The job coordinator process, CJQ0, monitors this table, and when necessary launches job slaves, the Jnnn processes, to run the jobs. The CJQ0 process is launched automatically when a job is due; it is deactivated after a sustained period of Scheduler inactivity. The Jnnn processes are launched on demand, though the maximum number is limited by the JOB_QUEUE_PROCESSES instance parameter; this defaults to 0, but if that value is used, the Scheduler will not function.
The job coordinator picks up jobs from the job queue table and passes them to slaves for execution. It also launches and terminates the slaves according to demand. To see the processes currently running, query the V$PROCESS view. In a Unix/Linux instance, the processes will be separate operating system processes, and in a Windows instance they are threads in the ORACLE.EXE image.
An advanced feature of the Scheduler is to associate it with the Resource Manager. Certain jobs might need to be run with certain priorities, and this can be achieved by linking a job to a Resource Manager consumer group (see Chapter 12 for more information on resource management). It is also possible to use the Scheduler to activate a Resource Manager plan, rather than having to change the RESOURCE_MANAGER_PLAN instance parameter manually. The Scheduler can be configured and monitored with an API—the DBMS_SCHEDULER PACKAGE—or through Database Control. Figure 14-1 shows the Scheduler home page, with links at the bottom to other scheduler objects such as jobs, programs, and windows.
FIGURE 14-1 Oracle Scheduler page
|
The Jobs
A job specifies what to do and when to do it. The what can be a single SQL statement, a PL/SQL block, a PL/SQL stored procedure, a Java stored procedure, an external procedure, or any executable file stored in the server’s file system—either a binary executable or a shell script. The when specifies the timestamp at which to launch the job and a repeat interval for future runs.
You can choose from among several options when creating a job, as you can see from looking at the DBMS_SCHEDULE.CREATE_JOB procedure. This procedure is overloaded because it has no less than six forms. Here is a part of the output for the first form of CREATE_JOB from a DESCRIBE of the DBMS_SCHEDULER package:
All forms of the CREATE_JOB procedure must specify a JOB_NAME. This must be unique within the schema in which the job is created.
Next, taking the first form of the procedure, the JOB_TYPE must be one of the following: PLSQL_BLOCK, STORED_PROCEDURE, or EXECUTABLE. If JOB_TYPE is PLSQL_BLOCK, then JOB_ACTION can be either a single SQL statement or a PL/SQL block. If the JOB_TYPE is STORED_PROCEDURE, then JOB_ACTION must name a stored procedure, which can be PL/SQL, Java, or an external procedure written in C. If the JOB_TYPE is EXECUTABLE, then the JOB_ACTION can be anything that could be run from an operating system command-line prompt: a command, an executable binary file, or a shell script or batch file. The NUMBER_OF_ARGUMENTS parameter states how many arguments the JOB_ACTION should take.
The first form of the procedure continues with details of when and how frequently to run the job. The first execution will be on the START_DATE; the INTERVAL defines a repeat frequency, such as daily, until END_DATE. JOB_CLASS has to do with priorities and integration of the Scheduler with the Resource Manager. The ENABLED argument determines whether the job can actually be run. Perhaps surprisingly, this defaults to FALSE. If a job is not created with this argument set to TRUE, it cannot be run (either manually or through a schedule) without enabling it first. Finally, AUTO_DROP controls whether to drop the job definition after the END_DATE. This defaults to TRUE. If a job is created with no scheduling information, it will be run as soon as it is enabled, and then dropped immediately if AUTO_DROP is on TRUE, which is the default.
The third form of the CREATE_JOB procedure has the job details (the JOB_TYPE, JOB_ACTION, and NUMBER_OF_ARGUMENTS) replaced with a PROGRAM_NAME that points to a program, which will provide these details. The fourth form has the scheduling details (START_DATE, REPEAT_INTERVAL, and END_DATE) replaced with a SCHEDULE_NAME that points to a schedule, which will manage the timing of the runs. The second, and briefest, form of the procedure uses both a program and a schedule.
Programs
Programs provide a layer of abstraction between the job and the action it will perform. They are created with the DBMS_SCHEDULER.CREATE_PROGRAM procedure:
By pulling the what of a job out of the job definition itself and defining it in a program, it becomes possible to reference the same program in different jobs and thus to associate it with different schedules and job classes, without having to define it many times. Note that (as for a job) a program must be ENABLED before it can be used.
Schedules
A schedule is a specification for when and how frequently a job should run. It is created with the DBMS_SCHEDULER.CREATE_SCHEDULE procedure:
The START_DATE defaults to the current date and time. This is the time that any jobs associated with this schedule will run. The REPEAT_INTERVAL specifies how frequently the job should run, until the END_DATE. Schedules without an END_DATE will run forever.
The REPEAT_INTERVAL argument can take a wide variety of calendaring expressions. These consist of up to three elements: a frequency, an interval, and possibly several specifiers. The frequency may be one of these values:
The specifiers can be one of these values:
Using these elements of a REPEAT_INTERVAL makes it possible to set up schedules that should satisfy any requirement. For example,
will run the job every 12 hours, starting at the START_DATE. The next example,
will run the job on the second day of each of the named four months, starting as early in the day as resources permit. A final example,
will run the job at 6:10 A.M. on alternate Mondays.
One schedule can be applied to many jobs; one program can be invoked by many jobs.
Job Classes
A job class is used to associate one or more jobs with a Resource Manager consumer group, and also to control logging levels. Classes are created with the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure:
The JOB_CLASS_NAME is the name to be referenced by the JOB_CLASS argument of the CREATE_JOB procedure. The RESOURCE_CONSUMER_GROUP nominates the group whose resource allocations should be applied to the running job, as determined by the Resource Manager plan in effect whenever the job happens to run. The SERVICE has significance only in a Real Application Cluster (RAC) database: you can restrict the job to run only on an instance with a particular service name. The details of logging can also be specified per class.
Windows
A schedule specifies exactly when a job should be launched. Windows extend the concept of schedules by giving Oracle more freedom to decide when to run the job. A window opens at a certain time and closes after a certain duration: jobs specified to run in a window may be launched, at Oracle’s discretion, at any time during the window. The window itself can open repeatedly according to a schedule. Use of windows is of particular value when combined with classes and the Resource Manager: Oracle can schedule jobs to run within a window according to their relative priorities. Windows also activate Resource Manager plans.
Windows are created with the DBMS_SCHEDULER.CREATE_WINDOW procedure:
The RESOURCE_PLAN nominates the Resource Manager plan that will be activated when the window opens. The window will open on the START_DATE and reopen according to the REPEAT_INTERVAL until the END_DATE. The procedure is overloaded; a second form lets you nominate a precreated schedule rather than specifying the schedule here with these three arguments.
The DURATION is an INTERVAL DAY TO SECOND datatype. This will allow a time span to be specified in days, hours, minutes, and seconds. The basic syntax for an INTERVAL DAY TO SECOND column is
Note that that a space appears between the days and the hours, and colons between the hours, minutes, and seconds. So this specifies a time gap of one day, two hours, three minutes, and four seconds.
The PRIORITY argument is intended to manage circumstances where windows overlap and has two possible values: LOW (the default) or HIGH. Only one window can be in effect at a time, and it will be the window with the higher priority. If two or more overlapping windows have the same priority, the window that opened first will take priority.
Windows share the same namespace as schedules. It is therefore impossible to create a window with the same name as a schedule, but this does mean that wherever you can refer to a schedule, you can also refer to a window. Note that a job can be created to run at any time within a named window, rather than at the precise times specified by a schedule. The window itself will open and close according to a schedule—either a schedule defined within the window or a precreated schedule object.
Privileges
All Scheduler privileges are granted and revoked with the usual GRANT and REVOKE syntax. A number of Scheduler-related privileges can be used:
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
EXECUTE ON >JOB, PROGRAM, OR CLASS<
ALTER ON >JOB, PROGRAM, OR SCHEDULE<
ALL ON >JOB, PROGRAM, SCHEDULE, OR CLASS<
Before a user can create any jobs, schedules, or programs, he or she must be granted the CREATE JOB privilege; this includes the ability to create and use his or her own programs and schedules. To create jobs in other schemes, the user will need the CREATE ANY JOB privilege. To use Scheduler objects in other schemas, you need the EXECUTE privilege on them. The MANAGE SCHEDULER privilege is needed to create job classes and windows and to force windows to open or close irrespective of their schedules.
The ready-made role SCHEDULER_ADMIN includes the first five privileges just listed. It is granted to SYSTEM with ADMIN by default.
CERTIFICATION OBJECTIVE 14.02
Use a Time-based or Event-based Schedule for Executing Scheduler Jobs
You can base the execution of a Scheduler job either on a specific time or an event. For example, you may want to run a backup job at 1:00 A.M. every morning. Alternatively, other jobs may be dependent on a specific event, such as receiving an accounts-receivable file from the accounting department, or the order entry department completing their manual order corrections for the day.
Creating and Scheduling Time-based Jobs
To create and schedule a time-based job with one procedure call, use the CREATE_JOB procedure. Here’s an example:
This will create a job that will call a Unix shell script at 11:00 every weekday evening, starting today. The job is created in the SYSTEM schema. The operating system permissions on the script will have to be set such that the Oracle owner can run it. When you specify the START_DATE parameter, the scheduler initiates the job as close to this date and time as possible.
EXERCISE 14-1
Creating a Job with the Scheduler API
Use the DBMS_SCHEDULER package to create a job, and confirm that it is working.
1. Connect to your database as a user with the CREATE JOB privilege.
2. Create a table to store times, and set your date format to show the date and time:
3. Create a job to insert the current time into the table every minute:
4. Query the job table to see that the job is scheduled:
5. Query the times table to demonstrate that the inserts are occurring:
6. Disable the job:
7. Re-run the queries from steps 4 and 5 to confirm that the job is disabled and that no more inserts are occurring.
8. Drop the job:
Creating and Scheduling Event-based Jobs
Event-based jobs still use the START_DATE parameter; however, the job’s initiation also depends on an event specified by the EVENT_CONDITION and QUEUE_SPEC parameters, as in this example:
The EVENT_CONDITION parameter uses syntax from Advanced Queueing (AQ) that checks periodically for an event name AR_FILE_ARRIVAL. The QUEUE_SPEC parameter contains the name of the queue whose events will trigger the event-based job.
Using Programs and Schedules
Programs and schedules let you reuse Scheduler components for similar tasks. Rather than defining each job as a self-contained entity, you create programs and schedules, each of which can be used by many jobs.
The job created in Exercise 14-1 could be split up into a job, a program, and a schedule. To do this through Database Control, from the database home page select the Server tab. Then in the Oracle Scheduler section click the Programs link, click the Create button, and enter the code you want executed, as shown in Figure 14-2. This can be as long and complicated as you want (bearing in mind that the datatype for PROGRAM_ACTION is VARCHAR2 and so is limited to 4KB).
Keep your JOB_ACTIONs and PROGRAM_ACTIONs as short as possible, preferably using just one statement. Do all the work in a procedure invoked by that statement. This will be far easier to maintain than having a large amount of SQL or PL/SQL in your job and program definitions.
FIGURE 14-2 Creating a Scheduler program using Enterprise Manager
As with most Enterprise Manager (EM) tasks, you can click the Show SQL button to see what code EM will run to perform the specified action:
If you create a program with the CREATE_PROGRAM procedure, then (just as with jobs) the program will be disabled by default. Change this default either by specifying the ENABLED argument as TRUE when you create the program, or by using the ENABLE procedure subsequently:
To create a schedule, click the Schedules link from the Oracle Scheduler section, and click Create to view the page shown in Figure 14-3. This GUI does not give access to some of the more complicated interval possibilities, such as every third Tuesday, which would be but it gives access to all that will usually be required.
FIGURE 14-3 Creating a schedule with EM
To create a job, click the Jobs link from the Oracle Scheduler section. The initial window (shown in Figure 14-4) assumes that the job is a PL/SQL block. Clicking the Change Command Type button will let you select your program type. The Schedule tab lets you tie the job to a precreated schedule, rather than defining the schedule within the job.
FIGURE 14-4 Creating a job with EM
Programs share the same namespace as jobs; you cannot have a program with the same name as a job. The same is true for schedules and windows.
It is also possible to run a job independently of a schedule, by using the RUN_JOB procedure:
CERTIFICATION OBJECTIVE 14.03
Create Lightweight Jobs
A lightweight job shares many characteristics with a standard job discussed earlier, except that a lightweight job is ideal for running many short-duration jobs that run frequently. A few restrictions to lightweight jobs are discussed in the following sections; these restrictions are offset by the performance improvement due to the low overhead of a lightweight job.
Understanding Lightweight Jobs
If you need to submit hundreds or even tens of jobs every second, a lightweight job is the best way to reduce overhead. For a regular job, Oracle creates a database object containing the metadata for the job, modifies several database tables, and generates redo. In contrast, a lightweight job (also known as a persistent lightweight job) has very small disk space and run-time data requirements. A lightweight job is created using a predefined job template, which can be a program or a stored procedure. Other characteristics of lightweight jobs that distinguish them from regular jobs are as follows:
Lightweight jobs are not schema objects as regular jobs are.
Create and drop times for lightweight jobs are significantly less than those of regular jobs since new schema objects are not required.
Lightweight jobs have a small disk footprint for job metadata and runtime data.
Session creation time is significantly lower than that for regular jobs.
Lightweight jobs can be load balanced in a RAC environment because of the small footprint on disk.
You can create a job template to use with lightweight jobs by using DBMS_SCHEDULER.CREATE_PROGRAM, as described earlier in this chapter. The job template used for lightweight jobs is stored as a program whose type must be PLSQL_BLOCK or STORED_PROCEDURE. When you create a lightweight job, you specify a JOB_STYLE of LIGHTWEIGHT. By default, the JOB_STYLE is REGULAR.
Lightweight jobs have a few drawbacks, as you might expect. You cannot set privileges on lightweight jobs—they inherit privileges from the template’s program. In addition, because the lightweight job uses a template, you cannot create a fully self-contained lightweight job. Finally, you must use a PL/SQL command to create a lightweight job—this is not available through the EM interface.
Using Lightweight Jobs
To create a lightweight job, you create the template once using CREATE_PROGRAM, and then you can run the lightweight job as often as necessary using CREATE_JOB.
EXERCISE 14-2
Create and Run a Lightweight Job
In this exercise, you’ll create a small table that you will populate with a lightweight job. Create a template for the lightweight job and then run it once.
1. Create a table to hold the current status of the DUAL table; if the DUAL table has one row, the status is 1; otherwise it is 2:
2. Create a program using a PL/SQL program that will be the template for the lightweight job:
3. Check the status of the job in DBA_SCHEDULER_PROGRAMS:
4. Use the DUAL_CHECK program name as a template to create and run a lightweight job once:
5. Check the contents of the table CHECK_DUAL_STATUS:
CERTIFICATION OBJECTIVE 14.04
Use Job Chains to Perform a Series of Related Tasks
In our increasingly complex environments, scheduler jobs may have one or more dependencies. In other words, you may want to run job A, and if job A succeeds, you can run job B. If job A does not succeed, you may want to run job C instead. As a result, regardless of whether job B or job C ran successfully, you may run job D. This is an example of a job chain. A job chain is a database object that contains a named series of programs linked together for a combined objective, such as processing the daily Internet and phone orders, sending order confirmation e-mails to the Internet customers, and then sending shipping requests to the warehouse.
Understanding Job Chains
Each part of a chain of programs is called a step. Though not required, after one or more initial steps, successive steps are conditional upon the success or failure of the initial steps. Therefore, creating chains without conditions somewhat defeats the purpose of using a chain. You use DBMS_SCHEDULER to create all chain objects and initiate the chain of events. Here are the steps you use for creating and executing a chain:
1. Create the chain using the CREATE_CHAIN procedure.
2. Create one or more chain steps using DEFINE_CHAIN_STEP or DEFINE_CHAIN_EVENT_STEP.
3. Define chain rules using DEFINE_CHAIN_RULE.
4. Enable the chain using the ENABLE procedure.
5. Create a job using CREATE_JOB with a JOB_TYPE of CHAIN.
Creating Chain Objects
You create a chain object with CREATE_CHAIN. To create a chain without any steps or rules, you need to specify only the chain name. Here is an example of creating an empty chain with a description:
You can also use EM to create a chain. Figure 14-5 shows how to create a chain called PROC_DAILY_ORDERS. You can add the chain steps and rules when you create the chain, or you can modify the chain later to add or remove steps or rules.
FIGURE 14-5 Creating a Scheduler chain
Defining Chain Steps
You create a chain step with DEFINE_CHAIN_STEP. Each chain step has a name within the chain and references one of the following:
A program
Another chain (this is known as nesting a chain)
An event
If one of your steps waits for an event, you use a DEFINE_CHAIN_EVENT_STEP that references an event that must be triggered before the chain step will be executed. For example, you may want to delay a step in a chain until you receive an inventory file from the mainframe system.
In this example, you add two chain steps to the PROC_DAILY_ORDERS chain to check the status of the DUAL table before proceeding to the order processing steps:
Defining Chain Rules
Chain rules, created with DEFINE_CHAIN_RULE, define dependencies between steps and when steps run. Each chain rule has a condition and an action. The condition uses syntax very similar to a WHERE clause in a SELECT statement. Typically, you specify a condition based on the success or failure of one or more previous steps in the chain. After evaluating the condition, the chain rule will then perform one of the following actions:
Run another step.
Stop a step.
End execution of the chain.
Continuing the example from the preceding section, this rule will run the step CREATE_SUCCESS_REPORT only if the step CHECK_DUAL_TABLE completes successfully:
Starting the Chain
Two steps are used to start a chain: first, you must enable it with the ENABLE procedure. When you create a chain, it is always created in a disabled state. Second, you create a job with a JOB_TYPE of CHAIN. The job schedule can be either event-based or time-based.
In the following example, the chain created previously is enabled, and then a job is created to run daily at 11 P.M.:
To run a job chain immediately, you can use either RUN_JOB or RUN_CHAIN. If you use RUN_CHAIN, you can start the chain at any step in the chain. For example, you may want to start the chain PROC_DAILY_ORDERS at the INV_LOAD steps right away:
Monitoring Job Chains
You can use a number of data dictionary views to query the structure of a chain and monitor the progress of a running chain. As with most data dictionary views, DBA, ALL, and USERS versions can be used:
*_SCHEDULER_CHAINS
*_SCHEDULER_CHAIN_RULES
*_SCHEDULER_CHAIN_STEPS
*_SCHEDULER_RUNNING_CHAINS
Here is an example of a query on the DBA_SCHEDULER_CHAINS data dictionary view:
CERTIFICATION OBJECTIVE 14.05
Create Windows and Job Classes
Using Scheduler windows and job classes, you can adjust how your jobs run. You might, for example, want a job to run only during a certain window, and if it does not complete, it should pick up where it left off when the window is open again. You can use job classes to assign one or more jobs to a resource consumer group and therefore load balance your limited resources when jobs must compete with online users and other jobs. In the following sections, you’ll learn how to create job classes and windows with EM and DBMS_SCHEDULER, and then you’ll explore some of the preconfigured jobs.
Creating Windows
To create a new window, click the Server tab from the EM home page. Under the Oracle Scheduler heading, click the Windows link. You will see the existing set of windows shown in Figure 14-6. Note that the windows WEEKNIGHT_WINDOW and WEEKEND_WINDOW are retained from previous releases of Oracle Database for backward compatibility.
FIGURE 14-6 Viewing Scheduler windows with EM
When you click the Create button, you see the window shown in Figure 14-7, where you can set the resource plan and priority associated with the window.
FIGURE 14-7 Creating a Scheduler window
The equivalent PL/SQL (Procedural Language/Structured Query Language) used to create the window looks like this:
Creating Job Classes
You can view and create job classes in EM using steps similar to creating a window. To create a new job class, click the Server tab from the EM home page. Under the Oracle Scheduler heading, click the Job Classes link. You will see the page shown in Figure 14-8. Note that all jobs that do not specify a job class are assigned the job class DEFAULT_JOB_CLASS by the Scheduler.
FIGURE 14-8 Viewing job classes using EM
To create a new job class, click the Create button. You will see the page shown in Figure 14-9.
FIGURE 14-9 Creating a new job class
Note that you can assign the resource consumer group to this class. A many-to-one relationship exists between classes and resource groups, where many classes can use the same resource consumer group. Here is the SQL that creates the new job class:
Preconfigured Jobs
Several jobs are configured by default, such as MGMT_STATS_CONFIG_JOB and MGMT_CONFIG_JOB. To see the details for these jobs, select the Server tab from the EM home page and click Jobs. You’ll see the page shown in Figure 14-10.
FIGURE 14-10 Viewing the preconfigured jobs
In Figure 14-10, you can see that the MGMT_STATS_CONFIG_JOB runs at a specific time every morning, and the MGMT_CONFIG_JOB runs in the MAINTENANCE_WINDOW_GROUP.
What happens if a window closes before a job that is run in the window has completed? The default behavior is that the job will continue until it finishes, but this can be overridden by setting an attribute. This query shows all the Scheduler jobs in the database, including the attribute STOP_ON_WINDOW_CLOSE:
To change this attribute, use the SET_ATTRIBUTE procedure:
This will cause the LIGHTWEIGHT_JOB_1 job to abort if it has not finished by the time its window closes.
CERTIFICATION OBJECTIVE 14.06
Use Advanced Scheduler Concepts to Prioritize Jobs
Most likely, you will have many different jobs running within any given window. Within each window, you may have several different classes of jobs running, and each class may have its own priority. As discussed earlier in the chapter, job classes categorize jobs. When you assign a job class to a resource consumer group, the resource plan currently in effect divides the available resources to each job class.
Jobs are prioritized at two levels: the class level and the job level. For jobs at different class levels, resource plans dictate the job priority. Within a class, however, you can assign a job priority. The job with the higher job priority starts first. You saw how to create job classes and windows earlier in the chapter.
Using Classes, Windows, and the Resource Manager
The more advanced capabilities of the Scheduler let you integrate it with the Resource Manager to control and prioritize jobs. These are the relevant components:
Job classes Jobs can be assigned a class and a class can be linked to a Resource Manager consumer group. Classes also control the logging level for their jobs.
Consumer groups Resource Manager consumer groups are restricted in the resources they can use, such as CPU usage or the number of active sessions.
Resource plans A Resource Manager plan defines how to apportion resources to groups. Only one plan is active in the instance at any one time.
Windows A window is a defined (probably recurring) period of time during which certain jobs will run and a certain plan will be active.
Window groups Windows can be combined into window groups for ease of administration.
Prioritizing jobs within a window is accomplished at two different levels. On the first level, within a class, jobs can be given different priorities by the Scheduler. However, the Resource Manager will not distinguish between them because all jobs in a class are in the same consumer group. On the second level, if jobs in different classes are scheduled within the same window, the Resource Manager will assign resources to each class according to the consumer groups for that class.
Using Job Classes
You can create a class with database control, or through the API. Here’s an example:
Then you can assign the jobs to the class, either at job creation time by specifying the JOB_CLASS attribute, or by modifying the job later. To assign a job to a class with the API, you must use the SET_ATTRIBUTE procedure. To put the job REPORTS_JOB into the class just created, use:
If several jobs are in the one class, you can prioritize them with more SET_ATTRIBUTE calls:
If several jobs in the same class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator process. The job priority can be a value from 1 through 5, with 1 being the first to be picked up for job execution (the highest priority). The default for all jobs is 3. This could be critical if, for instance, the class’s consumer group has an active session pool that is smaller than the number of jobs. So those jobs with the highest priority will run first and the other jobs are queued.
|
Logging levels are also controlled by the job’s class using three options:
DBMS_SCHEDULER.LOGGING_OFF No logging is done for any jobs in this class.
DBMS_SCHEDULER.LOGGING_RUNS Information is written to the job log regarding each run of each job in the class, including when the run was started and whether the job ran successfully.
DBMS_SCHEDULER.LOGGING_FULL In addition to logging information about the job runs, the log will also record management operations on the class, such as creating new jobs.
To view logging information, query the DBA_SCHEDULER_JOB_LOG view:
More detailed information is written to the DBA_SCHEDULER_JOB_RUN_DETAILS view, including the job’s run duration and any error code it returned.
Logging information is cleared by the automatically created PURGE_LOG job. By default, this runs daily and removes all logging information more than 30 days old.
Using Windows
You can create windows either through Database Control or with the CREATE_WINDOW procedure. Here’s an example:
This window activates a Resource Manager plan called NIGHT_PLAN. This might be a plan that gives priority to the Decision Support Systems (DSS) consumer groups over the online transaction processing (OLTP) group, for example. It opens according to the schedule WEEKDAY_NIGHTS, which might be Monday through Friday at 20:00 (8 P.M.). The window will remain open for eight hours; the DURATION argument accepts an INTERVAL DAY TO SECOND value, as does the REPEAT_INTERVAL for a schedule. Setting the priority to LOW means that if this window overlaps with another window, the other window will be allowed to impose its Resource Manager plan. This would be the case if you created a different window for your end-of-month processing and the end-of-month happened to be on a week day, for example. You could give the end-of-month window HIGH priority to ensure that the end-of-month Resource Manager plan, which could give top priority to the BATCH group, processes key management reports first.
|
CERTIFICATION SUMMARY
This chapter started with a brief overview of the Oracle Scheduler architecture along with the data dictionary views and processes used to manage Scheduler objects. It covered the basics of the Scheduler components: jobs, programs, schedules, job classes, and windows.
Once the basics were out of the way, you learned about a couple of different ways to schedule a job, which are time-based or event-based. In other words, you can schedule a job for a specific time, or you can schedule a job dependent on an internal or external event. A specific type of job, called a lightweight job, makes it easy to create a large number of jobs that run in a short amount of time using a minimal amount of overhead. Job chains make a DBA’s job easier by creating a series of jobs that can be initiated as a unit. The success or failure of each job within the job chain can conditionally execute other jobs within the chain.
The last part of the chapter delved into some of the more advanced features of the Oracle Scheduler—windows and job classes. Job windows extend the concept of schedules by giving Oracle more flexibility when running a job. A running job may be restricted to running in a single occurrence of a window, or it may suspend at the end of the window and pick up where it left off the next time the window is open. Job classes further refine job priorities and resource usage by associating one or more jobs with a Resource Manager consumer group.
TWO-MINUTE DRILL
Create a Job, Program, and Schedule
The data dictionary table DBA_SCHEDULER_JOBS stores information about all jobs associated with a schedule.
The CJQ0 job coordinator process monitors the DBA_SCHEDULER_JOBS table, and when necessary, launches job slaves (Jnnn processes) to run Scheduler jobs.
A job specifies what to do and when to do it. The what can be a single SQL statement, a PL/SQL block, a PL/SQL stored procedure, a Java stored procedure, an external procedure, or any executable file stored in the server’s file system.
Programs provide a layer of abstraction between the job and the action it will perform, and are created with the DBMS_SCHEDULER.CREATE_PROGRAM procedure.
A schedule is a specification for when and how frequently a job should run.
A job class is used to associate one or more jobs with a Resource Manager consumer group and also to control logging levels.
Windows extend the concept of schedules by giving Oracle more freedom on deciding when to run a job.
A window opens at a certain time and closes after a specified duration. So jobs specified to run in a window may be launched, at Oracle’s discretion, at any time during the window.
Use a Time-based or Event-based Schedule for Executing Scheduler Jobs
You can base the execution of a Scheduler job either on a specific time or an event.
To create and schedule a time-based job with one procedure call, use the CREATE_JOB procedure.
Event-based jobs still use the START_DATE parameter; however, the job’s initiation also depends on an event specified by the EVENT_CONDITION and QUEUE_SPEC parameters.
Programs and schedules let you reuse Scheduler components for similar tasks.
You can run a job independently of a schedule by using the RUN_JOB procedure.
Create Lightweight Jobs
A lightweight job has many of the same characteristics of a standard job, except that a lightweight job is ideal for running many short-duration jobs that run frequently.
If you need to submit hundreds or even tens of jobs every second, a lightweight job is the best way to reduce overhead.
Lightweight jobs have a small disk footprint for job metadata and runtime data.
You cannot set privileges on lightweight jobs because they inherit privileges from the template’s program.
Use Job Chains to Perform a Series of Related Tasks
A job chain is a database object that contains a named series of programs linked together for a combined objective.
Each part of a chain of programs is called a step.
You create a chain using the CREATE_CHAIN procedure.
You create a chain step with DEFINE_CHAIN_STEP procedure.
Chain rules, created with DEFINE_CHAIN_RULE, define dependencies between steps and when steps run.
You must enable a job chain with the ENABLE procedure.
To run a job chain, create a job with a JOB_TYPE of CHAIN.
Create Windows and Job Classes
You create a new window with the CREATE_WINDOW procedure.
When you create a new window, you can optionally assign a resource plan and priority.
You create a new job class using the CREATE_JOB_CLASS procedure.
The job class DEFAULT_JOB_CLASS is assigned to a job when a job does not have an explicit class assignment.
If a window closes before a running job has completed, the job will run to completion unless you set the attribute STOP_ON_WINDOW_CLOSE to TRUE.
Use Advanced Scheduler Concepts to Prioritize Jobs
Prioritizing jobs within a window occurs at two levels: within a class by a priority level or between classes by resource consumer group.
Logging levels are controlled by the job’s class.
You create a job class with the CREATE_JOB_CLASS procedure.
If several jobs in the same class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator process.
You create a window with the CREATE_WINDOW procedure.
You assign either LOW or HIGH priority to a window to prioritize window attributes when windows overlap.
When windows overlap and they have the same priority, the window opened earlier stays open.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Create a Job, Program, and Schedule
1. When a job is due to run, what process will run it? (Choose the best answer.)
A. A CJQn process
B. A Jnnn process
C. A server process
D. A background process
2. Which of the following is a requirement if the Scheduler is to work? (Choose the best answer.)
A. The instance parameter JOB_QUEUE_PROCESSES must be set.
B. A Resource Manager plan must be enabled.
C. A schedule must have been created.
D. All of the above.
E. None of the above.
3. A Scheduler job can be which of the following types? (Choose all that apply.)
A. Anonymous PL/SQL block
B. Executable operating system file
C. External C procedure
D. Java stored procedure
E. Operating system command
F. Operating system shell script (Unix) or batch file (Windows)
G. PL/SQL stored procedure
4. You create a job with this syntax:
You find that it is not running when expected. What might be a reason for this? (Choose the best answer.)
A. The schedule is associated with a window, which has not opened.
B. The job has not been enabled.
C. The class is part of Resource Manager consumer group with low priority.
D. The permissions on the job are not correct.
5. What are the possible priority levels of a job within a class? (Choose the best answer.)
A. 1 to 5
B. 1 to 999
C. HIGH or LOW
D. Depends on the Resource Manager plan in effect
6. A preconfigured job called MGMT_STATS_CONFIG_JOB is set up to gather optimizer statistics. This job is scheduled to run in both the THURSDAY_WINDOW and the SUNDAY_WINDOW. What will happen if it fails to complete before the window closes? (Choose the best answer.)
A. It will continue to run to completion.
B. It will terminate and continue the next time either window opens.
C. It will terminate and restart the next time either window opens.
D. The behavior will vary depending on whether it was running in the THURSDAY_WINDOW window or the SUNDAY_WINDOW window.
Use a Time-based or Event-based Schedule for Executing Scheduler Jobs
7. You want a job to run every 30 minutes. Which of the following possibilities for the REPEAT_INTERVAL argument are correct syntactically and will achieve this result? (Choose two answers.)
A. 'freq=minutely;interval=30'
B. 'freq=hourly;interval=1/2'
C. '0 00:30:00'
D. 'freq=minutely;byminute=30'
E. 'freq=byminute;interval=30'
8. You create a job class, and you set the LOGGING_LEVEL argument to LOGGING_RUNS. What will be the result? (Choose the best answer.)
A. A log entry will be created for each run of each job in the class, but no information will be included on whether the job was successful.
B. A log entry will be created for each run of each job in the class, plus information on whether the job was successful.
C. A single log entry will be created for the class whenever it is run.
D. You cannot set logging per class, only per job.
Create Lightweight Jobs
9. Which of the following is a prerequisite for using lightweight jobs? (Choose the best answer.)
A. You must define a schedule specifically for lightweight jobs.
B. You must have a job template that is a PL/SQL block or a stored procedure.
C. You must specify a REPEAT_INTERVAL.
D. You must have a job template that is of type EXECUTABLE to improve performance.
Use Job Chains to Perform a Series of Related Tasks
10. You create a chain rule for an existing job chain. What types of actions can be performed by the rule after evaluating the condition? (Choose all that apply.)
A. Terminate the chain execution.
B. Run another program.
C. Stop a different step in the chain.
D. Restart the job from the beginning.
E. Run another step.
Create Windows and Job Classes
11. Which of the following statements are correct regarding how Scheduler components can be used together? (Choose all that apply.
A. A schedule can be used by many jobs.
B. A job can use many programs.
C. A class can have many programs.
D. Job priorities can be set within a class.
E. Consumer groups control priorities within a class.
F. A Resource Manager plan can be activated by a schedule.
12. Which view will tell you about jobs configured with the Scheduler? (Choose the best answer.)
A. DBA_JOBS
B. DBA_SCHEDULER
C. DBA_SCHEDULED_JOBS
D. DBA_SCHEDULER_JOBS
Use Advanced Scheduler Concepts to Prioritize Jobs
13. If two windows are overlapping and have equal priority, which window(s) will be open? (Choose the best answer.)
A. Both windows will be open.
B. Windows cannot overlap.
C. Whichever window opened first will remain open; the other will remain closed.
D. Whichever window opened first will be closed, and the other will open.
14. How long will Scheduler logging records be visible in the DBA_SCHEDULER_JOB_LOG view? (Choose the best answer.)
A. They will remain until the PURGE_LOG job is run.
B. By default, they will be kept for 30 days.
C. By default, they will be kept for 1 day.
D. By default, the view will be cleared every 30 days.
SELF TEST ANSWERS
Create a Job, Program, and Schedule
1. 
B. Jobs are run by a job slave process called Jnnn. The CJQ0 process is the job queue coordinator, which passes the jobs to the slave for execution.
A is wrong because the CJQ0 process coordinates the Jnnn processes but does not run the job itself. C and D are wrong because they are not specific enough.
2. A. The only requirement for the Scheduler to function is that at least one job slave process must be created with the JOB_QUEUE_PROCESSES parameter.
B, C, D, and E are wrong. Resource Manager plans and schedules are optional.
3. A, B, C, D, E, F, and G. All the answers are correct.
The Scheduler job can be any job that you can run manually.
4. B. As written, the procedure call will not enable the job, so it won’t run at all.
A is wrong since the job will run when the window is open. C is wrong because even with low priority, the job will still start. D is wrong because you will know right away if the permissions are not correct by the output from the CREATE_JOB procedure.
5. A. Within a class, jobs can have a priority of 1 to 5.
B is wrong because the range is 1 to 5. C is a wrong answer because HIGH and LOW apply to overlapping windows, not jobs within a class. D is wrong because the priority range is not dependent on the Resource Manager plan.
6. C. The MGMT_STATS_CONFIG_JOB is configured to stop when its window closes. At the next window, it will start again.
A is wrong because the job stops at the end of the window unconditionally. B is wrong because the job will restart not continue. D is wrong because the behavior is not defined within the window itself.
Use a Time-based or Event-based Schedule for Executing Scheduler Jobs
7. A and B. Either is the correct syntax to set a half-hour repeat interval.
C is wrong because it is the format for a DURATION. D and E are syntactically incorrect.
8. B. With logging set to LOGGING_RUNS, you will get records of each run of each job, including its success or failure. The other possible logging levels are NONE, in which case no logging at all will occur, or FULL, which records details for each run and also administrative actions, such as enabling or disabling jobs.
A is wrong because LOGGING_RUNS includes a success or failure status. C is a wrong answer because a log entry exists for each run in each job. D is wrong because LOGGING_RUNS is set per the job’s class, not per job.
Create Lightweight Jobs
9. B. Lightweight jobs must specify a template that has a program with a PL/SQL block or a stored procedure.
A is wrong because you do not need a schedule. C is a wrong answer because you do not have to repeat the lightweight job. D is wrong because the program template must be of type PL/SQL or a stored procedure; lightweight jobs run faster due to the low overhead of creating and dropping the job, not because of the type of job template.
Use Job Chains to Perform a Series of Related Tasks
10. A, C, and E. Within a chain rule, you can terminate the chain execution, stop another step in progress, or transfer control to another step.
B and D are incorrect; after the condition is evaluated, the chain rule cannot run another program directly or restart the job.
Create Windows and Job Classes
11. A and D. One job can use only one schedule, but one schedule can be used by many jobs. Job priorities can be set at both the class level and the job level.
B is wrong because a job can reference only one program. C is a wrong answer because a job class associates one or more jobs with a Resource Manager consumer group as well as controls logging levels. E is wrong because consumer groups cannot specify a priority level. F is wrong because Resource Manager plans can be activated only from windows or a job class.
12. D. The DBA_SCHEDULER_JOBS view externalizes the data dictionary jobs table, with one row per scheduled job.
A is wrong because DBA_JOBS has information about jobs but not about associated schedules, if any. B is wrong because no DBA_SCHEDULER view exists. C is a wrong answer because no view DBA_SCHEDULED_JOBS exists.
Use Advanced Scheduler Concepts to Prioritize Jobs
13. C. If two windows overlap and have equal priority, the window that opened earlier will be the open window.
A is wrong because only one window can be open at a time. B is a wrong answer because two overlapping windows can exist, but they cannot be open at the same time. D is wrong because a window with higher priority will close an already open window with a lower priority.
14. B. By default, the PURGE_LOG job runs daily and keeps log records for 30 days.
A is wrong because the PURGE_LOG job runs automatically on a daily basis. C is wrong because the default is 30 days, not 1 day. D is wrong because the last 30 days of log records are kept and only older records are cleared.