Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Where did my GL performance go?
OR
The case of the missing indexes.

The Oracle Applications GL optimisation process is set up and executed using two different responsibilities.
In a normal implementation this will most probably involve two or more people.

STEP ONE: Defining accounting flexfields

The first process in the setup may have been completed by the person/s who installed Financials or the person/s who defined the key flexfield segments as part of the initial implementation.

This part of the setup defines which Code Combination segments are to be indexed by the GL optimise process.

How to find and view the current setup:

Connecting as the application sysadmin navigate to the Defining Key Flexfield Segments.
/ Navigate Application Flexfield Key Segments
In the "Key field title" select Accounting Flexfield. The associated description should be similar to: "General Ledger Accounting". The associated structure should be your chart of accounts.

The next Zone should give you the segments in your chart of accounts.

The column "Indexed attribute" ( default = Yes ) will be used by the GL Optimiser process to determine whether to build an index on that segment in GL_CODE_COMBINATIONS.

STEP TWO: Executing GL Optimisation

I have been told that the application will give a warning message during month end processing indicating that the GL has not been optimised. ( Because of the month end rush and that the users, as you would expect, are more interested in closing the month than recording warning messages, I have never seen the warning message ).

In order to execute the GL optimisation process, logon as a GL super user.

Run the GL optimisation process:

/ Navigate Setup System Optimise
( Don't do this until you have read the rest of this page )

WHILST ALL THIS WAS HAPPENING:

In order to improve performance it is a generally accepted practice for the Financials DBA to create concatenated indexes based on the selectivity of the CODE COMBINATIONS account segments.

So now we have three places involved with performance. 

NOW FOR THE PROBLEM:

When you or the applications administrator runs the GL optimisation process and answers yes to "Maintain Indexes", the process removes indexes not ending in N31-N40, this will include any indexes build by the DBA's. The process then builds single indexes on segments set to "Yes" in the flex field segments.

Storage for the new indexes will be based on the values set for GL_CODE_COMBINATION_INDEXES in the / Navigate Setup System Storage screen.

The programme that does all this work is RGOPTM. This programme is supplied as object code and as such cannot be read. Try using strings to view the text in the file. 

SO TO RECAP:

If you manually build your GL_CODE_COMBINATION indexes, run your optimise process answering NO to "Maintain Indexes"

The structure and number of indexes can and will affect the performance of the application. It would be safe to say in this case that more indexes is not necessarily better than a small number of "well" chosen concatenated indexes.

The best option is to let your application DBA build the GL_CODE_COMBINATION indexes, based on actual code combination selectability ( providing the DBA creates the indexes with the correct sizing, this will also prevent fragmentation ). Run the optimise process Answering NO to "Maintain Indexes"

Exit to : Oracle Hints and Tips