# APEX_DEBUG_MESSAGE: The Debugging View Most Never Query

Every developer has been there. An error appears in production that you cannot reproduce locally. A page loads slowly but only sometimes. A session state value is wrong and you have no idea which process changed it or when.

The instinct is to add logging, scatter `DBMS_OUTPUT.PUT_LINE` calls through your PL/SQL, and try to catch the problem on the next run. It works, sometimes but it requires code changes, it only shows what you explicitly tell it to show, and it vanishes the moment the session ends.

But APEX has already been logging everything, on every page load, the entire time. It's sitting in a view called `APEX_DEBUG_MESSAGES` queryable, filterable, timestamped to microseconds, and persistent across sessions. 

This article is about that view: what it contains, how to read it, and how I built a dashboard on top of it to solve three specific debugging problems that came up repeatedly in my day-to-day work.

* * *

## What APEX\_DEBUG\_MESSAGE Actually Is

When debug mode is enabled, APEX logs every internal operation it performs during a page load, session initialisation, security checks, session state reads and writes, region renders, plugin loads, database commits and everything. Each operation becomes one row in `APEX_DEBUG_MESSAGE`.

The view has 14 columns. These are the ones that matter:

`PAGE_VIEW_ID` is the most important column to understand. Every page load or form submission gets a unique `PAGE_VIEW_ID`. Every log message from that single interaction shares the same value. It's the grouping key for everything. the receipt number for one page event.

`EXECUTION_TIME` is how long that specific operation took, in seconds. `0.0022` means 2.2 milliseconds. This is how you find slow operations without guessing.

`ELAPSED_TIME` is how long since the page load started. So `ELAPSED_TIME = 1.5` means that operation happened 1.5 seconds into the page load this is useful for understanding the sequence of events.

`MESSAGE` is where the detail lives. Operation names, parameter values, item names, error descriptions, everything APEX logs about that operation is in this field.

`MESSAGE_LEVEL` is a numeric severity indicator. `1` is an error, `4` is a warning, `6` is info, `9` is verbose. Most developers filter to level 1 when hunting errors and level 6 for general investigation.

`CALL_STACK` is the PL/SQL call stack at the time of an error. This is the column you wish you had been looking at every time you got a cryptic APEX error with no context.

* * *

## Enabling Debug Mode

Three ways to enable it:

**URL parameter — quickest for ad-hoc investigation:**

```plaintext
https://yourapp.com/ords/r/workspace/myapp/1?debug=YES
```

**From the APEX Builder — most convenient during development:** Run your app from the builder and click the bug icon in the developer toolbar at the bottom of the page.

**SQL - useful for targeted production investigation:**

```sql
APEX_DEBUG.ENABLE(p_level => APEX_DEBUG.C_LOG_LEVEL_INFO);
```

One important note: never leave debug mode permanently enabled in production. It writes a lot of rows and adds overhead. Enable it for a specific session, capture what you need, then disable it. Purge old data periodically:

```sql
APEX_DEBUG.PURGE_LOG(
    p_application_id => YOUR_APP_ID,
    p_older_than_days => 7
);
```

* * *

## The Three Problems

Here are the three debugging scenarios that come up repeatedly and that `APEX_DEBUG_MESSAGES` handles better than anything else.

* * *

### Problem 1 - Finding What Actually Happened in a Session

The raw view has thousands of rows across many sessions. The first challenge is getting a useful summary, one row per page interaction so you can quickly identify which sessions had errors and how long each one took.

This query is the foundation:

```sql
SELECT
    PAGE_VIEW_ID,
    APPLICATION_ID,
    PAGE_ID,
    APEX_USER,
    MIN(MESSAGE_TIMESTAMP) AS SESSION_START,
    ROUND(MAX(ELAPSED_TIME), 3) AS DURATION_SECONDS,
    COUNT(*) AS MESSAGE_COUNT,
    SUM(CASE WHEN MESSAGE_LEVEL = 1 THEN 1 ELSE 0 END) AS ERROR_COUNT,
    MAX(EXECUTION_TIME) AS SLOWEST_OPERATION_MS
FROM apex_debug_messages
WHERE APEX_USER NOT IN ('-', 'nobody')
AND APEX_USER IS NOT NULL
GROUP BY PAGE_VIEW_ID, APPLICATION_ID, PAGE_ID, APEX_USER
ORDER BY MIN(MESSAGE_TIMESTAMP) DESC
```

The `APEX_USER NOT IN ('-', 'nobody')` filter is worth noting. Pre-authentication requests the login page, static asset loads appear with those placeholder user values. Filtering them out keeps the list focused on real user sessions.

The `ERROR_COUNT` column immediately surfaces which sessions had problems. `DURATION_SECONDS` shows which page loads were slow. `SLOWEST_OPERATION_MS` tells you the worst individual operation in that session before you've even drilled in.

* * *

