Querying the Batch Run Tree using SQL

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)
AS
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)
AS
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).

Comments:

Aw, this was a really quality post. In theory I'd like to write like this too - taking time and real effort to make a good article... but what can I say... I procrastinate alot and never seem to get something done.

Posted by Mohammed Matthiesen on May 06, 2010 at 03:11 AM EST #

Interesting reading

Posted by t-shirt on August 18, 2010 at 09:18 PM EST #

Howdy, i read your blog occasionally and i own a similar one and i was just wondering if you get a lot of spam comments? If so how do you prevent it, any plugin or anything you can advise? I get so much lately it's driving me mad so any assistance is very much appreciated.

Posted by Consignment Shop on December 15, 2010 at 05:33 AM EST #

I understand that but where does it take us? Girl, u better have a license coz u r drivin me crazy.

Posted by Bernadette Hornyak on December 27, 2010 at 01:15 AM EST #

What is this parcel's software, I ponder? Looks wonderful!

Posted by Bebe Demark on December 28, 2010 at 01:45 PM EST #

If there is something to pardon in everything, there is also something to condemn.

Posted by iphone apps on January 07, 2011 at 06:47 AM EST #

Thanks for sharing these useful information! Hope that you will continue doing nice article like this.

Posted by Last minute rodos on January 27, 2011 at 11:18 PM EST #

Keep writing ,.. I will consider to bookmark your page.

Posted by Anton Vandenberghe on February 02, 2011 at 06:50 PM EST #

This is good info! Where else can i find out more?? Keep up the good work :)

Posted by Josphine Councilman on February 04, 2011 at 11:36 AM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Anthony Shorten
Hi, I am Anthony Shorten, I am the Principal Product Manager for the Oracle Utilities Application Framework. I have been working for over 20+ years in the IT Business and am the author of many a technical whitepaper, manual and training material. I am one of the product managers working on strategy and designs for the next generation of the technology used for the Utilities and Tax markets. This blog is provided to announce new features, document tips and techniques and also outline features of the Oracle Utilities Application Framework based products. These products include Oracle Utilities Customer Care and Billing, Oracle Utilities Meter Data Management, Oracle Utilities Mobile Workforce Management and Oracle Enterprise Taxation and Policy Management. I am the product manager for the Management Pack for these products.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
9
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today