Achieving parallelism with Chains in Oracle

Our team’s latest sprint has not just brought me the month of December, it has presented an engaging topic. A brief overview of the challenge at hand, I had to load a huge amount of files in PL SQL in parallel considering certain prioritizations and set-ups. Let us see how it went.

Task Description

We have been working on a process, in which we; load a large quantity of files into an Oracle Database, process the files and perform certain operations on the data. For now, we will focus on the solution of loading the files in a PL SQL process considering the following aspects:

• The received files relate to different countries.
• The files have to be loaded in parallel and the number of individual threads defined in a set-up table. The number of threads may differ for each country.
• The files are grouped by batch; one batch is a set of two or three files. For each country, there is a loading order set-up, which determines the required order in which the batches have to be loaded.
• The different loading orders can also have a maximum percentage of threads set-up. For instance, a particular country can have eight threads in total. However, we may specify that the highest priority batches can take up only 50% of the eight threads.
• The threads should have the same workload, as much as it is possible. As one could guess, the file sizes can have significant differences. Therefore, it is not enough to allocate the batches to the threads by having an equal number of batches per thread.
• The process is scheduled for every day, early in the morning. Without the need to mention, this was the easiest part.

The image below illustrates the requirements. Let us assume that the country in the example may have four threads in total. The highest priority batches can take up 50%, while the second highest priority batches 25% of all the threads. Loading order zero means the batch does not have any priority.
(“LO” means loading order and the size of the bubbles represents the file size.)

As shown in the picture, first the appropriate number of threads load the batches having the highest priority. When finished, the threads continue loading the batches next in the queue.

Initial Thoughts

The first thing that came to my mind was the job object in Oracle. In short, a job is a scheduled executable task. In most cases, it is a PL SQL procedure or any other Oracle executable object. However, we can also schedule, for example, shell scripts on the server. As jobs are executable in different sessions, they seemed to be worth considering. As I started thinking, a few questions arose. If each job loads one batch, how am I supposed to know when a particular job has finished so that I can start the next one? Moreover, even if I solve the first issue, how will I handle the set-up loading orders and number of maximum threads? Considering all of the requirements, it seems too complex having multiple jobs and needing to know when and which one I should start. I thought, if only there were a way to determine the list and order of batches in advance for each thread. Each time I face an issue of this kind, I start to “dig” and I found a very promising solution. The Chains.

The Solution

To be honest, it was not until this point that I had first become acquainted with chains. A chain is a set of steps executed and scheduled based on a condition, usually depending on the outcome of the previous step(s). The condition can be the success, failure, completion or exit-code of the previous step(s). It can also include logical expressions such as AND / OR. It is very important to highlight that chains will run in different sessions, by means of which we can achieve parallelism. If you wish to read more about chains, the official Oracle documentation is excellent and easy to understand. This article is not about rephrasing it.

So, great news, we have finally found something that looks quite promising to solve our “challenge”. We can regard one chain as one thread. After this, we can allocate the file batches to the chains in advance as steps. In my final solution, I created a program object for each batch and these programs each represent one step. Having these in mind, I created a PL SQL procedure to; loop through all the files, create the program objects per batch and allocate, split them into the appropriate number of chains, considering all the mentioned requirements above.
Just as a note, I used external tables to load the files into the database. To loop through the files, I used an external table with preprocessor. You can read more about it in my earlier article.

Some code samples related to Chains

Creating a chain:

DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name          => 'CHAIN_NAME',
    comments            => 'Comment',
    rule_set_name       => NULL,
    evaluation_interval => NULL
);

Before a chain can be run by a job it must be enabled:

DBMS_SCHEDULER.ENABLE ( name => 'CHAIN_NAME' );

Adding a step to a chain:

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name     => 'CHAIN_NAME',
    step_name      => 'STEP_NAME',
    program_name   => 'PROGRAM_NAME'
);

The program_name is the name of the program object that you want to execute. A chain step can be a program object, another chain or an event schedule.
There are a couple of step attributes that you can configure such as PAUSE, SKIP, RESTART_ON_FAILURE or RESTART_ON_RECOVERY. Again, the intention of this article is not to replace the Oracle documentation, so I encourage you to read more about these.

You can define new rule(s) for each newly added step, similar to a condition:

DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name  => 'CHAIN_NAME',
    comments    => 'Comment',
    rule_name   => 'RULE_NAME',
    condition   => 'PREV_STEP_NAME COMPLETED',
    action      => 'START CUR_STEP_NAME'
);

As you can see, the condition to start the current step is that the previous step is completed. The outcome does not matter. When we are dealing with the first step of the chain, we can just put TRUE as condition.

To mark the end of the chain, you have to create a chain rule like this:

DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name  => 'CHAIN_NAME',
    comments    => 'Comment',
    rule_name   => 'RULE_NAME',
    condition   => 'LAST_STEP_NAME COMPLETED',
    action      => 'END'
);

Running a chain:

DBMS_SCHEDULER.RUN_CHAIN (
    chain_name    => 'CHAIN_NAME',
    job_name      => 'CHAIN_NAME_PARTIAL_JOB',
    start_steps   => NULL
);

When you run the chain, it creates a temporary job to do so. You can either provide a job name or a default job is assigned. If start_steps is NULL, it means the chain will start normally, starting with the first step. However, you can specify different steps to start with.

Dropping a chain:

DBMS_SCHEDULER.DROP_CHAIN (
    chain_name  => 'CHAIN_NAME',
    force       => TRUE
);

The solution is working well, my boss is happy, I am happy and everyone else is happy.

If you have any comments and other ideas, I would be happy to read them.