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:
- 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
which is used by both the GL posting program and FSGs. Given that the number of balances in
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,
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 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:
- Collect Statistics (Default Yes) - Set this value to Yes
- Rebuild Indexes (Default No) - Set this value to No
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