ODC Appreciation Day : APEX Workspace Activity Logs

Everything gets a special day these days, and thanks to Tim Hall's encouragement, you can enjoy a vibrant display of appreciation for what the Oracle Developer Community embraces about the technology they use daily.

The first year I talked about Dynamic Actions.
Last year I missed the boat - too busy preparing for some holidays.
This year I'd like to lay down my love for a supply of data every APEX developer has access to: apex_workspace_activity_log.

Yup, I'm talking about a log table.

It's enabled in your Oracle APEX applications by default, and inserts a record in a log table every time APEX renders a page, or a dynamic action interacting with the database thanks to a PL/SQL or Refresh action.

Every time a user opens a page, you know when, who, what, why, and how long it took to generate.

select apex_user as who
  ,application_id||':'||page_id as what
  ,view_date as when
  ,elapsed_time as how_long
  ,page_view_type as why
  ,request_value as more_why
from apex_workspace_activity_log
where application_id >= 4000
and apex_user != 'nobody'

How APEX Builder dashboard knows who's been the busiest developer

We can combine this information to produce a wealth of information. Oracle provides some out of the box under Administration -> Monitor Activity.

I think the first report worth noting is By Weighted Page Performance.

Packaged Reports

This report multiplies how many times certain pages were loaded by how long it took to load, so ordering gives weight to those pages consuming overall time. Here I've also highlighted the Median column, since that too can be an indicator. A higher average that maintains a low median can indicate outliers, while a raised median really should be looked at.

Weighted Page Performance

Fundamentally, we're asking - should I choose to tune a 3 second page that runs once a day, or tune a 1 second page that runs thousands of times a day?

I've taken this information to produce a range of drillable reports, starting with a beautiful OracleJET chart. This starts by day - guess where the weekend is?

OracleJET Plots Pretty Popular Page Performance

Another favourite of mine plots performance over time, allowing me to target specific pages.

It all reminds me of the first time I remember implementing such a practice in Oracle Forms. We had a pre-form trigger that would log who opened which Form when.

Using this information, we could communicate with the business about how often how many people really are using those 'super important' Forms.

Now APEX gives me this information out-of-the-box, and also tells me how long it spent working on each result. Awesome!

It's proof.
Proof that a user has (or hasn't) opened a particular page.
Proof that the application 'isn't running slow', well, the database is certainly ok.
Proof that people are in fact using your application.

From 5.x it also logs the Request value, allowing more granularity than ever before. With this I can do things like measure how often a page 3 is opened from page 1 vs page 2. Where are users clicking to navigate?

So thank you, Oracle APEX team, for continuing to baking such a resourceful feature into the product.

I use it every day.

#ThanksODC

Further reading:
- Start reading related documentation here.
- Martin shows us how to get started with archiving this data. It is possible to change the interval between the background table switching.
- Jeff offers an example showing recent users of APEX.
- I removed outliers in this post.
- I pasted a few other SQL examples in this forum question.