### Problem 2 - Slow Operations Across Sessions

Once you know a page is slow, the next question is: what specifically is slow? Not just in one session but across all sessions, consistently.

```sql
SELECT
    PAGE_VIEW_ID,
    APPLICATION_ID,
    PAGE_ID,
    MESSAGE_TIMESTAMP,
    ROUND(EXECUTION_TIME, 4) AS EXEC_MS,
    CASE
        WHEN MESSAGE LIKE 'Session State:%' THEN 'Session State'
        WHEN MESSAGE LIKE 'fetch_t_value%' THEN 'Item Fetch'
        WHEN MESSAGE LIKE 'do_commit%' THEN 'Commit'
        WHEN MESSAGE LIKE 'do_get_plugin%' THEN 'Plugin Load'
        WHEN MESSAGE LIKE 'render_page_slot%' THEN 'Page Render'
        WHEN MESSAGE LIKE 'render_region_slot%' THEN 'Region Render'
        WHEN MESSAGE LIKE 'Memory Usage%' THEN 'Memory Profile'
        WHEN MESSAGE LIKE 'Session Statistics%' THEN 'Session Stats'
        WHEN MESSAGE LIKE 'check session%' THEN 'Security Check'
        ELSE 'Internal'
    END AS OPERATION_TYPE,
    SUBSTR(MESSAGE, 1, 200) AS MESSAGE_SHORT,
    APEX_USER
FROM apex_debug_messages
WHERE APEX_USER NOT IN ('-', 'nobody')
AND EXECUTION_TIME > 0
ORDER BY EXECUTION_TIME DESC
FETCH FIRST 200 ROWS ONLY
```

Sorting by `EXECUTION_TIME` descending across all sessions immediately surfaces the worst operations. If the same operation type, say `Plugin Load` or `Region Render`, keeps appearing at the top across different sessions and pages, that's your bottleneck.

The `CASE` expression classifying operations by message pattern is important. The raw `MESSAGE` column is verbose, full parameter lists, internal function names, GUIDs. Classifying them into readable operation types makes the data scannable instead of overwhelming.

One thing that caught me here: `EXECUTION_TIME` values in the single-digit millisecond range are normal. The ones worth investigating are operations consistently above 100ms, and anything above 500ms on a regular page load is a problem worth diagnosing.

* * *

### Problem 3 - Tracing Session State Changes

This is the subtlest and most useful capability. When a session state value is wrong an item has an unexpected value, a page behaves differently than expected the question is: when was that item set, by what, and to what value?

APEX logs every session state read and write. The messages follow a consistent pattern:

```plaintext
Session State: fetch from database (exact)
... id=82181962404206834, name=P17_ITEM_NAME, type=VARCHAR2, value=***
```

The first message tells you APEX fetched state from the database. The `...` prefixed message that follows is the item detail- name, type, and value. The `value=***` means the item is protected and the value is masked, which is correct security behaviour for sensitive items.

This SQL extracts and parses those messages using `REGEXP_SUBSTR`:

```sql
SELECT
    MESSAGE_TIMESTAMP,
    ROUND(EXECUTION_TIME, 4) AS EXEC_MS,
    ROUND(ELAPSED_TIME, 4) AS ELAPSED_S,
    CASE
        WHEN MESSAGE LIKE 'Session State: fetch%' THEN 'State Fetch'
        WHEN MESSAGE LIKE 'Session State: save%' THEN 'State Save'
        WHEN MESSAGE LIKE 'Session State: clear%' THEN 'State Clear'
        WHEN MESSAGE LIKE '... id=%' THEN 'Item Detail'
        WHEN MESSAGE LIKE 'fetch_t_value%' THEN 'Item Fetch'
        ELSE 'Other'
    END AS STATE_OPERATION,
    CASE
        WHEN MESSAGE LIKE '... id=%'
        THEN REGEXP_SUBSTR(MESSAGE, 'name=([^,]+)', 1, 1, NULL, 1)
        WHEN MESSAGE LIKE 'fetch_t_value%'
        THEN COALESCE(
            NULLIF(REGEXP_SUBSTR(MESSAGE, 'p_item\.name=>([^,]+)', 1, 1, NULL, 1), ''),
            '(id=' || REGEXP_SUBSTR(MESSAGE, 'p_item_id=>([^,]+)', 1, 1, NULL, 1) || ')'
        )
        ELSE NULL
    END AS ITEM_NAME,
    CASE
        WHEN MESSAGE LIKE '... id=%'
        THEN REGEXP_SUBSTR(MESSAGE, 'type=([^,]+)', 1, 1, NULL, 1)
        ELSE NULL
    END AS ITEM_TYPE,
    CASE
        WHEN MESSAGE LIKE '... id=%'
        THEN REGEXP_SUBSTR(MESSAGE, 'value=(.+)$', 1, 1, NULL, 1)
        ELSE NULL
    END AS ITEM_VALUE,
    MESSAGE AS FULL_MESSAGE
FROM apex_debug_messages
WHERE PAGE_VIEW_ID = :YOUR_PAGE_VIEW_ID
AND (
    MESSAGE LIKE 'Session State:%'
    OR MESSAGE LIKE '... id=%'
    OR MESSAGE LIKE 'fetch_t_value%'
)
ORDER BY MESSAGE_TIMESTAMP ASC
```

