5 Replies Latest reply on Feb 23, 2018 1:28 AM by Scott Wesley

    elapsed_time in apex_workspace_activity_log

    Keegan_W

      Trying to get a better understanding of what elapsed_time is to use it in custom charts to show our highest-used APEX apps.

       

      The description in the apex_dictionary is "Elapsed time to generate page source"

       

      So, if you were to sum the elapsed_time for a certain app and time period, this would be the sum of all the times any page in the app was fully loaded (in seconds)?

       

      elapsed time.JPG

       

       

      Thank you!

        • 1. Re: elapsed_time in apex_workspace_activity_log
          Scott Wesley

          That's how I use it. (Though it's not the entire time the user may be waiting. Networking lag...)

           

          And I marry this with frequency, and compared medians & averages to find those that are used often, occasionally slow, frequently slow, rarely used, etc.

          1 person found this helpful
          • 2. Re: elapsed_time in apex_workspace_activity_log
            Keegan_W

            Thanks Scott!  Do you have an example of a query where you've married it with frequency?

            • 3. Re: elapsed_time in apex_workspace_activity_log
              Scott Wesley

              Yes, I should have clarified further. I think I used an odd choice of words

               

              Here I'm just looking at how often the page was used during the day.

               

              select
               to_char(view_date,'hh24') dt
               ,count(*) c
               ,min(elapsed_time) min
               ,round(median(elapsed_time),2) median
               ,round(avg(elapsed_time),2) avg
               ,max(elapsed_time) max
               ,round(avg(elapsed_time)-median(elapsed_time),2) diff
              from apex_workspace_activity_log
              where view_date > sysdate-2 -- last two days
              and application_id = :APP_ID
              and page_view_type = 'Rendering'
              group by to_char(view_date,'hh24')
              order by dt desc
              

               

               

              I find it interesting how the outliers tend the average away from the median as the count drops.

               

              And you could do what some of the app builder reports do and multiple them out to find weighted averages / medians

               

              -- top pages
              select r.application_id||':'||r.page_id page
               ,p.application_name||' - '||p.page_name page
               ,count(*) c
               ,count(distinct apeX_user) unique_users
               ,sum(elapsed_time) sum_elapsed
               ,count(*) * sum(elapsed_time) weighted_sum
               ,count(*) * median(elapsed_time) weighted_median
               ,max(elapsed_time) max_elapsed
               ,round(avg(elapsed_time),3) avg_elapsed
               ,median(elapsed_time) median_elapsed
               ,count(case when page_view_type = 'Rendering' then 1 end) render
               ,count(case when page_view_type = 'AJAX' then 1 end) ajax
              from apex_workspace_activity_log r
               join apx_application_Pages p
                on p.page_id = r.page_id
                and p.application_id = r.application_id
              where  r.application_id = :MY_APP
              group by r.application_id||':'||r.page_id
               ,p.application_name||' - '||p.page_name 
              order by  c desc
              

              In this case, I might ask why page 5 has a higher median than 1 or 3 (particularly since I'm aware of what those pages do...)

               

              I love this data.

              • 4. Re: elapsed_time in apex_workspace_activity_log
                Keegan_W

                These are great examples - thank you so much Scott!

                 

                I'm not sure if this is a silly question but is each elapsed_time entry for initial page load only or also for page refresh (such as generating a new chart or report on the same page with new filters)?

                • 5. Re: elapsed_time in apex_workspace_activity_log
                  Scott Wesley

                  I don't think it's a silly question. If I remember correctly, the page_view_type column was introduced in APEX 5.0 to help answer this question.

                  So you get separate records for each, and check out the request_value column to give it some more context.