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

What OEBS Application modules are installed?

The following genreates a list of the OEBS Application modules you have installed including their version and patch levels...

SELECT fa.application_id appn_id,
       decode(fa.application_short_name,
       'SQLAP', 'AP', 'SQLGL', 'GL', fa.application_short_name ) appn_short_name,
       substr(fat.application_name,1,50)
              decode(sign(length(fat.application_name) - 50), 1, '...') Application,
       fa.basepath Base_path,
       fl.meaning install_status,
       nvl(fpi.product_version, 'Not Available') product_version,
       nvl(fpi.patch_level, 'Not Available') patch_level,
       to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI') last_update_date,
       nvl(fu.user_name, '* Install *') Updated_by
  FROM applsys.fnd_application fa,
       applsys.fnd_application_tl fat,
       applsys.fnd_user fu,
       applsys.fnd_product_installations fpi,
       apps.fnd_lookups fl
 WHERE fa.application_id = fat.application_id
   and fat.language(+) = userenv('LANG')
   and fa.application_id = fpi.application_id
   and fpi.last_updated_by = fu.user_id(+)
   and fpi.status = fl.lookup_code
   and fl.lookup_type = 'FND_PRODUCT_STATUS'
UNION ALL
SELECT fa.application_id,
       fa.application_short_name,
       substr(fat.application_name,1,50)
       decode(sign(length(fat.application_name) - 50), 1, '...'),
       fa.basepath,
      'Not Available',
      'Not Available',
      'Not Available',
      to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI'),
      nvl(fu.user_name, '* Install *')
 FROM applsys.fnd_application fa,
      applsys.fnd_application_tl fat,
      applsys.fnd_user fu
WHERE fa.application_id = fat.application_id
  and fat.language(+) = userenv('LANG')
  and fa.last_updated_by = fu.user_id(+)
  and fa.application_id not in
      ( SELECT fpi.application_id
         FROM applsys.fnd_product_installations fpi )
ORDER by 2;

Example output:


Note: For readability I have converted the SQLAP to AP and SQLGL to GL – this looks so much better for users...

Hope this is useful....