Hi all,
I wanted to share an issue we encountered recently, that severely impacted our Appworx instance, which eventually snowballed into issues in other applications. Note that we are running Oracle 19c, and I do not know if this issue is corrected in later versions. Our Appworx is 9.4.4, but this would impact any version.
The issue stems from the Oracle job scheduler, particularly from a function that writes job info to a scheduler table. This function contains a variable that is too small to hold the possible values that it can be set to. Like many things in Oracle, scheduler jobs obtain their IDs from a sequence object, which is just an incremented number.
By default, sequences have a max value of 9,999,999,999,999,999,999,999,999,999. The variable in the function that gets the next ID is defined with a max size of 4 bytes. The biggest number that 4 bytes can hold is 4,294,967,295. If you surpass that number in the job scheduler sequence, many of your jobs will fail to complete. For us, we would see Appworx jobs run, and then the sessions for the jobs in the database would just sit with a wait event of "PL/SQL lock timer".
You can find what your current value is with this SQL:
select last_number from dba_sequences where sequence_name='SCHEDULER$_INSTANCE_S';
You may want to sample that over a period of time to see how quickly your sequence grows. For us, at our current rate, we would encounter this issue again in a little over five years. We will be putting a monitor in place for this.
I am not posting the resolution steps to this issue here, as doing so on you own will void your Oracle support. If you determine that you are approaching this limit, you will want to open an SR with Oracle through your support channel. Hopefully this will keep someone from enduring the 24 hours of confusion and misery we endured.
------------------------------
JimD
------------------------------