SQLDeveloper Reports to track RMS batch performance - part II

In my previous post, I showed how to build an SQL Developer report that aids in analysing the performance of RMS batches. Today I am going to share a new version of the report that reveals much more detailed information down to the thread level by leveraging the Master/Detail feature of SQLDeveloper reports.

The new version of the report has three new tabs:

  1. Thread Graph tab - Provides a graphical overview of time elapsed at the individual thread level.
  2. Threads tab -  Detailed list of thread level start/end times, thread level status, process ids and error messages.
  3. Runtimes tab - Visual comparison of minimum, average, maximum and latest runtimes.

If you want to download the new version of the report, just click here. If you prefer to build it yourself, you may head to this link for a tutorial and use the queries at the bottom of the post.

When you launch the report, the bottom pane (detail section) would be empty, it gets populated when you click one of the items in the top pane (master section). As you change your selection in the top pane, the bottom pane updates instantly.

The Thread Graph tab is very useful to see how the load is being distributed across the available threads. One quick glance at this tab will tell you if there is a skew in the distribution of processing between the threads.

Ideally, all threads should take almost the same amount of time. If some threads are taking significantly longer than the others, that is a sign that the department/class/subclass/location that is being processed by that thread may be significantly larger than others and you may need to address the skew by redistributing the data for even spread. Depending on the query that thread is running, you may be able to create more partitions for the larger data or create histograms that take the skew into account.

If some threads do not have any data to process and other threads are overloaded, look for gaps in the data. For e.g., if you have departments numbered 410, 420, 430, etc, invariably, the default modulo algorithm may waste threads by assigning threads to departments such as 421, 422, etc which do not exist. Remember that you can always modify the RESTART_THREAD_RETURN function to replace the default logic with your own custom logic, for e.g., one that takes into account the number of child entities in the Dept/Class/Subclass and use that to decide which thread should pick it up to process. Refer to the Operations Guide 3 for details.

The Threads tab tells you the status of each thread, start/end/restart times, error messages (if any) and the PIDs. I always find it easier to look at this report than query restart_program_status every time or log into the batch scheduler.

The Runtimes tab has a specific purpose - compare the latest run with previous run stats. For e.g., in this case, every time the batch runs, it takes at least 1.2 minutes. The average runtime is 1.79 minutes. The longest it has ever taken is 2.72 minutes, and the latest run took 1.5 minutes. This helps me compare the latest run and see where it stands vis-a-vis historical run times. This can be very useful to translate what would usually be just numbers, into knowledge you can use! Next time you run a batch, this can tell you if the "x" minutes it took is typically, better or worse.

Remember that you can always right click a report in the Navigation pane on the left side and pick HTML to save an offline HTML page with a dump of the entire data which changes as you click on the data. This way, you don't have to be connected to analyse your data and makes it a very useful tool to take with you when you are "on the move".

Now these are the queries that I have used, feel free to modify them to suit your precise needs.

Thread Graph (Style: Chart, Chart Type: Bar/Vertical Cluster)

select thread_val,
       round((finish_time-start_time)*24*60)  as elapsed_time
from   restart_program_status
where  restart_name=:RESTART_NAME
order by 1 asc

Threads (Style: Table, Table Type:  Horizontal)

select thread_val,
       to_char(start_time,'dd/mm/yyyy hh24:mi:ss'),
       to_char(finish_time,'dd/mm/yyyy hh24:mi:ss'),
from   restart_program_status
where  restart_name=:RESTART_NAME
order by nvl(restart_time,start_time) asc

Runtimes (Style: Gauge, Gauge Type: Status Meter, Enable "Use Query to specify the minimum, maximum, low and high values")

select curr_runtime, AbsMin_runtime, max_runtime, min_runtime, avg_runtime from (
  with rps as (select round((nvl(finish_time,sysdate)-start_time)*24*60,2) as curr_runtime 
                 from restart_program_status 
                where restart_name=:RESTART_NAME
                  and start_time=(select max(start_time) from restart_program_status where restart_name=:RESTART_NAME))
select restart_name,
       (select max(round((nvl(finish_time,sysdate)-start_time)*24*60,2)) 
                 from restart_program_status 
                where restart_name=:RESTART_NAME
                  and start_time=(select max(start_time) from restart_program_status where restart_name=:RESTART_NAME)) as curr_runtime,
       0 as AbsMin_Runtime,
       round(max((nvl(finish_time,sysdate)-start_time)*24*60),2) as Max_Runtime,
       round(min((nvl(finish_time,sysdate)-start_time)*24*60),2) as Min_Runtime, 
       round(avg((nvl(finish_time,sysdate)-start_time)*24*60),2) as Avg_Runtime 
  from restart_program_history, rps
 where restart_name=:RESTART_NAME
 group by restart_name

Please note that there is a bug in SQL Developer that resets the "Use Query to specify the minimum, maximum, low and high values" from persisting, please Enable this every time you make changes to the report.


Post a Comment:
  • HTML Syntax: NOT allowed

I am Ramnath Nayak, a Senior Principal Consultant with Oracle's Retail Consulting group in EMEA.

As a technical consultant I have worked extensively on Oracle Retail implementation projects for over a decade.


« March 2015