The `COALESCE` on the `fetch_t_value` item name handles a real data gap, sometimes APEX logs item fetches by internal ID rather than name. When the name is empty, falling back to `(id=xxx)` keeps the output readable instead of showing a blank cell.

What this query gives you is a chronological trace of every item APEX touched during a page load what was fetched, in what order, with what value. If an item has the wrong value, you can see exactly at what point in the page lifecycle it was loaded and what it contained.

* * *

## The Dashboard

I built a four-page APEX app on top of these queries. Page 1 shows the session summary, one row per page view, with error count and duration highlighted. Page 2 drills into a single session showing all messages with level colour coding. Page 3 is the performance view sorted by execution time with operation type classification. Page 4 is the session state trace using the parsed output above.

The app itself is straightforward - Interactive Reports with some JavaScript for colour coding, modal dialogs for detail pages. The complexity is in the queries and in knowing which columns to focus on.

The most useful addition was colour coding `MESSAGE_LEVEL` in the session detail view. Errors in red, warnings in orange, info in blue, verbose in grey. In a session with 160 messages, errors jump out immediately without scrolling through everything.

For the performance view, highlighting `EXECUTION_TIME` values above 100ms in orange and above 500ms in red follows the same principle the problem rows identify themselves without manual scanning.

I will not include a step by step tutorial on how to build the same application, You can use the queries i have included here and customize and rebuild it in a mannar that is more suited for your requirments.

* * *

## What This Replaces

The comparison with `DBMS_OUTPUT` is straightforward:

`DBMS_OUTPUT` shows you what you explicitly tell it to show. You have to know in advance what to log, add the code, deploy it, reproduce the problem, and then read the output in a tool that doesn't persist it.

`APEX_DEBUG_MESSAGES` shows you everything APEX does including the things you didn't know to look for. You enable debug mode, reproduce the problem once, and then query the results at your own pace. The data is there whether you thought to capture it or not.

The practical difference: when something breaks in production, you can enable debug mode for your own session, reproduce the issue, disable debug mode, and then query `APEX_DEBUG_MESSAGES` to read exactly what happened without touching any application code and without affecting other users.

* * *

## Quick Reference

```sql
-- Recent sessions with error count
SELECT PAGE_VIEW_ID, APPLICATION_ID, PAGE_ID, APEX_USER,
       MIN(MESSAGE_TIMESTAMP) AS STARTED,
       ROUND(MAX(ELAPSED_TIME), 3) AS DURATION_S,
       SUM(CASE WHEN MESSAGE_LEVEL = 1 THEN 1 ELSE 0 END) AS ERRORS
FROM apex_debug_messages
WHERE APEX_USER NOT IN ('-', 'nobody')
GROUP BY PAGE_VIEW_ID, APPLICATION_ID, PAGE_ID, APEX_USER
ORDER BY MIN(MESSAGE_TIMESTAMP) DESC
FETCH FIRST 20 ROWS ONLY;

-- Slowest operations across all sessions
SELECT PAGE_VIEW_ID, PAGE_ID, APEX_USER,
       ROUND(EXECUTION_TIME, 4) AS EXEC_MS,
       SUBSTR(MESSAGE, 1, 100) AS MESSAGE
FROM apex_debug_messages
WHERE APEX_USER NOT IN ('-', 'nobody')
AND EXECUTION_TIME > 0.1
ORDER BY EXECUTION_TIME DESC
FETCH FIRST 50 ROWS ONLY;

-- Errors with call stack
SELECT MESSAGE_TIMESTAMP, PAGE_ID, APEX_USER,
       MESSAGE, CALL_STACK
FROM apex_debug_messages
WHERE MESSAGE_LEVEL = 1
AND APPLICATION_ID = YOUR_APP_ID
ORDER BY MESSAGE_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;

-- Session state trace for a specific page view
SELECT MESSAGE_TIMESTAMP,
       REGEXP_SUBSTR(MESSAGE, 'name=([^,]+)', 1, 1, NULL, 1) AS ITEM_NAME,
       REGEXP_SUBSTR(MESSAGE, 'value=(.+)$', 1, 1, NULL, 1) AS ITEM_VALUE
FROM apex_debug_messages
WHERE PAGE_VIEW_ID = YOUR_PAGE_VIEW_ID
AND MESSAGE LIKE '... id=%'
ORDER BY MESSAGE_TIMESTAMP ASC;
```