22.4 About Utilizing Database Reporting

Improve application performance by utilizing database reporting.

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_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.