When installing Oracle Application Express there are a number of key architecture and management areas to understand.
Oracle Application Express is a no-cost option of the Oracle Database which is fully supported as part of your Oracle Database maintenance agreement. It can be installed into any edition of the Oracle Database 10.2.0.4 and above, which includes Enterprise Edition, Standard Edition, Standard Edition One, and Express Edition (XE). Starting with Oracle Database 11gR1, Application Express is installed as part of the standard Oracle Database. Oracle strongly recommends that you upgrade to the latest version of Application Express available on the Oracle Technology Network (OTN). Oracle Application Express cannot be installed into any other relational database.
Oracle Application Express can be installed into an Oracle Real Application Clusters (RAC) environment. If you intend to install Application Express into a RAC environment then you should review the associated Application Express and RAC White Paper and also run the script file
apxpart.sql, provided as part of the installation files under the utilities directory.
The installation of Application Express creates a separate schema (APEX_040200 for APEX 4.2) to house the programs that make up the Application Express engine and related metadata tables. Oracle recommends you create tablespaces specifically for the Application Express engine and Application Express files to enable better management and monitoring For example, Tablespace APEX_TS_040200 would contain the Application Express engine and APEX_TS_FILES for the Application Express files, but the names are arbitrary. Your initial installation step, as outlined in the Oracle Application Express Installation Guide, Downloading from Oracle Technology Network, would then be
@apexins APEX_TS_040200 APEX_TS_FILES TEMP /i/.
As a best practice, install Oracle Application Express into the database(s) which have the database objects you wish to use in your applications. You can also utilize Oracle database links and integrate your applications with Web Services.
Oracle Application Express utilizes a simple architecture where pages are dynamically generated using metadata stored within the Oracle Database. There is no code generation or file based compilation. Once fully installed, the Uniform Resource Locator (URL) will be defined for both developers and end users to access Application Express. Users require only a Web browser and the required URL. No additional client software is required.
The Web Listener functions as a communications broker between the Web browser and the Oracle Application Express objects in the Oracle database by mapping browser requests into database stored procedure calls. You have a choice of three Web Listener choices.
The Web listener options include:
Oracle REST Data Services (formerly APEX Listener) - The Oracle REST Data Services (ORDS) is Java based, can be installed into any J2EE compliant Web Server, and is the preferred option for use with Oracle Application Express. It is a free tool that is fully supported against Oracle WebLogic Server, Oracle Glassfish Server, and Apache Tomcat. Oracle REST Data Services is part of the reference architecture used to operate the Oracle Database Cloud Service.
Note: There are licensing costs associated with Oracle WebLogic Server and Oracle Glassfish Enterprise edition. Oracle Glassfish Community edition is a no cost option.
Embedded PL/SQL Gateway - The Embedded PL/SQL Gateway (EPG) runs in the Oracle XML DB Protocal Server within the Oracle database and includes the core features of mod_plsql.
Licensing for the Embedded PL/SQL Gateway is included with the Oracle Database license. Oracle HTTP Server - The Oracle HTTP Server (Apache) with mod_plsql plugin can be placed on the same physical machine as the database, or on a separate physical machine.
Note: If installed on the same physical machine as the database then the Oracle HTTP Server is included as part of the limited-use license included with the Oracle Database license - otherwise the Oracle HTTP Server will need to be licensed.
Warning: mod_plsql is deprecated as of Oracle HTTP Server 12c (12.1.3). For more information, please see My Oracle Support Note 1576588.1. Oracle recommends using Oracle REST Data Services instead..
|Capabilities||Oracle REST Data Services||Embedded PL/SQL Gateway||Oracle HTTP Server|
|Image location||File system||Within database||File system|
|Configuration options||GUI Interface (Release > =2.0); Administration pages||Database initialization parameters||Database Access Descriptor (DAD)|
|Connection Pool settings||JDBC connection parameters||SHARED_SERVERS; MAX_SHARED_SERVERS||Min/MaxSpareServers; MaxClients|
|Emit RESTful Web Services||Yes (Release > =2.0)||No||No|
|Support multi-databases||Yes, including RAC||No||Yes, including RAC|
|Virus scan files||Yes, with integration of ICAP server||No||No|
|PDF printing||Yes, included FOP support (Release > =2.0)||No||No|
|Environment recommendations||All||Development only||All|
Oracle Application Express enables a single database to host large numbers of applications and users. Developers work in a dedicated work area called a workspace to develop applications against one or more schemas. This flexible architecture enables a single database instance to act as a "Platform as a Service" (PaaS), providing a hosted Intranet capability within the organization, or hosted Internet service. It is common for workspaces to be defined for separate departments within an organization so that each department can develop their own database objects and applications independently.
Individuals who access the Oracle Application Express environment can have different roles, responsibilities and rights.
There are four major roles associated with Application Express:
Instance Administrator - Responsible for instance configuration and monitoring, including workspace provisioning, feature configuration, security, and instance settings.
Workspace Administrator - Responsible for requesting, monitoring, and maintaining developers within a workspace. Generally also have developer responsibilities.
Developer - Develop applications and required database objects, if objects not already available.
End User - Defined users for applications that utilize Application Express User authentication scheme.
In general you only need to define a few instance administrators to work with database administrators and system architects to correctly configure and manage the Oracle Application Express environment. For each workspace there must be at least one workspace administrator, and any number of developers. Workspace administrators are responsible for maintaining the developer credentials. Defining end users within the Application Builder is only required if applications are using the Application Express User authentication scheme. If the applications are public, requiring no authentication, or other authentication schemes such as SSO, LDAP, custom, etc. are utilized by the applications then you will not need to define end users.
As with any software development lifecycle, Oracle strongly recommends that you have different environments for development, test / QA, and production. As a best practice, install "Runtime Only" Application Express within your test and production environments. This removes the Application Builder and SQL Workshop components from your test and production environments and forces developers to make all changes directly in your development environment. Developers should check all applications, and associated files, into a source control system, and have Database Administrators (DBAs) check-out and run scripts directly from source control into test and production.
When Oracle Application Express is installed, four database jobs are created. In order for Application Express to operate correctly, these jobs must run regularly.
The installed database jobs include:
ORACLE_APEX_DAILY_MAINTENANCE - Runs at 0100 system time daily; Archives activity logs, purges workspaces and deletes expired files.
ORACLE_APEX_PURGE_SESSIONS - Runs hourly; Deletes session information from APEX tables for sessions older than 12 hours.
ORACLE_APEX_MAIL_QUEUE - Runs every 5 minutes; Sends messages in the the e-mail queue.
ORACLE_APEX_WS_NOTIFICATIONS - Runs every 30 minutes; Sends Websheet notifications.
Standard Oracle backup and recovery techniques should be used to backup workspace schemas and the APEX engine (APEX_040200 for APEX 4.2). However, restoring the Application Express engine restores metadata for all applications in all workspaces, overwriting any application updates made since the backup. If it is essential to restore specific applications from a backup it is recommended that the APEX engine schema is restored into a separate Oracle Database and then export the application from this installation. Oracle Database flashback capabilities can be used to export an application "As Of" which will potentially allow developers to create an export from some period earlier, however, flashback only extends a certain amount of time depending on database parameter settings. Developers should perform their own regular backups of both their applications and other files until the code is complete and checked into the source control system.
If you already have Oracle Application Express installed and wish to upgrade the version of Application Express, you should consider the following:
Oracle strongly recommends that you regularly upgrade to the latest version of Application Express available from the Downloads page. The majority of bugs reported against Application Express are listed on the Known Issues page for the related release. Where possible a patch set is made available for a bug to enable those running the latest version of Application Express to update their installation. In between major versions there are often point releases, for example Application Express 4.2.1, which correct bugs and incorporate all of the patch sets released to that point in time, but do not introduce new functionality. Patch sets and point releases are available for download from My Oracle Support.
Note: If upgrading from an earlier release of Application Express, for example from Application Express 4.1 to Application Express 4.2.1, then you can simply download the latest files from the Download page. However, if you are upgrading from Application Express 4.2 to Application Express 4.2.1 then you must download the point release from My Oracle Support.
When you install Oracle Database 11gR2, Oracle Application Express 3.2.1 is included by default. Similarly, Oracle Database 11gR1 came with Application Express 3.0.2. In all scenarios the latest release on the Downloads page should be installed as soon as possible as it includes additional features, plus numerous functional and performance improvements, combined with the latest bug fixes.
To determine which version of Application Express you are currently running, log in to your Application Builder environment. On the home page, the current release will be shown in the bottom right corner. Only one version of Application Express can be configured into a single Oracle Database server. If you wish to run different versions of Application Express you will need to run each in a separate Oracle Database. You can import an application exported from a prior release, however, if you export an application from Application Express 4.2.x then you will not be able to import it into any prior release.
The Application Express engine consists of a large number of Oracle tables and thousands of lines of PL/SQL programs residing within their own version specific schema. Application Express 4.2.x is installed into the APEX_040200 schema, Application Express 4.1.x is installed into the APEX_040100 schema. For Application Express 3.1.x and prior releases the schema is named FLOWS_xxxxxx.
You utilize the same installation procedure and files, downloaded from OTN, whether inserting Application Express for the first time or updating a previous release. The installation script checks for the latest existing Application Express schema and automatically copies the metadata, for the instance, workspaces, and applications, from the previous schema into the APEX_040200 schema. The original schema associated with the previous release is left completely unaltered. Following best practices, Oracle recommends that you create new tablespaces for the new release of Application Express and follow the appropriate installation instructions as outlined in the documentation.
When a new release of Application Express is installed all of the existing applications built into the previous release are automatically updated to run in the latest release. However, the intent of the Application Express development team is to not change the user interface as a result of an upgrade. As a consequence, application components that may alter the user interface are left untouched by the upgrade. It is the responsibility of the developer to manually review such components and update them or leave them running as before. Examples include new features added to interactive reports, charts (charts that utilize 'Custom XML' must be updated manually otherwise the custom XML will be lost), certain validations, and so on. In order for you to review the impact of these component upgrades, go to the Application Builder for each application upgraded and select
Upgrade Application and then review each upgrade type. You can easily upgrade the non-upgraded component for one, all, or a selection of the instances of that component from these pages. Please remember that updating the user interface of an application may require changes to existing application documentation / training guides and may also require that end users are retrained if the pages are significantly different.
The Oracle Application Express Advisor (Advisor) enables you to check the integrity and quality of your Oracle Application Express application. Advisor functions like a compiler or LINT and flags suspicious behavior or errors. Running Advisor checks the integrity of your application based on the underlying metadata. Each subsequent release of Application Express further tightens the rules engine, such as invalid developer code that was ignored previously may now produce an error. The Advisor will identify these issues as errors, along with numerous other warnings, to improve the quality of your applications. Therefore, Oracle strongly recommends that you run the
Advisor for every upgraded application.
Within each application, you can also specify the Compatibility Mode in the Application Definition, which changes certain runtime behaviors. The options include Pre 4.1, 4.1 and 4.2. Read the associated item help to determine the appropriate Compatibility Mode and the implications for upgraded applications.
While regression testing of upgraded applications is imperative to minimize risk of disrupting the end-users, it is important that such testing is not drawn out for an extended period. Generally, the development environment is upgraded first, allowing developers to review the applications and make initial updates as necessary or desired. Then the QA / Test environment is updated and the upgraded applications from development are built into this environment. Until such time as the Production environment is also updated it is often difficult or dangerous to make changes to production applications to resolve a bug or develop new functionality.
Following the successful upgrade of all of the environments to the latest release of Application Express, you should clean-up the environments. Once you start developing with the newer release, the Application Express schema associated with the prior release can be deleted. If you installed the prior release into a separate tablespace, you can simply drop the specific tablespace. Oracle recommends leaving the old Application Express schema(s) for a few weeks and then removing them from the Development, QA / Test, and Production environments. This clean-up releases disk space and ensures that no one accesses an outdated schema using tools such as SQL Developer or SQL*Plus.
One of the major benefits of Application Express automatically creating a new schema for each major release of Application Express is that reverting back to a prior release is a relatively simple and low risk option. If you revert back to a prior release, any modifications made in the Application Express 4.2 instance will be lost. The main task is to switch the public synonyms and grants to point at the previous schema instead of the APEX _040200 schema. The exact steps are outlined in the Oracle Application Express Installation Guide, Appendix A: Oracle Application Express Troubleshooting section. It is also important to remove the APEX_040200 schema once tests running on the prior release are successful.
You should review the Oracle Application Express Builder User's Guide, Deploying an Application documentation. The following sections cover additional considerations when deploying Oracle Application Express applications:
When developing with Oracle Application Express you should follow standard system development lifecycle practices in so far as having different environments for development, testing and production. Developers should only be allowed to make changes to applications and related database objects in the development environment. To further enforce this policy it is recommended that you install "Runtime Only" Application Express in the test and production environments. This will prohibit developers from accessing the Application Builder and SQL Workshop in these environments. Database Administrators (DBAs) should be the only ones with permission to update the test and production environments.
Often developers will need to create, modify / replace, or delete various database objects such as tables, packages, grants, etc. as part of a unit of work. Sometimes data will also need to be inserted, updated or deleted from one or more tables. Developers should create data definition language (DDL) and data manipulation language (DML) scripts as required so that any changes to database objects or data is reproducible. These scripts should be tested and checked into source control. Alternatively, developers can utilize Supporting Objects to define installation, upgrade and removal scripts. Supporting Objects is most valuable when developing applications that will be installed by third parties, such as the Packaged Applications. For other development activities having the DDL and DML scripts outside of specific applications, and not using Supporting Objects, provides more flexibility and control for the DBAs.
Instead of exporting complete applications you can also choose to export specific components, such as a page. This also creates a SQL script file. There are limitations with importing components into a different environment from which it was exported. As a best practice, it is better to export complete applications rather than components.
If you wish to export an application and there are components, such as pages, that are not yet ready for testing then you can utilize "Build Options" to include or exclude various application components. Review Oracle Application Express Builder User's Guide, Using Build Options to Control Configuration to learn about the mechanics of using build options to customize an application export. The Application Express development team uses this capability extensively, especially when building Early Adopter releases where certain components may not be functionally complete.
The Application Express installation includes two Java programs that enable you to perform either complete application exports or component exports. More details on these Java programs are available in the utilities / readme.txt file included within the installation. These programs are best utilized as part of a CRON job, or similar, that runs nightly and places the exports directly into a source control system. The exported files should be used as backups in case of a database corruption, etc. It is still best to manually export applications and check them into source control for deploying the applications.
Oracle Application Express 4.2 introduced the ability to define multiple user interfaces for desktop and mobile devices. The sections below provide an overview of different design considerations:
The optimal user interface design principles have continually changed and evolved as new architectures are introduced. Old mainframe applications screen design was based on keyboard input only and the order of items on a page was critically important. However, with client server applications user interface design generally placed multiple items on a single line and everything had to fit into a single screen with no scrolling. With Web applications the principle design changed again to very few items on a single line, instead using vertical scrolling as required. Now with mobile and tablet applications the user interface principles have changed again to display minimal information with very easy to navigate screens that utilize touch instead of pointing devices.
Application Express 4.2 introduced the ability to declaratively define mobile applications using jQuery Mobile. The Application Builder has been enhanced to support multiple user interfaces, both desktop and mobile, within a single application. Therefore, developers can choose to build an application only for the desktop, only for mobile, or for both. Each user interface defined for an application has its own theme, login page, home page, and global page (formerly Page 0). When defining a page within an application, you must specify which user interface is associated with that page. Based on the user interface selected the available page and region types varies, and the options available through the create page and region wizards varies accordingly. For example, maps, data loading and other page types and interactive report regions are only supported on desktop pages. Similarly on mobile pages the most common construct is list and form, instead of report and form.
Mobile applications are those designed to run specifically on smartphone devices. To facilitate this applications need to be built with extremely minimal, semantic HTML that is optimized for mobile connections. Oracle Application Express 4.2, and above, incorporates jQuery Mobile to allow developers to rapidly build mobile applications. jQuery Mobile based web pages use a framework that was specifically built for mobile devices. jQuery Mobile is a lightweight framework that enables you to create compact and minimal web sites that include only a few images and CSS files. jQuery Mobile is also aware of and able to respond to mobile device specific events, such as orientation change and touch events. The look and feel of jQuery Mobile based applications can be easily modified via CSS using tools such as theme-roller, thanks to it's relatively static HTML structure.
Mobile applications developed with Application Express are browser-based applications that run inside the browser on the mobile device. Therefore, these applications must have a connection in order to communicate with the Oracle Database and cannot operate in a disconnected environment. Using jQuery Mobile, these applications can run on any mobile operating system including iOS, Android, Blackberry, and Windows. Mobile devices that have HTML5 capabilities will be able to utilize all of the capabilities that can be built into the applications including HTML5 date-pickers, sub-types that display different keypads based on field definition, etc. Older devices will still render the application but with less advanced features. The major advantage of developing browser-based applications is that you only need to develop them once for the majority of mobile devices. However, one major limitation is accessing on-device features such as contact lists. This limitation can be alleviated by integrating with solutions such as PhoneGap that allow for creating hybrid solutions, which use a native application wrapper to display the web app.
One of the recent user interface design principles being adopted is responsive design. Responsive design is a way to design websites so that the layout fits the available space on differently sized desktop or laptop browsers as well as tablets and smartphones. On larger screens, the user get's the full experience. On smartphones and tablets, the layout adopts itself to the size of the device's screen. This is done by having certain elements shift position, re-size or become hidden entirely. The goal is to make all essential content available in a user-friendly and pleasing way on any device. Oracle Application Express 5.0 introduces the Universal Theme which is truely responsive 'out-of-the-box' theme, without requiring the developer to design pages in any special way to be responsive. This is a large improvement over Oracle Application Express 4.2 Theme 25. Theme 25 provided the building blocks and templates that allow for achieving a responsive layout. However, it was up to the developer to use those templates and to arrange the page content in such a way that the end result is truly responsive. Also note that converting an existing application to Theme 25 requires you to review the pages of the application to ensure the appropriate template and layout are defined.
Review how Demand-Analysis have utilized Responsive Design to deliver their commercial application on multiple devices.
The main deciding factor should be the type of site or application that is being developed and the content that is being presented. A marketing site for instance, such as a company's homes page, catalogs, libraries, and wikis are well suited for responsive design. Productivity applications, such as customer management applications, business intelligence tools, and inventory applications are good candidates for a jQuery Mobile based user interface. These types of applications require quick response time, easy to use data entry forms, and would benefit from utilizing the device's native controls. Customers who are used to quickly flipping through data using touch controls in native applications will get much the same experience with their jQuery Mobile applications, such as paginating through content using swipe, orientation change to make better use of space and context sensitive soft-keypads. Ideally of course, these type of applications would still have their desktop-orientated pages be somewhat responsive so that they work well on desktops as well as tablets.
There are three major considerations in respect of security:
Instance administrators are responsible for setting various instance wide options to ensure the appropriate level of security for both developing and deployed applications. You should also review the Oracle Application Express Application Builder User's Guide, Understanding Administrator Security Best Practices. To further harden your environments instance administrators should review the following settings either from Instance Administration or by running the appropriate APIs:
Enable SQL and PL/SQL Access In Websheets - Disable this option when you wish to prevent Websheet users from accessing underlying database objects using the SQL tag, creating SQL reports or creating PL/SQL sections.
Enable RESTful Services - Disable this option if you do not want to allow developers to create and edit RESTFul Web Services mapped to SQL and PL/SQL in conjunction with the APEX Listener Release 2.0 and above.
Disable Workspace Login - Set to Yes in test and production environments when "Runtime Only" not installed.
Allow Public File Upload - Set to No to prevent public (unauthenticated) users from uploading files.
Restrict Access by IP Address - Enter a comma-delimited list of allowable IP address ranges.
Instance Proxy - Specify the proxy server address to be used for all outbound traffic.
Require HTTPS / Outbound HTTPS - Providing HTTPS is enabled on your server, switch both to Yes to force all authenticated pages within the Application Builder to require HTTPS which encrypts network communications.
Allow RESTful Access - Disable this option if you don't want developers exposing report regions as RESTful services.
Session Timeout - Control the maximum session length and idle time as appropriate such that users must re-authenticate after a certain period. These settings can be overwritten by application-level settings.
General Login Control - Set delays after failed login attempts to prevent login bots, and specify an inbound proxy server.
Workspace Password Policy - Set various parameters to adjust how strong the passwords for all workspace users, including Application Express end users, needs to be.
Self Service / Email Provisioning - Specify the amount of automation when developers request new workspaces.
Storage - Define if workspaces can utilize existing schemas, and specify tablespaces for new schemas to be encrypted.
Email - Define the email SMTP settings and set the "Use SSL/TLS" to use a secure connection for Oracle Database 11gR2 and above.
Wallet - Define the wallet, a password-protected container used to store authentication and signing credentials, used for all HTTPS requests.
Workspace Purge Settings
For large installations where there are regular requests for new service and/or limited database resources, instance administrators can define purge settings which will automatically send emails to workspace administrators whenever their workspace has been inactive for a specified period. If one of the workspace administrators does not respond to the emails saying they want the workspace preserved then it will be purged. Purging a workspace will remove old applications and release the space they utilized.
Manage Workspace to Schema Assignment - Define the many-to-many associations between workspaces and schemas as appropriate.
Manage Developers and Users - Use this to define what schemas a specific user has access to and also whether they are a workspace administrator, developer or an end user. You can also limit developer access to specific Application Builder components and lock a specific account.
Manage Component Availability - Change access to Application Builder, SQL Workshop, and PL/SQL editing which will limit developer access across a workspace. For example, if you wanted users to be able to build database components, run SQL statements, etc but not build applications, you could define a workspace with rights to a specific schema and then configure the users as developers using this feature.
Workspace administrators are responsible for setting various workspace options to ensure the appropriate level of security within their workspace. From Administration > Manage Service > Set Workspace Preferences administrators can set the Account Login Control. This is specifically for Application Express end users and enables you to set the maximum login failures and the account lifetime. You can also disable access to Application Builder components or disable RESTful Services as needed.
Application developers are responsible for developing secure applications. You should review the Oracle Application Express Application Builder User's Guide, Managing Application Security. Within Application Properties you can define the proxy server for the application, which will overwrite the instance value if set. You can also override instance settings for maximum session length and idle time and specify the URLs to redirect to.
Defining the appropriate authentication scheme (that is, establishing a user's identity) for an application is critical to ensure only appropriate users can log into the application. As a developer, you must determine which pages do not require authentication (also known as public pages). These pages should never contain any sensitive information. You should also define authorization schemes to easily define access to different application components. It is very important to use the appropriate authorization on sensitive pages and the navigation controls (tabs, buttons, links, etc.) used to access the page(s). You can also use authorizations on processes, validations and computations to ensure only authorized users can maintain specific data.
As a developers it is important for you to also understand hardening items, especially password items. Password items should not be saved or encrypted in session state and have restricted session state protection. Where available, you should ensure that Form items have the HTML escaped. It is also advisable to restrict the enterable characters for text items to limit cross site-scripting and other injection attacks. Report regions and dynamic output should also be escaped to prevent attacks.
As a best practice, once you have completed development of your application run the Application Advisor (under Utilities). This includes many checks for conditions which could present security vulnerabilities. There are also third party tools available which extensively analyze applications for vulnerabilities. The two tools currently available are APEXSec Security Tool and APEX-SERT.
There are a number of factors that should be reviewed when facing performance issues with Oracle Application Express applications.
The first step in reviewing slow running applications is to determine where the bottleneck is located. The performance of an Oracle Application Express application can be determined by reviewing the time it takes for the database to return a specific page request or submission. If this time is short compared to how long it takes to render the page on the user's browser then other components are likely causing the perception that the application itself is slow. You should check all of the components involved including the client machine(s), network, middle-tier, database, and disk/storage devices. With respect to the database check the SGA, CPU utilization, locks and database parameters. It is also important to determine what else is running within the database such as legacy applications, data warehouse, batch programs, and database jobs.
The majority of the time, performance issues within Application Express applications are due to developer-authored SQL and PL/SQL. You should utilize standard database performance tuning techniques to address such issues. Very seldom is the issue related to a bug with Application Express or the Oracle Database. The steps to diagnosing performance issues are to identify the time window, isolate the largest consumers of database time, identify the Application Express workspace and application, and then correlate the offending SQL with a specific location within the application. Once you have identified the slow running page(s), you should run the application in debug mode to identify the specific components consuming the most time.
For queries that have been optimized but still take more than a second processing, you should consider implementing a "progress bar" and preventing multiple page submissions. Users expect instant results and will often resubmit a page if it does not return immediately. Each page submission will instigate a new session in the database and continue until completion unless managed properly. This will often degrade performance and may lead to database locks. This is especially true with interactive reports. An alternative may be to consider using Application Express collections to hold query results. Using collections the expensive, time consuming, query is only executed once for each user rather than each time they perform pagination, filters, etc.
Activity logs are recorded by the Application Express engine to allow administrators to review usage and performance using various reports. If a particular page is accessed extensively or a page renders slowly then these reports will often help identify the pages. Such pages are candidates for further analysis, such as running the report query in SQL Developer so that you can review the Explain Plan. Another method of determining expensive elements within a page or process is to review the Application Express Debug mode. The activity logs switch by default every 14 days, but this setting can be modified by Instance administrators. If you wish to keep the log files indefinitely it will be necessary to create a batch job to copy records from the activity log table to your own table.
There are a number of activity reports available to Workspace administrators including:
Page Views - Contains reports of page views organized by view, user, application, or application and page.
Developer Activity - Offers reports of developer activity organized by developer, day, application, application changes, and day or month.
Page View Analysis - Contains reports analyzing page views, such as top page views by application.
Sessions - Lists active sessions with the current workspace.
Login Attempts - Offers reports listing login attempts.
Environment - Contains reports of environments organized by user agent, browser, external clicks, or operating system.
Application Errors - Contains a report of application errors.
Workspace Schema Reports - Offers summaries of schema tablespace utilization and database privileges by schema and a list of all database schemas available in the current workspace.
For Instance administrators the following reports are available:
Page Views - View activity by application, user, workspace, day, or REST access.
Workspace Purge - View a dashboard summary, inactive workspaces, workspaces purged, workspaces that became active, or a workspace purge log.
Environment Reports - View a summary of used operating systems, browser types, user agent, or external sites.
Calendar Reports - View workspaces by date last used, page views by day and then by application and user, or by hour.
Service Requests - View new service requests or signup survey activity.
Logs - View the mail log, jobs log, automatic delete log, or monitor packaged application installations.
Login Attempts - View login attempts or developer last login.
Developer Activity - View application changes by developer or workspace.
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 Application Express specific information for the client information, client identifier, and module. Such information allows you to identify the associated SQL when you generate Oracle Trace files and use TKPROF to analyze them. You can add & p_trace=YES to the end of the Application Express URL or use ALTER SESSION.
Automatic Workload Repository (AWR) reports collects performance statistics every hour, by default. Active Session History (ASH) reports are a system-wide record of database activity. Such reports can be utilized 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 11g (PL/SQL Profiler in 10g) which will account for execution times separately. There are also a number of Application Express views, which are a database catalog of everything related to Application Express. These views will enable you to access information from Application Express using tools such as SQL Developer, SQL*Plus or command line. If you are granted the APEX_ADMINISTRATOR_ROLE then you will be able to query across the entire instance, rather than just the workspace your schema user is associated with.
There are certain database parameters that can also impact performance. In particular you should review the SHARED_POOL_SIZE and JOB_QUEUE_PROCESSES parameters to ensure they are sized correctly. Another concern is to ensure the database System Global Area (SGA) fits in real memory. An undersized SGA can dramatically hamper Application Express performance. Their needs to be sufficient memory in the Shared Pool for PL/SQL and in Buffer Cache for the Application Express metadata. If the SGA is sized correctly it should not be necessary to pin PL/SQL programs from the Application Express engine as they are accessed regularly, and should not be swapped out of memory. Given that Application Express engine resides in the database it is strongly recommended that you gather statistics on the APEX_040200 schema. Accurate statistics will allow the cost-based optimizer to develop better query plans for accessing the application metadata.
Database Resource Manager, an Oracle Database Enterprise Edition option, is exceedingly useful for maintaining an Application Express instance. Using resource manager you can limit the types of system resources (CPU_PER_CALL and LOGICAL_READS_PER_CALL) available to each user request. By setting up multiple resource profiles, with automatic switches between the profiles, as a user request exceeds a certain threshold you can reduce the CPU available to that transaction. For example you may define a default profile APEX_HIGH which can utilize 70% of CPU for 10 seconds, APEX_MEDIUM with 8% CPU for 120 seconds, APEX_LOW with 2% CPU for 1800 seconds, and OTHERS with 20% CPU. As a specific transaction exceeds 10 seconds it is switched to the APEX_MEDIUM profile which drastically reduces the CPU available. If the transaction switches to APEX_LOW and exceeds 1800 seconds then you can cancel the SQL or kill the session. This capability will ensure that one specific user or poorly written application cannot monopolize the CPU causing other users and applications to respond poorly.
To improve performance you should place static files on the Web server. You should also enable gzip compression on the Web server so that the file size is minimized when transmitting the static files to the browser. Furthermore, enable file caching for browsers by sending expiration headers.