Introduction

Most customers experience average cell refresh rates of between 10 and 50 cells per second, although some experience 100+ cells per second refreshes. This document may provide solutions to improve performance in cases where refresh rates are less than 1 cell per second or if reports are timing out.

Many factors will affect the performance including the PC specification, the server age and specification, as well as other processes running on the server. The customer remains ultimately responsible for the performance of the SQL queries running on your Oracle system. However, based on our experience at many of our customers over a number of years, we can provide the following tips and suggestions which might assist to improve performance.

The guide has been divided into two sections, the first describes performance improvement options categorized as Level 1 options. In most cases these options can be tested and implemented without the help of IT support.

The second section, Level 2, describes more complex performance options that will require experienced IT system and application administration resources to evaluate, test and implement.

Level 1 - Performance Improvement Options

In-Memory Database

The In-Memory database attempts to keep current balances locally on the PC and only queries those balances again from Oracle if they have changed. On some Oracle systems this process has been found to be inefficient. We are working on providing a solution to this issue.

If you are experiencing performance issues you should try disabling the In-Memory database to see if that makes a difference. The setting to disable the In-Memory database can be accessed as follows in Excel: GL Wand tab | Tools | Options.

Custom Index

The creation of this index can significantly improve performance. The process to create this index is as follows:

1. Determine the number of distinct values per segment in your GL_CODE_COMBINATIONS table

2. Order your segments from largest (most distinct values) to smallest

3. Determine the most frequently used segments for General Ledger Reporting.

4. Create the GL_CODE_COMBINATIONS_CAT index specifying the order of the segments as follows:

5. As a general rule, order your segments from largest (most distinct values) to smallest

6. If however your chart of accounts contains some segments which contain many distinct values but are used infrequently for reporting, then you should rather include the frequently used segments first in the index.

To determine the order of an index execute the following select statement:

SELECT COUNT(DISTINCT &segment_name) FROM gl_code_combinations;

This select statement counts the number of distinct values for a column name that you enter for the GL_CODE_COMBINATIONS table. Enter SEGMENT1, rerun with SEGMENT2, and for all segments until the last segment used in your account flexfield (SEGMENT6 in our example).

In our example the select statement could return the values:

• COUNT(DISTINCT SEGMENT1) : 214

• COUNT(DISTINCT SEGMENT2) : 355

• COUNT(DISTINCT SEGMENT3) : 1134      

• COUNT(DISTINCT SEGMENT4) : 1255

• COUNT(DISTINCT SEGMENT5) : 21

• COUNT(DISTINCT SEGMENT6) : 5423

From the data above, the best order to make the most selective index is:

- SEGMENT6, SEGMENT4, SEGMENT3, SEGMENT2, SEGMENT1, SEGMENT5

However, in our example segment 4 is the account segment and is used most in reporting. Segment 6 is a project segment and has many values but is not used much in reporting. The revised index order would then be:

- SEGMENT4, SEGMENT6, SEGMENT3, SEGMENT2, SEGMENT1, SEGMENT5

It is worthwhile to compare performance of the above mentioned order to an index that excludes segment 6 completely, especially if segment 6 is never used for reporting.

Create GL_CODE_COMBINATIONS_CAT with the concatenated index as below:

In our example you should sign on to SQL*Plus as APPS and execute the following:

(Replace this with your own SQL statement)

CREATE INDEX gl_code_combinations_cat ON gl_code_combinations   (segment4, segment6, segment3, segment2, segment1, segment5);

This can be a process of trial and error. You might find that your system provides better performance with an index order slightly different from the recommendation. It is therefore worthwhile to try a few variations in order to identify the order that works best on your system.

The creation of this index is recommended by Oracle in certain circumstances. Refer to Oracle support note (198437.1).

SQL Hint

In the Wands for Oracle System Administrator options you have the ability to specify a SQL hint which will be added to the SQL statement when it is executed. With this option you can force the use of a specific index if that configuration provides the best performance on your system.

Please see the Wands for Oracle user guide for instructions on setting the System Administrator options. The user guide can be accessed by clicking the help button on the Wands (Oracle) tab in Excel.

Gather Statistics

The cost based optimizer will attempt to process SQL statements as efficiently as possible. In order for the Cost Based Optimizer to work effectively the “Gather Statistics” program needs to be executed on a regular schedule. Refer to the applicable Oracle documentation for more information on the “Gather Statistics” program.

Based on experience with our customers we recommend the following gather statistics percentages:

Other Indexes

GL Wand is designed to utilize the following indexes to improve performance of the queries. These indexes are either standard or have been recommended by Oracle to improve reporting performance. We recommend that you ensure that these are created.

GL Optimizer Program

GL Optimizer is a standard GL concurrent request. It runs the gather statistics program in the GL schema and creates indexes for your accounting flexfield segment columns. Refer to your Oracle documentation for additional information.

Summary Accounts

The Oracle General Ledger provides the ability to create summary accounts which are special accounts that hold a balance at a summary level. GL Wand can use these summary accounts to facilitate faster reporting. Typically, summary accounts will help with reporting performance where the reports executed at higher levels in the reporting hierarchy take very long to complete. For example, if you have a cost center hierarchy with many levels and running a GL Wand report at the top level cost center takes very long, you could create summary accounts at this top level cost center and this could dramatically improve reporting time. The process is as follows:

