7 Replies Latest reply: Dec 26, 2012 9:34 AM by Programmer Analyst RSS

    Performance Issue


      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

          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
            Thank you...

            Any other Options
            • 3. Re: Performance Issue
              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

                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
                  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
                    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
                      Yes, Creating MV is the best option and it will better the performance. I can guarantee that.