PSA_ACCTGGL.SEL_PC PERFORMANCE DUE TO LACK OF UPDATESTATS AFTER PSA_ACCTG_TA1

(Doc ID 2132825.1)

Last updated on DECEMBER 01, 2017

Applies to:

PeopleSoft Enterprise FIN Contracts - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Performance issue with PSA_ACCTGGL due to lack of UpdateStats after truncate/inserting data into PSA_ACCTG_TA1.

Following steps in Sel_PC section Insert or Delete rows from %Table(PSA_ACCTG_TA1).
Some steps also are doing a NOT EXISTS (select ‘x’ from %Table(PSA_ACCTG_TA1)….)

From above message log, Revenue Step inserts zero rows and StatsTA1 step updates stats so that %Table(PSA_ACCTG_TA1) contains zero rows.  Cost Step is inserting 221331 rows into %Table(PSA_ACCTG_TA1), for which there is No Update Stats resulting in poor performance on step SharingB.

Also creating new index on PSA_ACCTG_TA1(PROCESS_INSTANCE, RESOURCE_ID)  helps improve the performance along with adding  UpdateStats mentioned above.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms