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:
It is possible to extract the following information from the queries:
The statistics created by CM_BATCH_STATISTICS can be analyzed using the following rules: