Introduction
Most enterprise applications have workloads that need to be run in the background. This is often a more efficient use of system resources and also allows users to continue working with your App while the processing takes place.
APEX provides two options for background processing: APEX Automations and Background Page Processes. This post focuses on Background Page Processes introduced in APEX 23.1 and aims to give you everything you need to know to get started with this important feature.
Advantages
Performing long-running processes in the background has the following advantages:
More efficient use of the ORDS connection pool.
More efficient use of database resources. Move long-running code to run in a different resource consumer group to limit the amount of CPU it can use.
Avoid gateway timeout errors when the page process runs for too long.
Provide a better user experience by allowing users to continue with other activities while the process runs.
Setting the Foundation
We need to understand some fundamentals about background page processing before creating our first background page process.
Underlying Technology
The database scheduler DBMS_SCHEDULER executes background page processing. Your application's parsing schema must have the CREATE JOB privilege to run background page processing jobs.
Export/Import
The application import flow changes when you create a Background Page Process in your APEX App. When you deploy an update to your App, the Import process will display a message warning you that you have a background page process and offering you the option to disable background execution temporarily:
If you click 'Disable Background Execution', APEX sets the Application level attribute 'Maximum Background Page Process Jobs' to 0.
💡
Note the discrepancy between the messages above, which state that the attribute 'Maximum Scheduler Jobs' will be set to zero, whereas it actually sets the attribute 'Maximum Background Page Process Jobs' to zero.
After clicking 'Disable Background Execution', the page refreshes, and you can then click 'Replace Application' safe in the knowledge that a new background process cannot be submitted during the install.
APEX sets the Application attribute 'Maximum Background Page Process Jobs' to the value in the App import after the installation is complete.
Setting Limits
Because we are talking about long-running processes that may be resource-intensive, it may be necessary to impose limits on the number of background processes that can run simultaneously and or the amount of resources these jobs can consume.
Instance Level
We can provide a DBMS_SCHEDULER job class (and associated Resource Consumer Group) at the instance level, which APEX should use when running the Background Page Processes. Just set the 'Background Page Process Job Class' attribute under Administration Services > Instance Settings > Background Jobs:
Note:
If you are running on OCI Autonomous or APEX Service, you can use several pre-defined job classes (TPURGENT, TP, HIGH, MEDIUM and LOW). See here for details.
If you set 'Background Page Process Job Class', this will apply to all workspaces and applications.
Workspace Level
You can set the Maximum number of background page processes that can run at any time at the workspace level (across all applications). This applies to all applications in the workspace. Navigate to Administration Services > Manage Workspaces > Existing Workspaces, click the Workspace name and then locate the 'Maximum Background Page Process Jobs' attribute under 'Workspace Isolation'.
Note: This option seems unavailable on the OCI Autonomous Database and OCI APEX Service.
Application Level
You can set 'Maximum Background Page Process Jobs' for an individual application under Application Definition > Properties.
Creating Background Page Processes
Now that we have covered the foundations let's dive into creating a background page process.
Start by creating a Page Process:
Type: Execution Chain
- Indicates that you want to execute a sequence of page processes in the foreground or the background.
Execution Chain: None
- This field tells APEX which execution chain the process is a part of. Given that this is our 'Top Level' process, we must leave this set to
None.
Settings > Run in Background: Selected
- This is the 🪄 magic setting. This tells APEX that we want to run this process in the background.
Settings > Return ID into Item: <APEX_PAGE_ITEM>
- If you provide a page item for this property, APEX will populate it with the
execution_id of the background page process that was submitted. This helps us track the progress of the background page process. See the 'Instrumentation' section for more details.
Settings > Context Value Item: <APEX_PAGE_ITEM>
- If you provide a page item for this property, APEX will use the value in the page item and the 'Serialize' attribute to determine if the background page process can be run in parallel with other background page processes. See the 'Parallel or Serial' section below for more details.
Settings > Temporary File Handling: Ignore (Other Options: Move, Copy]
- This property determines how to handle temporary files uploaded through a 'File Upload' page item. This could be useful if you load data from uploaded files in the background page process.
Settings > Executions Limit: 1
This setting allows you to limit the number of active (running or enqueued) instances of the background page process in one APEX session.
For example, if you set this to 2 and the user has one instance of the page process running and another enqueued, a third submission will receive the error message "Background Executions Limit exceeded for Example Background Page Process". Once the running instance completes, there is only one active instance and the user can submit another.
This is useful if you want to prevent more than one version of the process from running at a time or if you want to limit the number that can be submitted for performance reasons. See the 'Running Jobs and Parallel or Serial' section below for more details.
Settings > Submit Immediately: Disabled
- This property determines whether the background page process should be submitted within the current transaction (disabled) or outside (enabled). Setting it to enabled is like having an autonomous transaction; the background process will get submitted regardless of whether the page submission transaction complies successfully.
Serialization > Serialize: Disabled
- See the 'Parallel or Serial' section below for more details.
When Already Running: [Wait, Error]
This option tells APEX what to do if another process with the same context is already enqueued or running.
Wait: Add the new background page process instance in an enqueued state. It will run once the other process with the same context is completed.
Error: Prevent a second instance of the background page process from being submitted. Users will get this error message:
Now that we have defined the background page process, we need to add sub-processes that define what work should be done by the background process.
Identification > Type: Execute Code
- The property is where we define the type of process we want to run in the background. If you choose 'Invoke API', you can select a REST Source for the Location below.
Identification > Execution Chain: Name of Parent Process
- This is where you associate the child process with the parent process (or the execution chain).
Source > Location: Local Database
- In the above example, I am running PL/SQL code. Another alternative is REST Enabled SQL.
⏹
That concludes the setup of the Background Page process and sub-processes.
Running Jobs and Parallel or Serial
Several factors determine whether your page processes run in parallel or are serialized and how many background page processes can be run simultaneously.
Maximum Running Background Processes
The total number of background page processing jobs running simultaneously is determined by the 'Maximum Background Page Process Jobs' property at the workspace and application level, discussed earlier. No matter how many background page processes your users submit, only the number set in 'Maximum Background Page Process Jobs' can run simultaneously.
The 'Executions Limit' property of the page process is unrelated to 'Maximum Background Page Process Jobs'. Let's take the following scenario:
Parallel or Serial
In a previous section, we discussed the 'Serialize' and 'Context Value Item' properties of background page processes.
These properties govern whether APEX will allow multiple instances of a background process to run in parallel.
For example, three users submit the same background page process simultaneously. The 'Serialize' property is set to enabled.
User 1 - Context Item Value: 1234
User 2 - Context Item Value: 1234
User 3 - Context Item Value: 5678
The first two instances will be serialized. i.e., APEX will ensure one is finished before it starts the second. The instance for User 3 will be allowed to run at the same time as 1 and 2 (assuming the maximum number of simultaneous jobs set in 'Maximum Background Page Process Jobs' has not been exceeded. When Serialize is enabled, processes submitted with the same Context Value run one after another. If the Context Item is left empty and Serialize is enabled, then all instances of the background page process are serialized.
Note: The property "When Already Running" also comes into play here.
Wait: New background page processes with the same context will be submitted in an enqueued state. It will run once the other processes with the same context are completed.
Error: Prevents a second instance of the background page process from being submitted. Users will get the error message "Another Execution of Example Background Page Process is already running for context <<Conext Value>>".
Note: If Serialize is disabled, the Context Value will be ignored. Even if Serialize is disabled, you may still find it useful to set a context value, as it shows up in the monitoring view and PL/SQL APIs (see below).
Background Page Process Context
When a background page process starts running, a new APEX session is created behind the scenes, and the session state from which you submitted the background page process is copied to it.
The screenshot below shows the Monitor Activity page for the session from which I submitted a background page process. The 'Work Session ID' is under the 'Background Processing' tab.
If we drill down on the 'Work Session ID',
We can even see session state state values copied from the original session.
Because the background page process runs in the same context as your original session, you can reference session state values.
To illustrate this, if my background page process has the following code:
BEGIN
apex_background_process.set_status('Starting Step 1');
apex_debug.error('XXXX Message from Background Procsess [%s]',
V('AI_USER_DISPLAY_NAME'));
dbms_session.sleep(10);
END;
Once the process is complete, I can see apex_debug_messages for the background process:
Instrumentation
One of the nice features of Background Page processes is the ability to keep track of the progress of the background process. You may have noticed in my example process above that the first step in the execution chain was to run the following code:
BEGIN
apex_background_process.set_status('Starting Step 1');
dbms_session.sleep(10);
apex_background_process.set_status('Completed Step 1');
END;
The first line of code tells the background process to update the status of the process to 'Starting Step 1'. This is reflected in the status_message column in the APEX view apex_appl_page_bg_proc_status. Subsequent calls to apex_background_process.set_status changes the value in apex_appl_page_bg_proc_status.status_message.
When used in conjunction with the 'Return ID into Item' property of the parent process, apex_appl_page_bg_proc_status can be used to show users the status of the background job.
The above example uses a Content Row region with the below SQL to display the current status of the background page process.
SELECT process_name
, current_process_name
, context_value
, INITCAP(status_code) status_code
, status_message
, TO_CHAR(last_updated_on, 'MM/DD/YYYY HH:MI:SSpm') last_updated_on
FROM apex_appl_page_bg_proc_status
WHERE execution_id = :P10_PROCESS_ID
process_name - The name of the parent background process/execution chain
current_process_name - The name of the currently running child process within the execution chain
context_value - The value from the page item specified in the 'Context Value Item' property
status_code - The current status of the background process. See here for a list of statuses
status_message - The last value set by apex_background_process.set_status, or the value from the child process 'Success Message'. This value is empty once the process is completed
Monitoring
In this final section, we will cover how you can track the status of background page processes from the back end.
APEX View
Maybe the easiest method for tracking the status of background page process jobs is to query the APEX view apex_appl_page_bg_proc_status.
Monitoring Sessions
You can also see the status of background page processes for a specific session from APEX Builder by navigating to Monitor Activity > Active Sessions and then selecting the session ID you want to look at. Click the 'Background Processing' tab to see all the background page processes run during the session. If the process is still running, you can terminate it from here.
If you are logged into APEX Builder, you can click the Session icon in the toolbar of the running page:
Then, select 'Background Executions' from the 'View' field to see the process's current and previous executions for the current session.
PL/SQL API
I won't go into detail in this post, but the APEX_BACKGROUNS_PROCESS PL/SQL package includes APIs to terminate a running process and get the status of a running process.
Conclusion
This post should have provided you with everything you need to know to get started with APEX Background Page Processing.
🙏
This was a lengthy post, so I appreciate you hanging in there.
Read More
