This discussion is archived
7 Replies Latest reply: Dec 26, 2012 7:34 AM by Programmer Analyst RSS

Performance Issue

980477 Newbie
Currently Being Moderated

I have a report which takes 5 hours to run...
This report has almost 20 columns among which four are calculated columns(i.e,. case statements almost 50 when.. then.. on each column) and has some filter applied on these calculated columns and on direct columns as well.

Some of these columns are fetching data from fact table which again has a very huge data...
How to handle these situation, Suggestions Pleaseee...

  • 1. Re: Performance Issue
    fiaz0419 Journeyer
    Currently Being Moderated

    Obiee is best suited for aggreagate data display. Beeter sollution is to create aggreagete table in the database then query from the aggrgate table instead of hitting table with huge data set.

    mark if helps,

  • 2. Re: Performance Issue
    980477 Newbie
    Currently Being Moderated
    Thank you...

    Any other Options
  • 3. Re: Performance Issue
    859155 Newbie
    Currently Being Moderated
    Try to insert result set in new table using a procedure .. and build report using the new table. Update the result set table by scheduling the procedure as daily/hourly.

    Please mark if it is useful!


    Edited by: 856152 on Dec 18, 2012 11:43 PM
  • 4. Re: Performance Issue
    Satya Ranki Reddy Expert
    Currently Being Moderated

    Try to implement cache mechanism.

    2. Try to use case statement in pd it self.

    3. Create Aggregation tables.

    4. Use Hints in Physical Layer.

    5. Try to enable cache in physical layer.

    The above methods some what you will get some how performance.

    Hope this help's

  • 5. Re: Performance Issue
    Programmer Analyst Newbie
    Currently Being Moderated
    There are couple of things I can suggest.

    1) Create materialized views for that face table in database and then try to use
    2) First test if its actually the issue with calculated measures or some other reason. You can do this by running the report by removing those 4 calculated columns.
    3) If possible try to split the report into two reports and do a union.

    mark helpful/correct if it helps
  • 6. Re: Performance Issue
    980477 Newbie
    Currently Being Moderated
    Hi All..

    Thanks for your valuable suggestions.. I found that, Calculated columns are not the issue, i have removed those calculated columns and checked but still the query runs long..

    And I have some limitations..

    Working on OLTP
    Dont have Cache Concept
    Tables(has lakhs of records for even a single month) used are normal Views.

    So the option for me would be creating an MV ??? or any other options ??
    If i create an MV then its gonna improve the query performance ??
  • 7. Re: Performance Issue
    Programmer Analyst Newbie
    Currently Being Moderated
    Yes, Creating MV is the best option and it will better the performance. I can guarantee that.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points