Tips to Optimize Performance of Oracle Forms and Reports

It is critical to realize how vital the performance of Oracle Forms and Reports can be to your business. In many cases, achieving a high-performance level can make or break your business.

With a market share of over 50%, Oracle is still the most popular database management server in the world as of February 2023.

It goes without saying that the database server is one of the biggest Oracle Forms dependencies. If it’s running slow, nothing runs fast. However, optimizing the performance of these applications can be a daunting task.

This article will provide tips on how you can maximize performance without affecting the functionality of your application.

Let’s have a glance at what we are going to cover in this article:

1. 1. Optimize your SQL Database
   2. Benchmarking
   3. Reduce the Number of JDBC Drivers Installed on Server(s)
   4. Use Proper Caching

Fine-Tune Client Startup Time

The time required to start a form or report depends on how many objects are used in the design and whether they are in the same database. In addition, you can use Oracle Forms Designer to set up an optimized client for your application.

Figure 1: Forms Designer within the Data Entry Forms and Queries section makes it easy to design web-based user-friendly Forms using the pre-defined layouts.

The following sections describe how to optimize client startup time and create an optimal client:

• Users tend to assume nothing is happening at startup if they don’t observe some motion on the screen. Oracle offers two useful settings, the splash screen and background picture, to let users know that the Form is functioning and that it has acknowledged them. You can change either the splash or background parameter in the formsweb.cfg file to any GIF to customise both.
• Divide big modules into smaller ones. The FMB file consumes more resources if more canvas and window definitions there are in the file. Search for methods to minimise the FMB. For instance, if you need to use a lot of tabs or canvas, a general rule of thumb is to limit the number of windows and canvas to three and five, respectively. You may always load another FMB to gain the form space you require; for Oracle Forms, this is really more effective.

“Oracle Forms can manage several FMBs more effectively than it can manage an excessive number of canvas or windows in a single FMB.”

• Use tables that are related to each other in the same database as much as possible. For example, if you have a customer table and an address table, you should use those tables instead of creating separate tables for each customer and address combination. This reduces overhead for Oracle Forms Designer and increases performance for users who access these records with forms or reports.

Optimize your SQL Database

Oracle Forms and Reports are based on an SQL database, which means that all the data processing is done in SQL statements. The more data you have, the more CPU-intensive this process will be.

One of the most important ways to improve database performance is by optimizing SQL statements.

“Make sure that SQL statements are well-written, indexed, and optimized.”

Use the EXPLAIN PLAN command to analyze the execution plan of SQL statements and identify any potential bottlenecks.

If you have a large number of records with small values in your database tables, it may be best to create a separate table for each form or report, so each record has its column. This way, when you perform calculations on a particular field in one Form or Report, those calculations will only affect its corresponding field in another form or report.

It also helps prevent cross-table data from being transferred across tables when doing calculations in one Form or Report.

Benchmarking

Benchmarking is a way of gauging the performance of your application. You can use it to see if you have a bottleneck in your system, or you can use it to compare your current performance with that of other applications.

Benchmarking is usually done by measuring the time it takes for a specific task to complete. This can be done in two ways:

1. Measure the time it takes for a single long-running task to complete, such as an SQL query or report being generated. This is useful when you have multiple tasks waiting on each other or if one task has to wait for another to finish before starting its work.
2. Measure the time it takes for multiple short-running tasks, such as generating reports from an SQL query or assembling an HTML page from multiple files. This will give you an idea of how much additional load your application has after performing these tasks independently.

Reduce the Number of JDBC Drivers Installed on Server(s)

JDBC drivers are software that allows applications to connect with databases, which are required for Oracle Forms and Reports.

“The more JDBC drivers you have installed on your server(s), the more resources they will use.”

For example, suppose you have multiple servers in your environment, and each server has its own set of JDBC drivers installed. In that case, each server will have its version of each driver, which can cause problems when communicating between different versions of these drivers.

You can take the following steps you can take to reduce the number of JDBC drivers:

• Identify which JDBC drivers are installed on your server(s): Check the installation directory and any configuration files that specify JDBC drivers.
• Determine which JDBC drivers are actually needed: Review the applications running on the server(s) and determine which JDBC drivers are necessary for them.
• Remove any unused or unnecessary JDBC drivers: If you find any JDBC drivers that are not needed, uninstall them from the server(s).
• Consolidate multiple versions of the same JDBC driver: If you have multiple versions of the same JDBC driver installed, choose one version to keep and uninstall the others.
• Use a JDBC driver manager: Consider using a JDBC driver manager, which can help you manage multiple JDBC drivers more easily and efficiently.

Use Proper Caching

Caching is a technique that enables your application to store frequently accessed data in memory. This reduces the number of database queries that need to be executed, thereby improving performance. Use caching for frequently accessed data such as lookup tables and reference data.

The JVMs supported by Oracle Forms Server, such as Oracle JInitiator and oJDK, support the caching of JAR files

The JVM initially checks the local client cache for a pre-cached JAR file when it needs to reference a class. If the class is present in the cache, the JVM will then verify the server for any updates to the JAR file. If there are no updates, the class is loaded from the local cache instead of retrieving it over the network, which can save time and bandwidth.

“The cache size should be appropriate to prevent valid JAR files from being overwritten, requiring another JAR file download when the application is run again.”

The default cache size is 20 Mb, but this size should be compared with the size of the cache contents after successfully running the application.

JAR files are cached relative to the hosting server from where they were originally loaded. This can pose challenges in a load-balancing architecture, where multiple identical JAR files from different servers could fill up the cache. To overcome this issue, developers can store the JAR files in a centralized location and reference them for each server in the load-balancing configuration. This ensures that only one copy of each JAR file is maintained in the client’s cache, enhancing cache efficiency.

JAR files are cached relative to the host from which they were loaded. This has implications in a load-balancing architecture (which is not described here) where identical JAR files from different servers can fill the cache. By having JAR files in a central location and by having them referenced for each server in the load-balancing configuration, the developer can ensure that only one copy of each JAR file is maintained in the client’s cache.

The Oracle-supplied JAR files are already pre-signed, but if you are using custom JAR files, ensure that the required classes are signed to enable proper functionality.

In The End

By following these tips, you can improve the performance of your Oracle Forms and Reports and provide a better user experience for your users.

We at Abaca Systems help businesses understand, evaluate and optimize their data across several functions, whether front, middle or back-office operations. We have several Saas products like Abaca Digital AIM. Abaca Precision ERP, etc build with the latest Oracle technologies. We focus on the design, development and implementation of Oracle technology-based solutions, and our Consulting Practice leverages deep technical expertise in Oracle Application Technologies. We have managed projects worldwide in multiple industries, such as Banking, Insurance, Telecommunications, Retail, Manufacturing and Distribution.