Month: November 2012

OBIEE Performance Tuning Part 2

For the second part of my OBIEE Tuning guide we will look at the instanceconfig.xml and changes that can be made here. As stated in my previous post I dont believe in cache when performance tuning as it can lead to false results, in that respect make sure cache is turned off in OBIEE Enterprise Manager, the below settings can be set by editing the instanceconfig.xml file.


<Table>
 <MaxCells – 200000>
 <MaxVisibleColumns - 1000>
 <MaxVisiblePages – 1000>
 <MaxVisibleRows – 100000>
 <MaxVisibleSections – 1000>
 <Table>
 <DefaultRowsDisplayed – 25>
 <DefaultRowsDisplayedInDownload – 2500>

<Pivot>
 <MaxCells – 200000>
 <MaxVisibleColumns - 1000>
 <MaxVisiblePages – 1000>
 <MaxVisibleRows – 100000>
 <MaxVisibleSections – 1000>
 <Pivot>
 <DefaultRowsDisplayed – 25>
 <DefaultRowsDisplayedInDownload – 2500>

<ThreadPoolDefaults>
 <ChartThreadPool>
 <MaxQueue>2048</MaxQueue>
 <MaxThreads>**</MaxThreads> (Max Threads no. cores x 8)
 </ChartThreadPool>
 </ThreadPoolDefaults>

<Cursors>
 <NewCursorWaitSeconds>10</NewCursorWaitSeconds>
 </Cursors>

<Catalog>
 <LockStaleSecsSoft>14400</LockStaleSecsSoft>
 <LockStaleSecsHard>14400</LockStaleSecsHard>
 <HashUserHomeDirectories>3</HashUserHomeDirectories>
 <UpgradeAndExit>false</UpgradeAndExit>
 </Catalog>

<BIEEHomeLists>
 <Enabled>false</Enabled>

With the above changed made I restarted all services including the database to ensure an accurate report running time was recorded.

Results:
Time after changing NQServer.ini Time with instanceconfig.xml changes
1. 1m54s 1m44s
2. 2m46s 2m56s
3. 5m13s 5m15s
4. 2m34s 2m42s

Looks like the reports performed slightly worse than before. Even with reports running worse I left the settings in as with my original post only a 10s or more differnece either way will count as an improvment.

Until next time I will show the changed made to the Data Warehouse itself.

Advertisements

OBIEE Performance Tuning Part 1

This is part one of my OBIEE Tuning guide. In this post we will look at some of the parameters that should be set at NQSConfig.ini in order to achieve maximum performance  First things first to run a valid test I ran the same report each time, after I had applied changes rebooted all database and OBIEE services to find time of completion I used the manage sessions section on the Analytics Console (Administration > Manage Sessions). With regards to what is a good and bad result if its not 10 seconds or more faster then I would say the change hasn’t had an effect on performance.

Note that all SQL should be tuned and it is said that OBIEE should be able to return a report within 1-10 seconds. My instance is not at this stage but you can still see a clear performance increase.

With my performance testing cache has been turned off as this will give invalid results, when performace tweaking caching should only be turned in the final stage.

Below changes were made to the NQSConfig.ini


MAX_SESSION_LIMIT = 5000
MAX_REQUEST_PER_SESSION_LIMIT = 5000
SERVER_THREAD_RANGE = 40-260
DB_GATEWAY_THREAD_RANGE = 40-520
INIT_BLOCK_CACHE_ENTRIES = 5000 # default is 20

The settings were saved, database and all BI services restarted.

The Report I was running contained 4 dashboards they will be listed as 1-4 below.

Original Report Timings                                                  Changes to NQSConfig.ini Timings
1.                  2m10s                                                                                                    1m54s
2.                  3m5s                                                                                                      2m46s
3.                  5m16s                                                                                                    5m13s
4.                  2m41s                                                                                                    2m34s

As you can see from the above there have been slight improvments in the report but one dashboard still runs in pretty much the same time.

My next post will be on changes made to instanceconfig.xml then finally the changes I made to the database.