Timer Implementation and Database Scheduler Usage
The Flows for APEX Timer mechanism works as follows:
|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 the 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 (pto 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
- To reduce unnecessary scheduler loading, the default timer wake-up period is now set to once every 5 minutes.
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.
- An API call has been added so that a user can cause the timers to be stepped immediately, on-demand. (
- 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 Timersand then
Refreshto step due times forward.
- Production and Development Projects are free to set this to a shorter interval, as required.
- On personal test beds , demonstration systems, etc., the timer interval should be less frequent. Use the
flow_api_pkh.step_timerscall 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!).
- On personal test beds and demonstrators, use the configuration panel to disable timers if you are not using them for a few days or longer.
- The Oracle Corporation hosted shared APEX environments,
apex.oraclecorp.com, are restricted to a step interval of greater than 1 minute.