Monitoring your Oracle APEX environment; when is a good time to do an upgrade?

Yesterday Oracle released Oracle APEX 19.1. We typically upgrade our environment within a week.

The question is always, when is a good time to upgrade?

As we are hosting multiple applications and sites in our environment, we want to find a spot with minimal impact to our customers. For some applications which are only accessed by some people in Belgium, it's very easy to schedule an upgrade at night as the chances are not high people accessing the app. But in the case of APEX Office Print, we have customers all over the world in different timezones. So they might want to look at their dashboard or download the latest version at any moment in time.

For our AOP service itself, we have an internal policy we don't allow downtime. We load balance, so even when performing upgrades of AOP, there will always be a server available to handle the requests. For our Oracle database and APEX environment, we are fine with a few minutes downtime while upgrading the server.

In Oracle APEX itself, when you go to Monitor Activity - Page Views by User by Hour you get a graphical overview when your applications are being used:

This screen works well for a given workspace. If you want to look cross workspaces, for your entire APEX instance, you could build something yourself similar to the above. An overview like that would give you an idea based on historical usage which hours have the least amount of impact.

Now that you found the window, you still want to check if somebody is using our APEX apps.

So before I start an upgrade, I always check the last activity in our APEX apps.
The script I use to monitor the activity in our entire APEX environment - as SYS user run in SQL>

SELECT
    workspace_name,
    apex_session_id,
    user_name,
    remote_addr,
    TO_CHAR(session_created, 'DD-MON-YYYY HH24:MI') AS session_created,
    TO_CHAR(session_idle_timeout_on, 'DD-MON-YYYY HH24:MI') AS session_idle_timeout_on,
    TO_CHAR(session_idle_timeout_on-(session_max_idle_sec/24/60/60), 'DD-MON-YYYY HH24:MI') AS last_activity,
    TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI') AS time_now,
    round((sysdate-(session_idle_timeout_on-(session_max_idle_sec/24/60/60)))*24*60) as minutes_ago
 FROM apex_workspace_sessions
WHERE user_name NOT IN ('APEX_PUBLIC_USER','nobody')
ORDER BY minutes_ago, workspace_name, session_idle_timeout_on DESC;

This gives me the following overview:

So I see how many minutes ago (last column) which APEX workspace was used by which user. I could even follow the APEX session to find out more about the APP etc. but that is not really relevant for me. In the script, I only check for logged in users.

In case I also want to see for direct connections to the database, or I want more real-time info from the APEX session, I run the following script to check the session in the Oracle Database:

SELECT
    sid,
    serial#,
    username,
    osuser,
    machine,
    program,
    --sql_id, sql_exec_start, event,
    logon_time,    
    client_info         AS workspace_id_auth_user,
    module              AS db_schema_apex_app_id_page_id,
    action              AS action,
    client_identifier   AS auth_user_apex_session
 FROM gv$session
WHERE type = 'USER'
  AND service_name = 'apex_pdb';

This gives me the following overview:

In the above screen you see I'm connected to the database with SQL Developer. The other sessions are coming from our connection pool. What is interesting is that APEX is instrumented to populate session info in the database like module, client_info, and client_identifier. This is important as APEX/ORDS is working with a connection pool so it would be hard to see what APEX session corresponded to which database session in case they didn't.

The above helps me to find a good time to perform upgrades, without impacting too many people.
So I thought to share my scripts as it might help you too.