3 Replies Latest reply: Oct 10, 2013 3:35 PM by jkoradba RSS

    How to monitor execution duration exceeding usually expected time?

      I have a need to monitor in real time execution duration so we can identify workflows jobs that for some reason are occassionaly running longer than expected. This is so we can check what is going on and perhaps delay scheduled execution of reports. Hoping to trigger an e-mail alert when certain job execution duration is exceeded. I know I can query ALL_RT_AUDIT_EXECUTIONS, but I believe this will return total elapsed time at the end of workflow execution.
        • 1. Re: How to monitor execution duration exceeding usually expected time?
          Here you have an example of a script that does just that using analytic functions.
          It shows every map within the context of the driving procesflow (one level deep)
          Even errors are shown if any occurred.
          (run on 11gr1 but should work on 11gr2 as well)

          The example shows only relevant runs of las week (sysdate - 7)

          psec means previous seconds (yesterday)
          asec means average seconds of last week

          I use it in all my projects to get and keep stable load processes.

          ae.execution_audit_id audit_id,
          amr.map_run_id run_id,
          tae.execution_name proces,
          amr.start_time start_time,
          amr.map_name map,
          ae.return_result res,
          amr.elapse_time sec,
          lag (amr.elapse_time, 1) over (partition by amr.map_name, ae.object_location_name order by amr.start_time) psec,
          round (amr.elapse_time / 60, 1) min,
          avg (amr.elapse_time) over (partition by amr.map_name, ae.object_location_name) asec,
          amr.number_records_selected sel,
          amr.number_records_inserted ins,
          amr.number_records_updated upd,
          amr.number_records_merged mrg,
          amr.number_records_deleted del,
          amr.number_errors err,
          aum.message_text msg
          owbsys.all_rt_audit_executions ae,
          owbsys.all_rt_audit_executions tae,
          owbsys.all_rt_audit_exec_messages aum,
          owbsys.all_rt_audit_map_runs amr
          ae.top_level_execution_audit_id = tae.execution_audit_id(+)
          and ae.execution_audit_id = amr.execution_audit_id(+)
          and ae.execution_audit_id = aum.execution_audit_id(+)
          and amr.start_time >= sysdate - 7 -- last 7 days only
          order by
          ae.execution_audit_id desc,

          • 3. Re: How to monitor execution duration exceeding usually expected time?

            Very useful.  Thank you.  I was looking around for other ideas and found your query.  I want to run a query that will show me all OWB jobs coming from different projects.  Right now I have five different OWB projects and workflow jobs on one production instance.  I should be able to run this in OWBSYS to see all of them?  I will need to check.  Just started looking into this.


            There is also a set of reports available in a zip here:  Oracle PL/SQL Sample Code. Scroll down to "OWB SQL Developer Reports".  These then can be installed in Oracle SQL Developer.


            Also, is there somewhere a document that elaborates more on public views, like for instance all_rt_audit_exec_messages and all the other deployment and runtime public views?


            Of course there is the doco, but it doesn't elaborate about each one of them: Public Views for the Runtime Environmenthttp://www.findthatpdf.com/search-59603838-hPDF/download-documents-owb11gr2runtimemodel.pdf.htm


            And then there is this great find with the ER model for runtime.  Have not seen it in the past.