Oh! where did my GL performance go?

Oh! where did my GL performance go? - Part 2
OR
Improving GL_CODE_COMBINATIONS performance.

Please ensure you have read - Oh! where did my GL performance go?

Performance tuning is a very emotive subject, every one has an opinion and are usually quick inform you both politely and in some cases impolitely that you are wrong. However, take comfort in the knowledge that there is no right or wrong answer, if your database is performing within expectations then you have been successful.

With this in mind I will identify where you may be able to "optimise" the performance of the GL_CODE_COMBINATIONS table.

Before you start.
There is no point in implementing any performance enhancements unless you have a base line with which to measure your success or other wise.

CODE_COMBINATIONS

From my previous article Oh! where did my GL performance go? I indicated that:

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

The basis for this optermisation is based in the proposition that the more selective and index is, the better the performance.

1. Identify the structure of your chart of accounts.

For this example I will use a four (4) segment chart of accounts.

       1. Division
       2. Department
       3. Account
       4. Subaccount

       Division       -----> gl_code_combinations ( segment1 )
       Department     -----> gl_code_combinations ( segment2 )
       Account        -----> gl_code_combinations ( segment3 )
       Subaccount     -----> gl_code_combinations ( segment4 )

2. Determine the selectivity of each of the segments.

For each of the segments in turn run the following SQL*PLUS script

       SELECT count(distinct segment1)
         FROM gl_code_combinations
       /

For this example assume the following results:

     Segment Name  distinct values
     -----------------------------
     Segment1              4
     Segment2           4000
     Segment3           2500
     Segment4            555

As such the order of selectivity is as follows:

segment2, segment3, segment4, segment1

3. Create a concatenated index on GL_CODE_COMBINATIONS

Create a concatenated index on the account segments in decreasing order of selectivity, however do not include any segments with less than 50 distinct values.

Using the above example I would write a create index script as follows:

     CREATE index GL_CODE_COMBINATIONS_C1 ON
            GL_CODE_COMBINATIONS ( SEGMENT2, SEGMENT3, SEGMENT4  )
     STORAGE ( initial X M next X M
               minextents 1 maxextents 50
               pctincrease 0 )
     TABLESPACE index_tablespace
      /

The _C1 used in the index name indicates custom index one

4. Identify the chart of account segment used most frequently for reporting

By consulting your accountants and your Applications Administrator, identify which account segment is most used for reporting. Generally I have found that most reporting is based on account number, as such ensure you have an single index on the account number segment, in this example it would be on segment3.

     CREATE index GL_CODE_COMBINATIONS_C2 ON
            GL_CODE_COMBINATIONS ( SEGMENT3 )
     STORAGE ( initial X M next X M
               minextents 1 maxextents 50
               pctincrease 0 )
     TABLESPACE index_tablespace
     /

NOTE: Do not build an index on segment3 if it is the first column in the concatenated index. I.E. If department ( segment2 ) was identified as the most used segment for reporting, a separate index on segment2 would be redundant as segment2 is the first column in the concatenated index identified above.

5. Identify all indexes on GL_CODE_COMBINATIONS

If the GL optimise process has been run you will probably have at a minimum indexes on:

segment1

segment2

segment3

segment4

Its a good idea to write index create scripts for the indexes you have, just in case you want to re-create them. The PL*SQL script in ORA 10: Moving, Rebuilding and or Resizing Indexes will help with this process.

6. Drop the redundant indexes

As stated in the in the prior article: "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."

Armed with this knowledge you may want to run the GL optimisation process and let the application clean up the indexes first.

Now for the fun part. Drop all the single column gl_code_combination indexes on the chart of account segments.

7. Create your custom indexes

Execute the create index scripts you created in steps 3 and 4.

8. Follow up

Once you have evidence of improved performance, inform your Accountants and Applications Administrator that you have optimised the GL_CODE_COMBINATIONS index structure and that they should answer "NO" to the Maintain Indexes option when running the GL optimisation process.

Have a Oracle Question
Do you have an Oracle Question?

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

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.