This discussion is archived
6 Replies Latest reply: Aug 19, 2012 4:14 AM by Haree RSS

Performance improvement in OBIEE 11.1.1.5

Haree Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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