R10211B - Performance Issues

[natalieroberge]

Hello Board
Im wondering if you could help me with an issue Im having with my R5510211B report. I copied R10211B and want to run the report with a new data sequencing. Instead of CO.OBJ.SUB i have set it to OBJ.SUB. I am getting the correct numbers, that I am looking for, but my problem is that is takes too long to wrong ie 1 hour!!! If I run per company and get an acceptable 3 minutes.
Any ideas on how to improve the time?
Version E910

Thanks!

 

[BOster]

Is this using the financial report writer?

 

[natalieroberge]

yes. It was a straight copy of R10211B

 

[78aspide78]

Turn on logging, find where ube spend a lot of time (may be the initial query) identify missing index (may be OBJ.SUB without the CO) add the index.

 

[DBohner-(db)]

Your friends are:
. Logging on
. Performance Workbench or Patwel's JDETrace
. An SQL Analyzer (every DB has some form of one)

Probable Indexing... just a hunch.

(db)

 

[BOster]

This is a complete shot in the dark.

If you have all ready taken other people's advice and turned on debug logging and optionally analyzed the log using performance workbench look to see if the function UserDefinedDateTitle is called and if performance workbench shows that it is taking a lot of total time to process. Key word "total" as in total time for ALL calls.

In 9.0 I found a bug in this function that was causing some of our custom financial reports to run for hours. However, our custom financial reports were brought over from Xe, so a fresh copy of a pristine financial report may not be implemented the same way (i.e. it may not call UserDefinedDateTitle). It appears Oracle realized that the financial report writer would call UserDefinedDateTitle about a billion times and that all the round trips to the database would cause performance issues so they tried to put in a performance fix. Unfortunately there was a bug in their performance fix that basically nullified the performance gain. Once I fixed the small bug, our financial reports that were taking hours to run started running in 15-20 minutes or less.

Again, I think this may have been due to Xe reports we migrated over to 9.0 so a copy of a new pristine financial report may not make use of the problematic BSFN, but its something to look at.