Skip to main content

Heat maps for APEX applications usage


You have a lot of valuable insights in APEX logs and you most likely don't realize that. And that is because you don't have a tool to display this data in a meaningful way.

Some things you can extract from the apex_workspace_activity_log table:

  • average or maximum times for each page for several actions (rendering, processing, AJAX calls...)
  • number of requests, so you can identify highly used pages (or not used pages)
  • number of users and sessions as a complementary metrics
  • you could also extract browsers, IP addresses, buttons clicked, errors...

There are other things you can track.

I love heat maps. So I created this mini app, which will allow you to explore the data from apex_workspace_activity_log as a heat maps. I use whole month data and the T1..T30 columns represents dates in past, T1 as yesterday, T2 as day before yesterday... You just have to set the colors and thresholds to suits your needs.

What I created:

  • simple page with IG to show data as heat map and allow easy extendability
  • bunch of LOVs (and views) for the filters
  • view to preprocess data from log based on the filters
  • function to calculate color based on the value and setup table
  • setup table to adjust colors and thresholds

All of this is on my Tracking repo and as always you can use it on your projects.

And there is an article dedicated to coloring IG.

The most challenging part was to calculate the color in between two colors to represent value in between two tresholds:

        in_value            AS value,
        (in_value - t.left_treshold) / (t.right_treshold - t.left_treshold) AS value_perc,
        TO_NUMBER(SUBSTR(t.left_color,  2, 2), 'XX') AS left_r,
        TO_NUMBER(SUBSTR(t.left_color,  4, 2), 'XX') AS left_g,
        TO_NUMBER(SUBSTR(t.left_color,  6, 2), 'XX') AS left_b,
        TO_NUMBER(SUBSTR(t.right_color, 2, 2), 'XX') AS right_r,
        TO_NUMBER(SUBSTR(t.right_color, 4, 2), 'XX') AS right_g,
        TO_NUMBER(SUBSTR(t.right_color, 6, 2), 'XX') AS right_b
    FROM (
            MIN(t.color_bg) KEEP (DENSE_RANK FIRST ORDER BY t.treshold DESC)    AS left_color,
            MIN(t.treshold) KEEP (DENSE_RANK FIRST ORDER BY t.treshold DESC)    AS left_treshold,
            MIN(r.color_bg) KEEP (DENSE_RANK FIRST ORDER BY r.treshold)         AS right_color,
            MIN(r.treshold) KEEP (DENSE_RANK FIRST ORDER BY r.treshold)         AS right_treshold
        FROM trc_lov_colors t
        JOIN trc_lov_colors r
            ON       =
        WHERE 1 = 1
            AND      = in_name
            AND t.treshold  <= in_value
            AND r.treshold  > in_value
    ) t
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_r - t.left_r) * t.value_perc + t.left_r, 0), 255), 0), 'FM0X') ||
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_g - t.left_g) * t.value_perc + t.left_g, 0), 255), 0), 'FM0X') ||
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_b - t.left_b) * t.value_perc + t.left_b, 0), 255), 0), 'FM0X')
INTO out_color


  1. Hello Jan, I like the app you created.

    Any idea how to import/install this in my workspace? I did it manually (f755.sql, all tables/views/package) but run in the following issue after starting the application:

    Error: ORA-06550: line 1, column 7:
    PLS-00201: identifier 'TSK_APP.INIT_DEFAULTS' must be declared

    If I check your files in the repo, I cannot find any with TSK_APP.INIT_DEFAULT included to serve the application process.

    1. Thanks! I am working on some interesting enhancements, so stay tuned.
      You need to install the app AND also the DB objects, checkout patches_done/INSTALL.sql script...

    2. I did, unfortunately there is a lot of manual work to do at the moment. I had to install some parts from your CORE and CORE23 repo, had to change a view name in the trc_activity_chart_v.sql file and I'm not done yet.

      At the moment I try to get your TASK repo running because of dependencies from the TRACKING to the TASK application. ;-)

    3. There should not be any dependencies to Tasks app. I agree on the Core package, yes, you need that, ideally the one from Core23 repo and thats just the one package, nothing more.
      The manual work should be then eliminated, because you have all objects listed in the Install script and in proper order. And it is just few objects anyway.
      So what is not working?

    4. Oh, yes, it lists some auth roles from TSK, but they are not needed and not used. You can skip/ignore those for sure.

    5. Removed the references in app, update your copy please.

    6. Sorry for the confusion and thanks for the feedback! Lets connect on LinkedIn if you can.


Post a Comment