Improve OBIEE Performance by Increasing Java Heap Size

Increasing the Heap Size of the Weblogic JVM

1. Admin Server

2. Managed Server

Increasing JVM Size of Admin Server

JVM size of Admin server can be increased by changing Xmx and Xms values in setDomainEnv.sh which is located in $MW_HOME/user_projects/domains/<Domain>/bin

There are two blocks for XMX and XMS in setDomainEnv.sh. Depending up on type of JVM make changes in second block. Below I changed the value of XMX and XMS of 64 Bit SUN Java to 2048 and 1024.

XMS_SUN_64BIT=”256″

export XMS_SUN_64BIT

XMS_SUN_32BIT=”256″

export XMS_SUN_32BIT

XMX_SUN_64BIT=”512″

export XMX_SUN_64BIT

XMX_SUN_32BIT=”512″

export XMX_SUN_32BIT

XMS_JROCKIT_64BIT=”256″

export XMS_JROCKIT_64BIT

XMS_JROCKIT_32BIT=”256″

export XMS_JROCKIT_32BIT

XMX_JROCKIT_64BIT=”512″

export XMX_JROCKIT_64BIT

XMX_JROCKIT_32BIT=”512″

export XMX_JROCKIT_32BIT

XMS_SUN_64BIT=”1024″

export XMS_SUN_64BIT

XMS_SUN_32BIT=”512″

export XMS_SUN_32BIT

XMX_SUN_64BIT=”2048″

export XMX_SUN_64BIT

XMX_SUN_32BIT=”1024″

export XMX_SUN_32BIT

XMS_JROCKIT_64BIT=”512″

export XMS_JROCKIT_64BIT

XMS_JROCKIT_32BIT=”512″

export XMS_JROCKIT_32BIT

XMX_JROCKIT_64BIT=”1024″

export XMX_JROCKIT_64BIT

XMX_JROCKIT_32BIT=”1024″

export XMX_JROCKIT_32BIT

Increasing JVM Size of Managed Server

JVM size of Managed Server can be increased from Weblogic console. This can be achieved by adding Xmx and Xms values   start tab in Managed server shown as below

Servers>>Server-0>>Start Tab>> Arguments

OBIEE Performance Tuning by Connection Pool

 Connection Pools for Initialization Blocks

It is recommended that you create a dedicated connection pool for initialization blocks. This connection pool should not be used for queries.

Additionally, it is recommended that you isolate the connections pools for different types of initialization blocks. This also makes sure that authentication and login-specific initialization blocks do not slow down the login process. The following types should have separate connection pools:

• All authentication and login-specific initialization blocks such as language, externalized strings, and group assignments.
• All initialization blocks that set session variables.
• All initialization blocks that set repository variables. These initialization blocks should always be run using credentials with administrator privileges.

Be aware of the number of these initialization blocks, their scheduled refresh rate, and when they are scheduled to run. Typically, it would take an extreme case for this scenario to affect resources. For example, refresh rates set in minutes, greater than 15 initialization blocks that refresh concurrently, and a situation in which either of these scenarios could occur during prime user access time frames.

Initialization blocks should be designed so that the maximum number of Oracle BI Server variables may be assigned by each block. For example, if you have five variables, it is more efficient and less resource intensive to construct a single initialization block containing all five variables. When using one initialization block, the values are resolved with one call to the back end tables using the initialization string. Constructing five initialization blocks, one for each variable, would result in five calls to the back end tables for assignment.

If an initialization block fails for a particular connection pool during Oracle BI Server start-up, no more initialization blocks using that connection pool are processed. Instead, the connection pool is blacklisted and subsequent initialization blocks for that connection pool are skipped. This behavior ensures that the Oracle BI Server starts in a timely manner, even when a connection pool has a large number of associated initialization blocks or variables.

If this occurs, a message similar to the following appears in the server log:

[OracleBIServerComponent] [ERROR:1] [43143] Blacklisted connection pool

name_of_connection_pool

If you see this error, check the initialization blocks for the given connection pool to ensure they are correct.

Connection Pool settings for Performance Enhancement - General Tab

Property	Description
Maximum connections	The maximum number of connections allowed for this connection pool. The default is 10. This value should be determined by the database make and model and the configuration of the hardware for the computer on which the database runs, as well as the number of concurrent users who require access. Note: For deployments with Oracle BI Interactive Dashboards pages, consider estimating this value at 10% to 20% of the number of simultaneous users multiplied by the number of requests on a dashboard. This number can be adjusted based on usage. The total number of all connections in the repository should be less than 800. To estimate the maximum connections needed for a connection pool dedicated to an initialization block, you might use the number of users concurrently logged on during initialization block execution.
Enable connection pooling	When selected, allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.
Timeout	Specify the amount of time and in what increment (such as minutes) that a connection to the data source remains open after a request completes. During this time, new requests use this connection rather than open a new one (up to the number specified for the maximum connections). The time is reset after each completed connection request.
Use multithreaded connections	When this option is selected, the Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection (number of threads = maximum connections). Even if threads are idle, they consume memory. The parameter DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when the Oracle BI Server terminates idle threads. The lower number in the range is the number of threads that are kept open before the Oracle BI Server takes action. If the number of open threads exceeds the low point in the range, the Oracle BI Server terminates idle threads. For example, if DB_GATEWAY_THREAD_RANGE is set to 40-200 and 75 threads are open, the Oracle BI Server terminates any idle threads.

