The Oracle parameter undo_retention determines how long that data remains in the undo segment. In my example, it is set to 900 seconds, so I can only query versions in the last 15 minutes. If I attempt to go back further than this I will get an error.
Now, I can see each of the committed versions of the record. Note that each version is the result of a different transaction ID.
Reading up from the last and earliest row in the report, you can see the history of this process request record.
- At line 8 it was inserted (the value of psuedocolumn VERSION_OPERATION is 'I') at RUNSTATUS 5 (queued) by the component the operator used to submit the record.
- At line 7, RUNSTATUS was updated to status 6 (Initiated) by the process scheduler.
- At line 6 the process begins and updates the BEGINDTTM with the current database time, and sets RUNSTATUS to 7 (processing).
- At line 5 the process completes, updates ENDDTTM to the current database time, and sets RUNSTATUS to 9 (success).
- At line 4 the ENDDTTM is updated again. This update is performed by the Distribution Server process in the Process Scheduler domain as report output is posted to the report repository. Note that the value is 1 second later than the VERSIONS_ENDTIME, therefore this time stamp is based on the operating system time for the host running the process scheduler. This server's clock is slightly out of sync with that of the database server.
- At lines 3 to 1 there are 3 further updates as the distribution status is updated twice more.
For me, the most interesting point is that ENDDTTM is updated twice; first with the database time at which the process ended, and then again with the time at which any report output was successfully completed.
I frequently want measure the performance of a processes. I often write script that calculate the duration of the process as being the difference between ENDDTTM and BEGINDTTM, but now it is clear that this includes the time taken to post the report and log files to the report repository.
For Application Engine processes, you can still recover the time when the process ended. If batch timings are enabled and written to the database, the BEGINDTTM and ENDDTTM are logged in PS_BAT_TIMINGS_LOG.
You can see above that ENDDTTM is the time when the process ended.
That gives me some opportunities. For Application Engine programs, I can measure the amount of time taken to posting report content, separately from the process execution time. This query shows me that this particular process took 49 seconds, but the report output took a further 9 seconds to post.
For more detail on the Flashback Query syntax see the Oracle SQL Reference.