General Ledger - GL Program Optimizer

The General Ledger - GL program optimizer is a concurrent program that is available to the GL super user responsibility and it has two functions:

• Collect statistics to aid in the running of FSGs and posting
• Build indexes on gl_code_conbinations where they do not exist.

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 optimizer stats up to date.


When was the GL Program Optimizer last run?
The following SQL shows you when the GL program optimizer 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;

It is not uncommon for the program to have never been run.

This program is run by logging onto the GL super user responsibility and then running the concurrent program - GL Program Optimizer.
When running the GL Program Optimizer there are two options:

• Collect Statistics (Default Yes)  -Set this value to Yes
• Rebuild Indexes (Default No)  - Set this value to No

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 optimizer 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 Optimizer 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.

Good Luck....