R12.1 Reserve Summary/Detail Reports: Performance Issue: Populate Balance Report GT Data Request Hangs/Exhausts Temp Tablespace Errors With ORA-01652: unable to extend temp segment (Doc ID 1594295.1)

Last updated on JULY 18, 2016

Applies to:

Oracle Assets - Version 12.1.3 and later
Information in this document applies to any platform.
FASRSVES.rdf: Reserve Summary Report
FASRSVED.rdf: Reserve Detail Report
RXFAPOGT: Populate Balance Report GT Data
FACOSTS.rdf Cost Summary Report
FASCOSTD.rdf Cost Detail Report

Symptoms

The balances reports:

Reserve Summary Report (FASRSVES)

Reserve Detail Report (FASRSVED)

Cost Summary Report (FASCOSTS)

Cost Detail Report (FASCOSTD)

 are running for several hours, and consume large amounts of temporary tablespace, before the Populate Balance Report GT Data sub-requests exhaust all available Temp tablespace and end with the error;

Error
-----------------------
'ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1'

The SQL statement starting as follows, can be seen as the most time-consuming one in the tkprof;

INSERT INTO FA_BALANCES_REPORTS_ITF......

The following profile options are already set, to try to optimize the performance;

FA: Batch Size 10000
FA: Number of Parallel Requests 10

This resolved Cost Summary and Cost Detail reports, run for the same parameters, but reserve reports persist..

 

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