Querying the Batch Run Tree using SQL
By ACShorten on Oct 17, 2008
Part of the infrastructure of Oracle Utilities Application Framework is the Batch Run Tree. No matter how you submit a background process, the run information for the execution of the job is recorded in the Batch Run Tree.
The Batch Run Tree is a series of generic tables that record all sorts of information about the execution of a particular thread of a particular background process. This information is stored for viewing by the provided query screen but can also be a fountain of information about the performance of your background processes.
After a bit of quick investigation I worked out a series of views that can be built that expose the statistics from the Batch Run Tree so that the information can be used in reports and, in V2.1/V2.2 of the products, a custom portal.
When a background process completes it creates a record per thread in the CI_BATCH_INST table. This table includes the batch number, batch thread number and the number of records processed by that thread (as well as number of errors).
For each thread there is a set of messages stored in the CI_LOG_MSG table. This includes a message for the start and end of the process.
The date for the start and end of the process is stored as a parameter to that message in the CI_LOG_MSG_PARM table. This is a generic table that contains all sorts of information and message types. We are only using one part of the table.
Using a query that joins these three tables it is possible to return the start and end times as well as the number of records processed for any execution of any thread for any background process in Oracle Utilities Application Framework based products at a site (including custom processes).
Note: All the view definitions in this article are based upon ORACLE. For customers using DB2 or SQL Server versions of our products alter the view defintions accordingly.
Note: The view definitions below will work for version V1.x and V2.x of Oracle Utilities Customer Care And Billing and Oracle Enterprise Tax Management.
Here are a set of views I created to collate the information into a "readable format":
CM_STARTEND_TIMES– This view combines the above tables into a consolidated view that has a record per start of any execution of any thread for any background process and a record per end of any execution of any thread for any background process.
CREATE OR REPLACE VIEW cm_startend_times
(batch_cd, batch_nbr, batch_thread_nbr, batch_bus_date, batch_status, rec_proc_cnt, rec_err_cnt, scheduler_id, message_seq, parm_seq, datetime, time_cd)AS
SELECT i.batch_cd, i.batch_nbr, i.batch_thread_nbr, r.batch_bus_dt, r.run_status, i.rec_proc_cnt, i.rec_err_cnt, i.scheduler_id, m.message_seq, m.parm_seq, m.message_parm, DECODE (l.message_nbr,11913, 'Start',12114, 'End',12115, 'End', 'Start') FROM ci_batch_inst i, ci_msg_logparm m, ci_msg_log l, ci_batch_run r WHERE i.scheduler_id = m.scheduler_id AND i.scheduler_id = l.scheduler_id AND i.batch_cd = r.batch_cd AND i.batch_nbr = r.batch_nbr AND m.message_seq = l.message_seq AND l.message_nbr IN (11913, 12114, 12115) AND m.message_parm LIKE '2___-__-__-__.__.__';
CM_BATCH_TIMES– This view merges the start and end times of CM_STARTEND_TIMES to calculate the elapsed time (in minutes) and run rate (number of records processed/elapsed time) to produce these statistics for any execution of any thread for any background process. This is the base view used for the analysis.
CREATE OR REPLACE VIEW cm_batch_times
(batch_cd, batch_nbr, batch_thread_nbr, batch_bus_date, batch_status, rec_proc_cnt, rec_err_cnt, start_time, end_time, elapsed_min,thruput)
SELECT s.batch_cd, s.batch_nbr, s.batch_thread_nbr, s.batch_bus_date, s.batch_status, s.rec_proc_cnt, s.rec_err_cnt, TO_DATE (s.datetime, 'YYYY-MM-DD-HH24.MI.SS'), TO_DATE (e.datetime, 'YYYY-MM-DD-HH24.MI.SS'), ROUND ( ( TO_DATE (e.datetime, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (s.datetime, 'YYYY-MM-DD-HH24.MI.SS')) * 1440,2), ROUND ( (s.rec_proc_cnt) / ( ( TO_DATE (e.datetime, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (s.datetime, 'YYYY-MM-DD-HH24.MI.SS'))* 1440),2) FROM cm_startend_times s, cm_startend_times e WHERE s.time_cd = 'Start' AND e.time_cd = 'End' AND s.batch_cd = e.batch_cd AND s.batch_nbr = e.batch_nbr AND e.datetime > s.datetime AND s.batch_thread_nbr = e.batch_thread_nbr AND s.scheduler_id = e.scheduler_id AND e.datetime LIKE '2___-__-__-__.__.__' AND s.datetime LIKE '2___-__-__-__.__.__';
CM_BATCH_STATISTICS – This view calculates basic statistics using CM_BATCH_TIMES across all occurrences of any background process submitted. It calculates minimum, maximum, average elapsed times and run rates (i.e. throughput). Useful for estimation.
CREATE OR REPLACE VIEW cm_batch_statistics (batch_job, min_elapsed_time, avg_elapsed_time,est_elapsed_time, max_elapsed_time, min_thruput, avg_thruput, est_thruput, max_thruput,number_occurances)
SELECT batch_cd,MIN (elapsed_min), ROUND (AVG (elapsed_min), 2), ROUND ((MIN(elapsed_min) + 4 * AVG (elapsed_min) + MAX (elapsed_min))/6, 2), MAX (elapsed_min), MIN (thruput), ROUND (AVG (thruput), 2),ROUND ((MIN(thruput) + 4 * AVG (thruput) + MAX (thruput))/6, 2),MAX (thruput),COUNT (*)FROM cm_batch_times GROUP BY batch_cd;
Note: The est_elapsed_time and est_run_rate columns use a standard estimation algorithm to determine the most likely value. Most likely = (best + 4 * average + worst)/6. This may be altered if not appropriate for your site.
Additionally an extract of CI_BATCH_CTRL_L should be done to associate the batch jobs with descriptions (to make the output readable for reporting). I sometimes add the description into one of the view definitions using a join on BATCH_CD and the appropriate literal for LANGUAGE_CD.
The following limitations apply to the above views:
- To avoid any zero divide errors only end times greater than start times are considered. This means any executions where start time equals end time are ignored.
To avoid miscalculations any process that does not have a corresponding end time for a start time is ignored. This means processes that failed and that were not restarted are ignored.
- Given the flexibility of the architecture, restarts can skew the results (as they result in multiple starts) and are not factored into results. Given that multiple restarts are rare this is deemed acceptable.
- Grouping batch into a batch window on a particular batch night is not supported yet but the queries have been designed so that information may be analyzed in the future.
It is possible to extract the following information from the queries:
- CM_BATCH_TIMES - This is useful for getting analysis for jobs by thread over a long period. This information can also be the basis of a "rolled up" view to provide analysis across threads (if averaged).
- CM_BATCH_STATISTICS - This is a useful summary of the current trends for all batch jobs.
The statistics created by CM_BATCH_STATISTICS can be analyzed using the following rules:
- The Minimum’s can be interpreted as best case scenarios. If the value is zero then it can be ignored.
- The Maximum’s can be interpreted as worst case scenarios.
- The Average can be used for comparison purposes.The columns prefixed with "est" (for estimated) can be used for comparison and estimation purposes.
- Compare the Minimum against the Average, Maximum against the Average to see if the numbers are close or far. Use the "Number of Occurrences" column as a perspective column (e.g. number is low then sample is low etc).
- These views are now available for use in your favorite reporting tool, for extraction (I use Excel to generate graphs) or a custom portal on the browser interface (V2.1 makes this simple).