Oracle Application Express establishes a physical connection from the database pool as APEX_PUBLIC_USER
, which calls the Application Express engine. The Application Express 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 Oracle Application Express 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 Application Express 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.
Oracle Application Express also includes views which catalog everything related to Oracle Application Express. You can use these views to access information from Oracle Application Express using tools such as SQL Developer, SQL*Plus and SQL Commands (Oracle Application Express SQL Workshop). If you are granted the APEX_ADMINISTRATOR_ROLE
then you can query across the entire instance, rather than just the workspace your schema user is associated with.
See Also:
"Enabling SQL Tracing and Using TKPROF", Oracle Database Performance Tuning Guide for information on AWR and ASH data, and Oracle Application Express SQL Workshop Guide