How to get traces in apex automatically (in all the pages)

We know that adding ?p_trace=YES to the APEX URL generates SQL Trace information for the page for the current session.. This is very useful when the problem is located in few places. However, sometimes the performance problem seems to be random, it happens in different website pages or it may be difficult to reproduce. For these cases, the above solution is not valid.

What we need is to generate traces automatically for the entire website (like adding ?p_trace=YES in all the url pages but automatically).

Our solution is to create a new process (on load - before header) in the home of our website. This process executes:

execute immediate 'alter session set sql_trace=true';

To create the process the steps are:

1. In apex, go to the Edit page of the page that will execute the process
2. In the Processes section, click the Create new Process icon.
3. Select PL/SQL and click next
4. Type the name of the process and select "On Load - Before Header" in the Point LOV and click next.
5. Type: execute immediate 'alter session set sql_trace=true';
6. Click Create Process

Once it is done, every time the user loads a web page the trace is generated automatically. Not only in the home but in all the pages that the user visits. When the problem is reproduced we are sure that the trace has been generated in the directory pointed by the user_demp_dest parameter. Remember to delete the process when you solve the problem.

We need to grant to the parsing schema of our application the privilege to alter the session with the following command:

grant alter session to parsing_schema

You can find the parsing schema your application is using in apex:

Home > Application Builder > Application number > Shared Components > Edit Security Attributes