1. Decide at what level in the hierarchy the GL Wand reports will be executed.

2. Follow the instructions in the Oracle General Ledger user guide to create summary accounts at this level.

Performance gains can be quite substantial but it will depend on your specific configuration and reporting requirements.

In GL Wand you must use the specific Get Balance function which reports on Summary Accounts. See the GL Wand user guide for a detailed explanation.

Get Balance Formulas

When creating Get Balance formulas always use “%” to denote all values as this allows more efficient SQL to be generated. Do not use a range like “000-ZZZ” or a parent like “T” as they will require unnecessary processing.

Remote system health check

If you are still having performance issues then please log a performance related case by clicking the support button or emailing e4asupport@insightsoftware.com and we will see if we can assist further.

Level 2 - Performance Improvement Options

Result Cache

The Oracle Database Result Cache feature, which was introduced with Oracle Database 11g Release 1, provides the ability to cache SQL query and PL/SQL function results in memory.

How does the Result Cache feature work?

When the result cache feature is enabled for a query, the result of the query execution is stored in the result cache area. If the same query is then executed, the database will search for the results of the submitted query in the result cache area. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.

When users execute queries repeatedly, the database retrieves results from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

Benefits of Using the Server Result Cache

The Get Balance function is a good candidate for caching as the query generally accesses a high number of rows but returns only a single row result set. This allows for very efficient storage of Get Balance query results, enabling the caching of a large number of results in a relatively small result cache space.

Enabling the Server Result Cache allows you to configure a server side In-Memory area, similar to the proprietary Wands client side In-Memory feature; enabling server side caching allows result to be shared between all users executing Get Balance functions.

Configuring GL Wand to use the Result Cache Feature

The /*+ RESULT_CACHE */ hint instructs the database to cache the results of a query and to use the cached results in future executions. Add this hint to the Balance Hint configuration option to enable the Get Balance function to take advantage of the Result Cache feature; Wands (Oracle) Tab -> Admin Options: Configuration.

By default, on database startup, Oracle Database allocates memory to the server Result Cache in the shared pool. The memory size allocated depends on the memory size of the shared pool and the selected memory management system. See your database versions Performance Tuning Guide for guidance on increasing your result cache memory size.

Partitioning

Partitioning splits large tables and indexes into smaller, more manageable components called partitions, and can offer significant benefits for large databases with high performance requirements. Partitioning has been available since Oracle 8i but has been greatly enhanced from version 11g. Several Oracle EBS modules take advantage of partitioning out of the box, and the use of custom partitioning with Oracle E-Business Suite is supported. 

How does Partitioning work?

Partitioning can improve query performance by limiting the amount of data queried by a particular SQL statement to a single or sub-set of partitions. Partitions are skipped that are not required (pruned) before the results are further reduced by additional criteria. A partition key is a set of one or more columns that determines in which partition a particular row will reside. The correct definition of the partition key and selection of partitioning method is essential.

Custom partitioning applies to cases where an existing (un-partitioned) Oracle EBS table is redefined as a partitioned table according to a specific partitioning method. This requires creating a new table with the required partition definition and then transferring the data from the original table into the new one. The partitioned table then replaces the original table.

Benefits of using Partitioning

The Get Balance function could benefit from partitioning, by pruning the dataset that needs to be queried and returned. The optimizer can immediately identify the partitions that need to be accessed from the partition key. Partition keys based on PERIOD_NAME or LEDGER_ID matches the parameters of the Get Balance function thereby potentially improving performance.

Configuring GL Wand to use Partitioning

The GL.GL_BALANCES table queried by GL Wand is a good candidate for partitioning. It is important to define a partitioning strategy according to your specific data analysis and perform robust testing.

There is no Wands for Oracle related configuration required to allow GL Wand queries to take advantage of Partitioning.

References

Using Database Partitioning with Oracle E-Business Suite (Oracle Support - Doc ID 554539.1)

In-Memory Column Store

The Oracle Database In-Memory Column Store, which was introduced with Oracle Database 12c Release 1, allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage system lends itself extremely well to analytic queries as found in GL Wand.

How does the In-Memory feature work?

Oracle Database In-Memory provides a dual-format architecture that enables tables to be simultaneously represented in memory using traditional row format and a new in-memory column format. The Oracle SQL Optimizer automatically routes analytic queries to the column format. Oracle Database 12c automatically maintains full transactional consistency between the row and the column formats.

Benefits of Using the In-Memory Column Store

The In-Memory Column Store allows for faster data retrieval when only a few columns are selected but the query accesses a large portion of the data set. The GL Wand Get Balance function matches this performance criteria and can take advantage of the improved data retrieval offered by the In-Memory Column Store feature.

Configuring GL Wand to use the In-Memory Column Store

GL Wand uses the GL.GL_BALANCES and GL.GL_CODE_COMBINATIONS tables to retrieve balance information. By placing the columns used by the Get Balance queries, in the In-Memory Column Store, the Oracle Database Cost Based Optimizer (CBO) will have the option to employ an In-Memory operation to execute Get Balance queries. See Appendix A for a list of recommended columns to be included when configuring the In-Memory column store option.

There is no Wands for Oracle related configuration required to allow GL Wand queries to take advantage of the In-Memory Column Store functionality.

Appendix A

Columns from tables GL.GL_BALANCES and GL.GL_CODE_COMBINATIONS recommended for inclusion in the In-Memory Column Store: