Timer Implementation and Database Scheduler Usage

Timer Implementation.

The Flows for APEX Timer mechanism works as follows:

Event	Action
Timer Started	New Timer created in flow_timers table
Step Timers	dbms_scheduler wakes-up (22.2 default = every 10 seconds, v23.1 default = every 5 minutes). The dbms_scheduler wakes up, processes any timers that have now gone past their firing time.

Design rationale for this implementation were:

• allow new timer events to be created by the Flows for APEX engine by simply inserting a row into an application table.
• no privileges required by end users once the scheduler job has been created
• throttle scheduler wake-ups to once every 10 seconds on high volume applications
• use the lightest weight job/process mechanism available in dbms_scheduler

Side effects / consequences of this implementation are:

• timer intervals are ‘quantized’ to the next wake-up - so timers will fire not at the exact time specified, but at the next call after that time.
• the dbms_scheduler wake-up occurs every 10 seconds (upto v22.2), whether there is work to do or not. (8640 times per day)
• large, shared-environment systems (like apex.oracle.com) with many installed Flows for APEX workspaces can see heavy scheduler wakeup loads (apex.oracle.com has over 60 installatons at the time of writing, causing over 500K scheduler wake-ups per day!)

System architects who are preparing a production application deployment should consider the number of timer events they expect to occur, and the minimum interval required between timer events. How frequently / how accurately do timer events need to be scheduled? Should this be every 10 seconds, every minute, every 5 minutes?

On shared environments being used for testbeds, self-training, and demonstrations – such as apex.oracle.com – the typical usage pattern is that Flows for APEX wil be used intensively for a few days for initial familiarisation, training, and testing before being left for days or weeks until another intense period of use. Current installations (v22.2) will continue to wake-up every 10 seconds, 24/7, regardless of workload. Often these are used for a few weeks, but then continue to operate for months or years.

Changes in Flows for APEX v23.1

1. To reduce unnecessary scheduler loading, the default timer wake-up period is now set to once every 5 minutes.

2. The Flows for APEX application Configuration page now contains a Timer region, that allows you to easily enable / disable timers, and to set the stepping frequency.

3. An API call has been added so that a user can cause the timers to be stepped immediately, on-demand. (flow_api_pkg.step_timers).
4. The Flow Monitor » Instance Details page now includes an Action Menu item so that timers can be checked immediately. When you are testing timer functionality, or demonstrating timer capability, you can now Step Timers and then Refresh to step due times forward.

1. Production and Development Projects are free to set the interval to a shorter interval, if required.
2. On personal test beds , demonstration systems, etc., the timer interval should be less frequent. Use the flow_api_pkg.step_timers call attached to a menu item or a button in your demo to step times forward on demand (that creates a better demo environment than having to wait 10-20 seconds for the next timer stepping event with v22.2 or earlier!).
3. On personal test beds and demonstrators, please use the configuration panel to disable timers if you are not using them for a few days or longer.
4. The Oracle Corporation hosted shared APEX environments, apex.oracle.com (and apex.oraclecorp.com), are restricted to a step interval of greater than 1 minute.