apps-infra

    Forum Stats

  • 3,873,880 Users
  • 2,266,627 Discussions
  • 7,911,650 Comments

Discussions

Performance issues when building a large crosstab

534936
534936 Member Posts: 8
edited Oct 10, 2008 11:55AM in Discoverer
Hi,

I have a performance issues when building a large crosstab.

So here is my situation :

We are using Discoverer Plus 10.1.2.45.46c
Oracle Database 9.2.0.6 on Windows 2003
OAS 10.1.0.3
1.5 Gb of RAM on my PC

- I scheduled a Workbook with only 1 Worksheet that takes 2 minutes to execute. The table generated in the database to store the results has 19 columns and around 225000 rows (I know it's a lot but it's what the client needs).

- When I open the workbook it takes around 2 hours to retrieve the data. Data is retrieved in groups of 1000 and at the beginning, the rows are fetched much faster than at the end. It also takes 15 more minutes to build the crosstab. So all in all, it takes 2 hours and 15 minutes for the crosstab to be displayed on my screen.

Can anyone explain to me :
- why does it take so long ?
- What can I do to improve the the performance other than changing the query or displaying the results in a regular tab ? Is there some tuning I can do on either the database of the Oracle Application Server ?

Note : I duplicated the Worksheet in a simple tab and scheduled it. The table generated in the DB to store the results is identical and it takes only 2-3 minutes to open this worksheet and extract all rows.


Thank You !

Mary

Best Answer

  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    edited Oct 6, 2008 4:38PM Answer ✓
    Hi Mary
    If you have 225,000 rows in your base worksheet then it will take a long time to produce a crosstab. The reason is that Discoverer cannot calculate how many buckets it needs until it has read all of the data. I can almost guarantee that with 225,000 rows to read and manipulate that you are running out of memory.

    You might be better suited to have someone create and populate a table with the results that you need rather than trying to get Discoverer to calculate the crosstab values on the fly. If the end result of the crosstab is a few rows of aggregated data then that is what should be in your table. The advantage of using a table and some SQL (or PL/SQL) is that you will not be using your local machine during the aggregation / sorting phase. Remember a crosstab also sorts based on the values in the left-hand column(s), so the more columns you have the more aggregations (buckets) you will have and the more sorting that is required. Using a table you can index or even partition the results which will make for much faster retrieval.

    As an FYI, Oracle recommends, confirmed by myself during exhaustive testing, not to build large crosstabs because of the performance hit that you will have. Tables are much more efficient because you can pull back x rows at a time. You cannot do this with a crosstab and ALL values have to be read before anything gets displayed.

    Does this help?
    Regards
    Michael

Answers

  • user634293
    user634293 Member Posts: 331
    Hi Mary,


    Discoverer 10.1.2.45.46c is 10gr2 base release, there are now 3 patchset available. So i suggest to install patchset 2 and the last cumulative patch. where a lot of bugs are fixed. Your report is not too big to take all this time.Also you can try to disable QPP in the pref.txt file

    Thanks
    Mohamed
    user634293
  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    edited Oct 6, 2008 4:38PM Answer ✓
    Hi Mary
    If you have 225,000 rows in your base worksheet then it will take a long time to produce a crosstab. The reason is that Discoverer cannot calculate how many buckets it needs until it has read all of the data. I can almost guarantee that with 225,000 rows to read and manipulate that you are running out of memory.

    You might be better suited to have someone create and populate a table with the results that you need rather than trying to get Discoverer to calculate the crosstab values on the fly. If the end result of the crosstab is a few rows of aggregated data then that is what should be in your table. The advantage of using a table and some SQL (or PL/SQL) is that you will not be using your local machine during the aggregation / sorting phase. Remember a crosstab also sorts based on the values in the left-hand column(s), so the more columns you have the more aggregations (buckets) you will have and the more sorting that is required. Using a table you can index or even partition the results which will make for much faster retrieval.

    As an FYI, Oracle recommends, confirmed by myself during exhaustive testing, not to build large crosstabs because of the performance hit that you will have. Tables are much more efficient because you can pull back x rows at a time. You cannot do this with a crosstab and ALL values have to be read before anything gets displayed.

    Does this help?
    Regards
    Michael
  • 534936
    534936 Member Posts: 8
    Hello Michael,

    Thank you for your answer.

    When you say that I am probably running out of memory, whay memory exactly are you talking about ?

    Thanks again,

    Mary
  • Hi Mary
    Memory is consumed in two places, on the application server and on your local PC.

    On the server my recommendation to companies that I consult to is to have a ratio of 4:1 between GB of memory and the number of CPUs. Thus, if you have a 2 CPU server you should have 8 GB RAM, and if you have a 4 CPU machine you should have 16 GB RAM. I find many companies skimp on the memory while having reasonable CPU resources. The single-most important resource for Discoverer is memory. The more you can allocate the better will be your performance.

    On the local machine, I recommend at least 1 GB, but 2 GB if you can help it.

    Best wishes
    Michael
This discussion has been closed.
apps-infra