Observation

Temporary tables used in the GL Transfer process are no longer cleared down once the transfer is complete. These tables are named XLA_GLT_% where % is the group_id used in the General Ledger Transfer.

Versions affected: R12 and Above

Impact

  • The performance of the “Gather Schema Statistics” for the schema GL (General Ledger) has decreased.
  • High Generation of redo logs during the run of “Gather Schema Statistics” on GL Schema.
  • Due to the above problems, it is affecting the space usage of tablespaces SYSAUX, APPS_TS_TX_DATA and APPS_TS_TX_IDX.
  • XLAACCUP module: Accounting Program Fails

Cause

Temporary tables used in the GL Transfer process are no longer cleared down once the transfer is complete. This is a change in behavior as these tables were normally dropped for fully processed GL Transfers. Tables XLA_GLT_ are not dropped, so that it is available for debugging.

Gather Schema Statistics for the schema is taking longer time to complete due to the retention of the XLA_GLT_ interface tables.

While execution of Gather Stats, it is creating large amount of data in these tables:

  • WRI$_OPTSTAT_TAB_HISTORY
  • WRI$_OPTSTAT_IND_HISTORY
  • WRI$_OPTSTAT_HISTHEAD_HISTORY
  • WRI$_OPTSTAT_HISTGRM_HISTORY

Due to the above problems, it is affecting the space usage of tablespaces SYSAUX, APPS_TS_TX_DATA, APPS_TS_TX_IDX.

Solution

The XLA_GLT_ interface tables can be purged on a regular basis by running the program “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting”. This Purge program can be run from the GL responsibility after every period close.

Note: If it is not found in the submit request window for a responsibility, add it to the request group using the system administrator responsibility.

  • Login to the application as System Administrator responsibility.
  • Open the Request Groups form.
  • Query for the ‘All Reports’ Request Group for the respective Application (like payables, receivables etc).
  • Add the program “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting” (Subledger Accounting application) to the above request group.
  • Save.

If the purge program is not available in the current R12 environment, then apply following patch:
For R12.0.x: Patch 10382869:R12.XLA.A
For R12.1.x: Patch 9781687:R12.XLA.B

Reference

1158934.1 XLA_GLT_XXXX Temporary Tables For Purge Program ‘Purge Temporary Journal Import Interface Tables’ Not Dropped
796417.1 SLA: Temporary Tables XLA_GLT_ % Are No Longer Dropped