6 Replies Latest reply: Aug 19, 2012 6:14 AM by Haree RSS

    Performance improvement in OBIEE 11.1.1.5

    Haree
      Hi all,

      In OBIEE 11.1.1.5 reports takes long time to load , Kindly provide me some performance improvement guides.

      Thanks,
      Haree.
        • 1. Re: Performance improvement in OBIEE 11.1.1.5
          Satya Ranki Reddy
          Hi Haree,

          Steps to improve the performance.

          1. implement caching mechanism
          2. use aggregates
          3. use aggregate navigation
          4. limit the number of initialisation blocks
          5. turn off logging
          6. carry out calculations in database
          7. use materialized views if possible
          8. use database hints
          9. alter the NQSONFIG.ini parameters

          Note:calculate all the aggregates in the Repository it self and Create a Fast Refresh for MV(Materialized views).
          and you can also do one thing you can schedule an IBOT to run the report every 1 hour or some thing so that the report data will be cached and when the user runs the report the BI Server extracts the data from Cache

          This is the latest version for OBIEE11g.

          http://blogs.oracle.com/pa/resource/Oracle_OBIEE_Tuning_Guide.pdf

          Report level:

          1. Enable cache -- change nqsconfig instead of NO change to YES.

          2. GO--> Physical layer --> right click table--> properties --> check cacheable.

          3. Try to implement Aggregate mechanism.

          4.Create Index/Partition in Database level.

          There are multiple other ways to fine tune reports from OBIEE side itself:

          1) You can check for your measures granularity in reports and have level base measures created in RPD using OBIEE utility.
          http://www.rittmanmead.com/2007/10/using-the-obiee-aggregate-persistence-wizard/
          This will pick your aggr tables and not detailed tables.

          2) You can use Caching Seeding options. Using ibot or Using NQCMD command utility
          http://www.artofbi.com/index.php/2010/03/obiee-ibots-obi-caching-strategy-with-seeding-cache/
          http://satyaobieesolutions.blogspot.in/2012/07/different-to-manage-cache-in-obiee-one.html
          OR
          http://hiteshbiblog.blogspot.com/2010/08/obiee-schedule-purge-and-re-build-of.html

          Using one of the above 2 methods, you can fine tune your reports and reduce the query time.

          Also, on a safer side, just take the physical SQL from log and run it directly on DB to see the time taken and check for the explain plan with the help of a DBA.

          Hope this help's

          Thanks,
          Satya

          Edited by: Satya Ranki Reddy on Aug 12, 2012 7:39 PM

          Edited by: Satya Ranki Reddy on Aug 12, 2012 8:12 PM

          Edited by: Satya Ranki Reddy on Aug 12, 2012 8:20 PM
          • 2. Re: Performance improvement in OBIEE 11.1.1.5
            Haree
            Thanks for your reply Satya.

            I followed this link

            http://hiteshbiblog.blogspot.com/2010/08/obiee-schedule-purge-and-re-build-of.html and i created a PurgeCache.txt file in D: and when i issue the following command,

            NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

            It throws an error as,

            Call SAPurgeAllCache()
            Call SAPurgeAllCache()
            [6576][State: S1000] [Oracle][ODBC][Ora]*ORA-06576: not a valid function or procedure name*

            Statement execute failed

            Processed: 1 queries
            Encountered 1 errors.

            Where I have to create a procedure and what does it contain ?

            Kindly help me on this.

            Thanks,
            Haree
            • 3. Re: Performance improvement in OBIEE 11.1.1.5
              Christian Berg
              Steps to improve the performance.
              1. implement caching mechanism >

              I love how caching is number 1 on that list. Let's treat symptoms rather than the cause of performance problems! http://www.linkedin.com/groupItem?view=&gid=47239&type=member&item=124262986&trk=group_search_item_list-0-b-ttl&goback=.gna_47239
              • 4. Re: Performance improvement in OBIEE 11.1.1.5
                Satya Ranki Reddy
                Hi Haree,

                In execution syntax You have mentioned path is wrong.

                You said purge file had stored in D drive but in sytax you have mention C Drive -- Please cross check this.

                Check the below steps:-

                Via ODBC functions

                Note : Replace OracleBI_Home by your own directory.

                Create a file PurgeAllCache.sql and add the SAPurgeAllCache ODBC Function :

                {call SAPurgeAllCache()}; And save it in a directory (for instance OracleBI_Home\Cache).

                Then from a command dos, call nqcmd with this statement :

                OracleBI_Home\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Password
                -s "OracleBI_Home\Cache\PurgeAllCache.sql" -o "OracleBI_Home\Cache\PurgeAllCache.log"You will retrieve then in the PurgeAllCache.log :

                {call SAPurgeAllCache()}
                ----------------------------------------------------------------
                RESULT_CODE RESULT_MESSAGE
                ----------------------------------------------------------------
                1 [59118] Operation SAPurgeAllCache succeeded!
                ----------------------------------------------------------------
                Row count: 1
                ----------------------------------------------------------------
                Processed: 1 queries

                Hope this help's

                Thanks,
                Satya
                • 5. Re: Performance improvement in OBIEE 11.1.1.5
                  Haree
                  Hi Satya,

                  I have created a file called PurgeCache.sql located under D:\OraHome\Middlleware\Oracle_BI1\Cache .
                  PurgeCache.sql file contains the following code:

                  Call SAPurgeAllCache();

                  Then in command prompt, I issued the following command

                  D:\OraHome\Middlleware\Oracle_BI1\bifoundation\server\bin>nqcmd.exe -d DATA_SOURCE_NAME -u USERNAME -p PASSWORD -s "D:\OraHome\Middlleware\Oracle_BI1\Cache\PurgeCache.sql" -o "D:\OraHome\Middlleware\Oracle_BI1\Cache\PurgeCache.log"
                  • 6. Re: Performance improvement in OBIEE 11.1.1.5
                    Haree
                    Hi Satya,

                    I have created a file called PurgeCache.sql located under D:\OraHome\Middlleware\Oracle_BI1\Cache .
                    PurgeCache.sql file contains the following code:

                    Call SAPurgeAllCache();

                    Then in command prompt, I issued the following command

                    D:\OraHome\Middlleware\Oracle_BI1\bifoundation\server\bin>nqcmd.exe -d DATA_SOURCE_NAME -u USERNAME -p PASSWORD -s "D:\OraHome\Middlleware\Oracle_BI1\Cache\PurgeCache.sql" -o "D:\OraHome\Middlleware\Oracle_BI1\Cache\PurgeCache.log"

                    Then Log file contains the following Error:

                    Call SAPurgeAllCache()
                    Call SAPurgeAllCache()
                    *[6576][State: S1000] [Oracle][ODBC][Ora]ORA-06576: not a valid function or procedure name*

                    Statement execute failed


                    Processed: 1 queries
                    Encountered 1  errors


                    Kindly help me out of this.

                    Thanks,
                    Haree.