Performance issue

[ourhari1]

Hello,

We are currently working on a complex report that has to be given to the customer for signing. The report contains various sections (around 70+) each section gets data from different tables (most of them are 1:1). A usual PDF report comes around 80 to 100 pages. We have defined query subject for all of the tables; attached them to one list each; which includes showing / hiding rows, column data based on various conditions. The lists will have varied number of columns, sub lists etc.

However, this caused performance issue when running the reports. At times, we get out of memory issue (32bit).

After much study and R&D activities, we come up with the following solutions.
* Create a report table with generic column names (SECION_ID, TEXT1, TEXT2, NUM1, etc)
* Use a Stored Procedure to populate this table with appropriate data for all sections of the report.
   The conditions are applied at Procedure in order to minimize the effort on Cognos
* Created a master Query Subject for this table.
* Created various Query Subjects derived from this master QS for each sections
* Design the sections to get the value from the appropriate table columns.
* The conditional expressions were reduced to the least possible; since they are applied at PLSQL procedure.

The above approach did increase the performance of report generation better than previous. i.e., the reports are now taking upto 15 minutes instead of 'never generated'.

Also, not more than 2 users can generate the report at the same time (with different parameters). We had later introduced multiple dispatchers in order to support multiple reports generated in parallel. However, this did not reduce the time taken for a single report generation.

The average number of records for a report is around 2500 to 3000 only; but spanned across 70+ sections.

When I discussed with other Cognos developers in the floor, they said that this kind of performance is the maximum possible for such a report. Since I'm new to Cognos reports development, I'm unable to validate the same. But I strongly believe that it should not take so much time for 3K records, due to the following:
* All conditions, data massaging, etc are done at the stored procedure; which happens prior to Cognos.
* Only one table is involved with around 3K records

Could you please let me know, what kinds of settings needs to be looked in order to improve the performance of the report?

* Please accept my apologies for asking such a generic question. Since I'm new to Cognos reports development, I really unaware of settings and properties that impact performance of reports.

Thanks & regards,
Hari

[CognosPaul]

15 minutes seems excessive for a 100 page report, but it may be reasonable depending on the complexity.

The first thing to check is how the data is being pulled. You mentioned that the SP is populating a table. Is this a permanent table? Or is the SP generating a table on the fly then passing the data directly to Cognos? I strongly recommend running a trace to see exactly what query is being sent, and what is being received. If it's the latter, it may be possible that Cognos is running the SP over and over and over for each data container, then storing the results locally. If it's the former, then it may be possible that the model built over the table is needless complex. Check to see how many "selects" are inside a given query.

What happens if you break the report into smaller chunks? does each report page legitimately take 9 seconds to run? If so, then 15 minutes maybe a reasonable run time.

Which out of memory issue are you getting? Is it a Cognos error or an Oracle? Is it the java.lang.outOfMemory?

[ourhari1]

Hi PaulM,

The Java code executes the procedure which manipulates & populates the table (permanent Oracle table), and calls Cognos passing the report_id as a parameter. Cognos simply pulls data from the report table for all the sections. Each section is associated with a query subject which selects from the report table with a filter (section_id = 'id-for-that-section').

I have not tried to break the report into individual sections to see the time taken. I'll do it in the coming days and keep you posted.

For out of memory issue, I have got the following errors in the cogserver log file.
(1) 8848   BITSrv   6235   1   Audit.RTUsage.BITSrv            Failure   CCL-SRV-0513 The BIBusTKServer process ran out of memory.   <Messages><Message Name="CCLMessage" File="cclbitmsgs" Severity="Error" Nesting="0" ><MessageText>CCL-SRV-0513 The BIBusTKServer process ran out of memory.</MessageText></Message><Message Name="CCLMessageString" File="" Severity="Error" Nesting="0" ><MessageText>CCLOutOfMemoryError</MessageText></Message><Message Name="CCLMessageString" File="" Severity="Error" Nesting="0" ><MessageText>Out of memory</MessageText></Message></Messages>


(2) 3052   BITSrv   6235   1   Audit.RTUsage.BITSrv            Failure   CCL-SRV-0514 The BIBusTKServer process ran out of thread resource.   <Messages><Message Name="CCLMessage" File="cclbitmsgs" Severity="Error" Nesting="0" ><MessageText>CCL-SRV-0514 The BIBusTKServer process ran out of thread resource.</MessageText></Message><Message Name="CCLMessageString" File="" Severity="Error" Nesting="0" ><MessageText>CCLThreadResourceError</MessageText></Message><Message Name="CCLMessageString" File="" Severity="Error" Nesting="0" ><MessageText></MessageText></Message></Messages>

Looks that the error is on the Cognos side; not in Java / Oracle.
The cognos is running in Windows 2003 Standard, 8 GB RAM, 32 bit, 3 dispatchers.

Thanks,
Hari

[CognosPaul]

When you say 3 dispatchers, you mean other dedicated servers, yes?

How many CPUs do you have on the servers? How many report processes? how big is the final report?

My gut instinct is that it's pulling in the data fine, but it's running out of room to store the PDF before it finishes. As a test, try reducing the number of report processes in one of the dispatchers to 1, and run the report in that dispatcher. While it's running, keep your eye on the memory usage of the bibus, see if it's peaking anywhere.

[ourhari1]

Hi Paul,

We are having DEV environment, where 3 dispatchers are configured in 2 machines. Gateway and one dispatcher in one machine, and another two dispatchers in the second machine.

The servers are having 8 core CPU. The number of report processes are not manually changed; it is set by Cognos during installation (default settings).

The sample PDF report is 141 pages, occupying 590 KB.

I have monitored the BIBusTkServerMain process which started from around 60MB and and ended up at 853 MB when the report is finished. The memory consumption is gradual; going up, and did not come down any time. It consumed a consistent 8 to 11% CPU.

In an another instant when running more than two reports in parallel, the BIBusTKServerMain consumed upto 1557MB and then crashed.

Thanks,
Hari

[CognosPaul]

32 bit means Cognos can't utilize more than 2gb per bibus.

You might want to look into the tuning settings. This is a good start: http://www-01.ibm.com/support/docview.wss?uid=swg21513949&aid=1

Can you check how many queries are being thrown for this report? Run a trace.

Check your bin or temp directory for dmp files. Delete those.

Try also reducing Maximum Memory in MB in the configuration.

Unfortunately this is more of a systems issue, which is a little outside of my expertise. You might also want to raise this with IBM.

[ourhari1]

Thanks very much, Paul.

I understand from your answer that this issue is more towards configuration of the server; than the report itself.

I'll take it up to the infrastructure team and ask them to do the needful; based on the referenced link.

Thanks again.

Regards,
Hari

[CognosPaul]

You should still run a trace. If there are hundreds of queries being generated for this report, it might explain things.

[ourhari1]

Sure. We'll run the trace and see if the queries caused delay.

Thanks again for your valuable inputs.

Regards,
Hari

[ourhari1]

Hi Paul,

I tried to follow the instructions from http://www-01.ibm.com/support/docview.wss?uid=swg21455407
However, after running a sample report, I don't see any SQL statements logged to any of the log files.

Am I missing something here?

Please advise.

Thanks,
Hari

[CognosPaul]

Ask the DBA to help you out. A trace is something that's done on the database level.