Introduction
I recently watched an excellent presentation by Carsten Czarski from the APEX Development Team on All Things Background. In his presentation, Carsten touched on several DBMS Scheduler jobs which APEX uses to a) Perform Maintenance activities in your APEX environment and b) Launch scheduled processes such as APEX Automations, REST Source Synchronizations, and Page Background Processes.
In this post, I will review these jobs and explain what they do and when they are scheduled to run.
Scheduler / Co-Ordinator Jobs
The most interesting jobs for an APEX developer are what Carsten called Scheduler or Co-Ordinator Jobs. These jobs run on a scheduled basis to check if some of your code needs to be run, such as APEX Automations, REST Data Synchronizations, and Background Page Processes.
🕐
You may notice that when you schedule an APEX Automation or REST Data Source Synchronization for a specific time, your code does not necessarily start at the exact time you specify. This is because the Scheduler / Co-Ordinator Job kicks off your code, and this job has its own schedule.
ORACLE_APEX_REST_SOURCE_SYNC
This job runs every 10 minutes and checks to see if you have scheduled any REST Source Synchronizations to run at that time (or before). If there are any scheduled REST Source Synchronizations, then it runs them.
ORACLE_APEX_AUTOMATIONS
This job runs every 2 minutes (at 5 seconds past the minute) and checks to see if you have scheduled any APEX Automations to run at that time (or before). If there are any scheduled APEX Automations, then it runs them.
ORACLE_APEX_BG_PROCESSES
This job runs every 2 minutes (at 45 seconds past the minute) and checks to see if any Background Page Processes need to be run. If there are any, then it runs them.
Schedule for Co-Ordinator Jobs
The table below shows the precise schedule that the Co-Ordinator jobs run on.
📖
See the end of this post for the SQL I used to get the job schedules and how to view these scheduled from APEX.
Queue Handlers
What I call Queue Handler jobs are jobs that manage the various queues within your APEX Environment. These include jobs that send out PWA Push Notifications and Emails sent out using the APEX_MAIL API.
ORACLE_APEX_PWA_PUSH_QUEUE
This job runs every 2 minutes (at 0 seconds past the minute) and sends out any pending PWA Push Notifications.
ORACLE_APEX_MAIL_QUEUE
This job runs every 5 minutes (at 0 seconds past the minute) and sends out any pending emails.
ORACLE_APEX_WS_NOTIFICATIONS
This job runs on the hour and the half hour and sends out Interactive Report Subscriptions.
ORACLE_APEX_AUTO_APPROVAL
This job runs every two minutes and creates requested and approved Workspaces.
ORACLE_APEX_ISSUE_EMAILS
This job runs every hour on the hour and sends out emails initiated from APEX Teams Development.
Schedule for Queue Handler Jobs
Maintenance Jobs
Maintenance jobs are jobs that perform maintenance activities within your APEX environment.
ORACLE_APEX_PURGE_SESSIONS
This job purges expired APEX Sessions and runs every hour, on the hour.
ORACLE_APEX_DAILY_MAINTENANCE
This job performs activities such as Archiving Activity Summary, purging REST Source Cache, purging Stale Page/Region Cache, purging Uploaded Files, etc. It runs every day at 1 a.m. database time (usually UTC).
ORACLE_APEX_DAILY_METRICS
This job gathers aggregated workspace, schema, and instance-level metrics. It runs every day at 2 a.m. database time (usually UTC).
ORACLE_APEX_DICTIONARY_CACHE
This job caches database dictionary objects related to APEX Workspaces. It runs every day at 1 a.m. database time (usually UTC).
Who knows why?
ORACLE_APEX_BACKUP
This job performs backups of your APEX Applications that changed in the previous 24 hours. It runs every day at 2 a.m. database time (usually UTC).
ORACLE_APEX_TASKS_PURGE
This job deletes APEX Workflow Approval tasks that have been Canceled, Errored, or Completed that are beyond the retention period. It runs every day at 2 a.m. database time (usually UTC). Read more on Workflow Task Retention and purge rules here.
ORACLE_APEX_TASKS_EXPIRE
Similar to the previous job, this job is also related to APEX Workflow Tasks. It handles re-processing expired Workflow tasks. The job runs every hour on the hour.
ORACLE_APEX_WF_TIMEOUT
This job handles timing out APEX Workflows that have timed out. It runs every 5 minutes.
ORACLE_APEX_WFS_PURGE
This job handles purging completed APEX Workflows. It runs once a day at 3 a.m. This is controlled by the 'Workflow Retention Period Days' setting under Instance Settings.
Monitoring APEX Jobs
You can use the following SQL (run as the SYSTEM or SYS user) to get a list of scheduler jobs owned by APEX:
SELECT job_name
, job_action
, repeat_interval
FROM all_scheduler_jobs
WHERE owner = 'APEX_230100';
You can also view APEX Jobs from the INTERNAL / Administration Services Workspace by navigating to Monitor Activity > Jobs.
💡
You can expose many additional details about each job using the Interactive Report. These details include the PL/SQL package & procedure run by the job and the job schedule.
Clicking on a specific job will show details about its individual executions. This can be helpful when tracking down issues.
In later versions of APEX, you can also find APEX job information under Monitor Activity > Administrator Digest > System Schedule Jobs:
Don't Forget ORDS
Also, don't forget that ORDS has a background job that performs administrative tasks. This job is called ORDS_HOUSEKEEPING_JOB, and it runs from the ORDS_METADATA schema. This job runs every hour on the hour.
Conclusion
I believe that it is important for developers to understand what is happening under the APEX hood. I know that I have learned a lot just by writing this blog post.
🔗 Read More
