Error!?! What's going in APEX? The easiest way to Debug and Trace an Oracle APEX session

There are some days you just can't explain the behaviour of the APEX Builder or your own APEX Application. Or you recognize this sentence of your end-user? "Hey, it doesn't work..."

In Oracle APEX 5.1 and 18.1, here's how you start to see in the land of the blinds :)

Logged in as a developer in APEX, go to Monitor Activity:

 From there go to Active Sessions:

You will see all active sessions at that moment. Looking at the Session Id or Owner (User) you can identify the session easily:

Clicking on the session id shows the details: which page views have been done, which calls, the session state information and the browser they are using.

But even more interesting, you can set the Debug Level for that session :)

When the user requests a new page or action, you see a Debug ID of that request.

Clicking on the Debug ID, you see straight away all the debug info and hopefully it gives you more insight why something is not behaving as expected.

A real use case: custom APEX app

I had a real strange issue which I couldn't explain at first... an app that was running for several years suddenly didn't show info in a classic report, it got "no data found". When logging out and back in, it would show the data in the report just fine. The user said it was not consistent, sometimes it works, sometimes not... even worse, I couldn't reproduce the issue. So I told her to call me whenever it happened again.
One day she calls, so I followed the above to set debug on for her session and then I saw it... the issue was due to pagination. In a previous record she had paginated to the "second page", but for the current record there was no "second page". With the debug information I could see exactly why it was behaving like that... APEX rewrote the query rows > :first_row, which was set to 16, but for that specific record there were not more than 16 records, so it would show no data found.
Once I figured that out, I could quickly fix the issue by Resetting Pagination on opening of the page.

Debug Levels

You can set different Debug Levels. Level 9 (= APEX Trace) gives you most info whereas debug level 1, only shows the errors, but not much other info. I typically go with APEX Trace (level 9).

The different debug levels with the description:

Trace Mode

In case you want to go a step futher you can also set Trace Mode to SQL Trace.

This will do behind the scenes: alter session set events '10046 trace name context forever, level 12’;
To find out where the trace file is stored, go to SQL Workshop > SQL Scripts and run

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

It will return the path of the trace file. When looking into that directory you want to search for the filename which contains the APEX session id (2644211946422) and time you ran the trace.

In Oracle SQL Developer you can then look at those trace files a bit more easily. You can also use TKPROF or other tools.

When I really have performance issues and I need to investigate further, I like to use Method R Workbench. The Profiler interpretes the trace file(s) and gives an explanation what's going on.

And with the different tools on the left, you can drill down in the files.

I'm definitely not a specialist in reading those trace files, but the above tools really help me understanding them. When I'm really stuck I contact Cary Millsap - or I call him Mr Trace - he's the father of those tools and knows trace files inside out :)

A second use case: APEX Builder

I was testing our APEX Office Print plugin in APEX 18.1 and for some reason APEX was behaving differently than earlier versions, but I didn't understand why. I followed the above method again to turn debug and trace on for my own session - so even when you are in the APEX Builder you can see what APEX is doing behind the scenes.

Debugging and Tracing made easy

I hope by this post you see the light when you are in the dark. Let the force be with you :)