PIPER-Rx - GL Program Optimiser

PIPER-Rx
Oracle E-Business Suite Resource Centre

General Ledger - GL Program Optimiser

GL program optimiser

The General Ledger - GL program optimiser is a concurrent program that is available to the GL super user responsibility and it has two functions: The program collects and stores information on the spread of balances in gl.gl_balances which is used by both the GL posting program and FSGs. Given that the number of balances in gl.gl_balances increases each month with the month end roll process, it is important for the overall performance of posting and FSGs to keep the GL program optimiser stats up to date.

When was the GL Program Optimiser last run?

The following SQL shows you when the GL program optimiser was last run and by whom:
    SELECT fu.user_name ||' - '|| fu.description "Last Run By",
           to_char(max(rts.last_update_date),'DD-Mon-YY HH24:MI') "Last Run",
           trunc(sysdate - max(rts.last_update_date)) "Age (Days)"
      FROM rg.rg_table_sizes rts,
           applsys.fnd_user fu
     WHERE rts.last_updated_by = fu.user_id
     GROUP by fu.user_name, fu.description;
  
Example Output

It is not uncommon for the program to have never been run as is the case in this example.

This program is run by logging onto the GL super user responsibility and then running the concurrent program - GL Program Optimiser.

When running the GL Program Optimiser there are two options:

Warning: Do not set the Rebuild Indexes option to Yes as this could cause more damage and may have an adverse effect on the entire General Ledger application. FYI Older releases dropped existing code combination indexes and rebuild indexes as defined in the flexfield definitions. Current releases will maintain existing indexes and build only those that have been defined in the flexfiled definitions that do not currently exist.

Given the above warning, what we donít want is any of the GL super users finding the GL program optimiser program and running it thinking they are doing the right thing; I have been there and itís not pretty. Thatís why I always remove it from the GL super user responsibility and add it to the Systems Administrator responsibility which they cannot get to.

How often should GL Program Optimiser be run?

I generally set this up as a scheduled job to be run once per month, a couple of days after the month end roll over.

I hope you find this useful.

Last update: May 2009