22.4 About Utilizing Database Reporting
Improve application performance by utilizing database reporting.
Oracle APEX establishes a physical connection from the database pool as APEX_PUBLIC_USER, which calls the APEX engine. The APEX engine utilizes SQL.DBMS_SYS_SQL to parse SQL as another user, the parsing schema. Database V$SESSION records contain useful information for each SQL executed including APEX specific information for the client information, client identifier, and module. You can use this information to identify the associated SQL when you generate Oracle Trace files and use TKPROF to analyze them. You can also add &p_trace=YES to the end of the APEX URL or use ALTER SESSION.
Automatic Workload Repository (AWR) reports collect performance statistics every hour, by default. Active Session History (ASH) reports are a system-wide record of database activity. You can use these reports to identify resource intensive SQL statements. For PL/SQL packages, procedures and functions that in turn call other PL/SQL programs, you can use PL/SQL hierarchical profiler in Oracle Database 11g. These subprograms account for execution times separately.
APEX also includes views which catalog everything related to APEX. You can use these views to access information from APEX using tools such as SQL Developer, SQL*Plus and SQL Commands (Oracle APEX SQL Workshop). If you are granted the APEX_ADMINISTRATOR_READ_ROLE or APEX_ADMINISTRATOR_ROLE then you can query across the entire instance, rather than just the workspace your schema user is associated with. You should grant APEX_ADMINISTRATOR_READ_ROLE to monitoring users, while APEX_ADMINISTRATOR_ROLE should be used for instance administrators, who manage instance parameters, workspaces, and so on.