Todos los Sábados a las 8:00PM

Ahora a suscribirse y seguir el contenido de este blog.

viernes, 12 de enero de 2018

The latest enhancements made to Oracle Scheduler is the introduction of Scheduler Resources and Incompatibilities.

Oracle Premier Support - Oracle Database Support News - Issue December 2017, Volume 82 (Doc ID 230.1)
12c New Scheduler Features

DBMS_SCHEDULER helps you schedule tasks in an Oracle Database. The latest enhancements made to Oracle Scheduler is the introduction of Scheduler Resources and Incompatibilities.

Incompatibility, introduced in Oracle Database 12c Release 2, helps identify incompatible jobs or incompatible programs in the database. As the name suggests, two incompatible jobs cannot execute at the same time. Once has to wait for the completion of the other. Similarly, incompatible programs means jobs with incompatible programs cannot execute at the same time. This logic helps specify mutually exclusive jobs or programs that have to wait for the completion of one.

For example: The application schedules jobs whenever there is a request for bulk data extraction (data_extraction), insertion (data_insertion) or deletion (data_deletion). Considering that data extraction should not be scheduled when either insertion of deletion jobs are in progres two incompatibilities can be created to prevent this. One to mark data the extraction job and data deletion job as incompatible, and the second incompatibility to mark the data insertion job and data extraction job as incompatible.

In this case, if the data extraction job is scheduled to run while the data insertion is in progress, the former will wait until the completion of the latter.

You can use the CREATE_INCOMPATIBILITY, REMOVE_FROM_INCOMPATIBILITY and ADD_TO_INCOMPATIBILITY procedures available with the DBMS_SCHEDULER package to create an incompatibility, remove a job or program from the incompatibility and add a job or program to an incompatibility, respectively.

Another enhancement made to Oracle Scheduler in 12c Release 2 is the introduction of Scheduler Resource Queues. Here, you can define your own scheduler resource queues. While defining, you can mention the name and the number of units for your resource queue. It’s as simple as that. Please note, these units do not relate to any of the OS or database resources (like CPU, memory, I/O, processes etc). You can consider the number or units specified for scheduler resources as any random unit. The significance of this unit is that you can associate that with a scheduler job. For example, job1 requires 2 units of resource_queue 1 and 3 units of resource_queue 2 and so on.

You can decide the units required for your resource, based on the constraints you want to implement. Consider the above example of data extraction, insertion or deletion jobs. You can implement this constraint using the resource queues as well.

The following describes this scenario:
  • Create a resource queue, for example, resource1 with 4 units
  • Specify 2 units of resource1 for data_insertion and 2 units for data_deletion jobs (meaning they can execute simultaneously, if required)
  • Specify 3 units of resource1 for data_extraction (if the data_insertion job is executing, it utilizes 2 units of resource1)
  • At this point, the data_deletion job can execute (if required) as there are 2 more units of resource1 available
  • The data_extraction job will have to wait, as it requires 3 units of resource1, whereas the total available units for resource1 resource queue is just 4
You can create a scheduler resource queue using DBMS_SCHEDULER.CREATE_RESOURCE procedure. The resource constraint for the jobs can be set using DBMS_SCHEDULER.SET_RESOURCE_CONSTRAINT procedure.

Additional Details 

The details of the Job resource queue and Job Incompatibilities, along with the steps to create and modify them, are covered in the Advisor Webcast - Optimizing Scheduler Jobs Using 12.2 Features and Automated Troubleshooting. The Q&A during the webcast is available here and the downloadable pdf with the powerpoint slides is available here.

Optimismo para una vida Mejor

Optimismo para una vida Mejor
Noticias buenas que comentar