Aggregate Persistance Wizard in OBIEE

Steps for Creating Aggregate Tables in OBIEE

1. Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.
2. Go to ToolsàUtilities (from Administration tool menu bar), a dialog box will pop up with all available utilities.
3. Select Aggregate Persistence Wizard, and then click Execute Button. (See Figure Below)

4.       Now Select appropriate path where you need to generate Aggregate Table SQL.

Note : Click Generate DDL file for first time generation of Aggregate Table. (See Figure Below)

5. Click Next, to move to next page (Select Business Model & Measure Page)
6. In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.(See Figure Below)

7.       Click next, to move to next page (Select Dimensions & Levels).

8. Select appropriate level of dimension & check Use Surrogate Key.

9.       Click next, to move to next page (Select output Connection Pool, Container & Name).

10. Click next, to move to next page (Aggregate Definition).
11. Select I am Done (Radio Button).
12. Click Next.

13. Then Click Finish. Your Aggregate Table is Created Now & available at the path you specified in “Select File Location” Page.
14. To view generated script move to - C:\Agg\
15. Select The Aggregate Table Created & View the Code. Code for above process is mentioned below :

delete aggregates;  /* Delete code manually added to remove any pre-existing aggregate tables with the same name */

create aggregates

"ag_FACTINTERNETSAL"

 for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")

 at levels ("SALES"."Time"."Year" using_surrogate_key )

 using connection pool "Adventure Works"."Agg CP"

 in "Adventure Works"."AdventureWorksDW2008";

Now the script is ready, I run it using the “nqcmd.exe” utility in the /OracleBI/server/bin directory.

Steps for running Aggregate Script are mentioned below:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation.  All rights reserved. C:\Users\gaurav.mishra>cd\ C:\>cd oraclebi C:\OracleBI>cd server C:\OracleBI\server>cd bin C:\OracleBI\server\Bin>nqcmd.exe -u Administrator -p Administrator -d analyticsweb -s c:\Agg\agg.sql Where : u : UserName p : Password d : Domain s : SQL Query Path -------------------------------------------------------------------------------           Oracle BI Server           Copyright (c) 1997-2009 Oracle Corporation, All rights reserved delete aggregates delete aggregates Statement execute succeeded create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" Statement execute succeeded Processed: 2 queries

After getting successful aggregate script execution, you need to restart your BI Services.

Now Open your metadata Repository & you will see that new aggregate tables created and registered, and shown in red to show they’re aggregates.

Note : No change in presentation layer, as data is executed from logical table & physical table, so presentation layer remains unaffected.

OBIEE Performance Management

Performance Management in OBIEE for optimal Query Execution time

After the implementation of OBIEE project completes, then the real world issues datawarehouse team faces. The issues reported by end user & client are mostly based on performance issue or to say report execution time for most common business reports having huge data. Other than Table Indexing at database level, here are some of the common technique’s which may help you in metadata repository development (rpd) to overcome performance related issues.

Caching :
Caching / seeding important reports before working hours on daily basis, reduces query execution time and report executes in optimal time.

There can be various technique for caching, some of which are :

      Seeding cache from delivers / alerts (IBots)

Setting Event Pooling Table

Aggregate Table : (Aggregate Persistence Wizard)

Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard

Aggregate Table: Aggregate tables store precalculated measures that have been aggregated over a set of dimensional attributes.

Note : You must create aggregate table only in the situation when you can suppress huge number of rows of in the record of detail table.

The Ratio should be :

Detail Table : Aggregate Table >= 30 : 1

Turning off Loging :

Rpd -->Manage --> security --> users -->(Select appropriate user)--> right click --> properties

Logging can affect in performance of Oracle BI Server and can create large log files. By default logging level is 0.

Note : Logging can be used in problem solving and troubleshooting problematic queries. Do not change or update logging level until required.

Connection Pools – Best Practices for Performance Enhancement:

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage.
• Create a separate connection pool for execution of session variables.
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables.
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Constructing result using where clause :

With the help of where clause in content tab in logical table source you can filter the data (limits the rows) returned from database.Here, you can see the where clause and restrict the data using Expression Builder( marked in Blue).

Limiting Number of Initialization block:
Initialization block are the only means to initialize dynamic repository, system session and non-system session variables. We should be very careful about not to create too many init blocks.

As we know, In the case of system and non-system session variables, the initialization blocks get executed every time a user logs in to the server. And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.

Setting Query limits : 
We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition:

1) maximum number of rows a query can retrieve from a database.

2) maximum time a query can run on a database.

3) restricting access to a database during particular time periods from Analytic server.

Modelling Dimension Hierarchies Correctly : 
Dimension Hierarchy must be modelled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact, but ratio of number from one parent to child logical level should be accurate.

Setting NQSConfig Parameters : ( General Section )

Home : OracleBI\server\Config\ NqsConfig.ini (Open in Notepad to edit) à General Section à Edit :

• Sort Memory size
• Sort buffer Increment size
• Virtual Table Page Size