Performance Issue with GLSTPM Incremental Add/Delete Summary Template Program When There Are a Large Number of Summary Accounts (Doc ID 1682949.1)

Last updated on JUNE 20, 2024

Applies to:

Symptoms

Performance issue reported in Incremental Add/Delete Summary template program (GLSTPM):

slow running sql statement as found in the trace is:

DELETE FROM GL_BALANCES
WHERE  template_id+0 = :h_template_id
  AND code_combination_id BETWEEN
      :curr_min_ccid AND :curr_max_ccid
  AND rownum <= :row_num

No custom indexes on gl_balances are in place

Have run the gather schema stats program for apps and gl schemas and issue remains.

Log file shows slowness in (as an example):
>> glstind() 05-AUG-2013 16:09:36
SHRD0119: Deleted 20000 record(s) from GL_BALANCES.
etc
<< glstind() 08-AUG-2013 05:17:37

A large number of summary accounts are created from each summary template.
Ranging from several hundred thousand to over a million summary accounts per template.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.