Oracle OBIEE Tuning Guide
Oracle OBIEE Tuning Guide
Oracle OBIEE Tuning Guide
Change Record
Date
Primary Author
Version
Change Reference
12/2012
Ahmed Awan
(Product Assurance Engineering)
3.0
Revised edition
Disclaimer:
All tuning information stated in this guide is only for orientation, every modification has
to be tested and its impact should be monitored and analyzed.
Before implementing any of the tuning settings, it is recommended to carry out end to
end performance testing that will also include to obtain baseline performance data for
the default configurations, make incremental changes to the tuning settings and then
collect performance data. Otherwise it may worse the system performance.
TABLE OF CONTENTS
INTRODUCTION................................................................................................................................ 3
1.0 PERFORMANCE OVERVIEW ................................................................................................ 3
1.1
1.2
1.3
INTRODUCTION
This document is written for people who monitor performance and tune the
components in a BI environment. It is assumed that readers know server
administration, Oracle Fusion Middleware (FMW), hardware performance tuning
fundamentals, web servers, java application servers and database.
1.0
PERFORMANCE OVERVIEW
This chapter discusses performance and tuning concepts for Oracle Business
Intelligence Enterprise Edition. This chapter contains the following sections:
1.1
1.2
1.3
1.1
1.2
Performance Terminology
Scalability
o System's ability to perform within specification under increasing
user load, data load and hardware expansion.
Latency
o Time between the issuing of a request and the time when the work
actually begins on the request.
Think time
o The time a real user pauses to think between actions.
Resource utilization
o A consumption metric, for example, the percent of CPU usage.
Response time
o A time metric, for example round-trip time it takes the server to
deliver a Web page.
1.3
Throughput
o A rate metric (requests per unit of time), for example, requests per
second, bits per second. For example, if an application can handle
20 customer requests simultaneously and each request takes one
second to process, this site has a potential throughput of 20
requests per second.
To optimize your deployment, you must understand the elements that influence
performance and scalability. A factor that dictates performance is called a key
performance driver. Knowing how the drivers behave in combination further
enhances your ability to deploy Oracle Business Intelligence Enterprise Edition
optimally, based on the unique requirements of each deployment.
-
Business process usage - Activities carried out by users in the normal flow of
your business cycle.
o Business process usage has three components:
User activityActivities available to users for reporting and
analysis.
2.0
2.2
2.3
2.4
2.5
2.6
2.7
Note: While the list in each of the above stated section is a useful tool in starting
your performance tuning, it is not meant to be comprehensive list of areas to tune.
You must monitor and track specific performance issues within your Business
Intelligence Enterprise Edition dashboards / reports design to understand where
tuning can improve performance.
2.1
This topic describes how to tune the Linux operating system to optimize the
performance of your Oracle Business Intelligence Enterprise Edition.
Linux Parameter
Default Value
Suggested Value
tcp_fin_timeout
By reducing the value of this entry, TCP/IP can release
closed connections faster, providing more resources for new
connections. Consult your Linux (OEL / RHEL / SLES)
documentation for more information on how to
permanently change the value for this parameter.
60
30
1024
1024
4096
For more information about Linux tuning, you should consult your Linux
documentation. Note the above TCP/kernel tunable parameters and their
corresponding values are provided as examples and rough guidelines only. You can
re-adjust these and other parameters based on actual system load, usage patterns
such as the number of concurrent users and sessions and so on.
2.1.2
This topic describes how to tune the Windows 2008 operating system to optimize
the performance of your Oracle Business Intelligence Enterprise Edition.
Windows 2008 Server Parameter
Default
Value
Suggested Value*
MaxUserPort
Under heavy loads it may be necessary to adjust the
MaxUserPort. This parameter determines the availability of
user ports requested by Oracle Business Intelligence
Enterprise Edition. Tip: By default, the start port is 49152,
and the default end port is 65535, this means there are 16383
usable dynamic ports.
*Use the following netsh command to configure start port
and the range:
16383
65532
Balanced
High Performance
Tune Windows 2008 (32bit) /3GB switch. Important Note: It is only recommended to enable /3GB,
if BI Presentation Services (32bit) crashes due to 2 GB process virtual memory limit.
Tip: The utility to manage the BCD Store, BCDEDIT.EXE.
/3GB switch applies to BI Presentation (sawserver.exe) process
i.e. compiled with the /LARGEADDRESSAWARE switch:
Default
process
user limit
(userVa) is
2048 MB.
To
set
/3GB
/USERVA=2900, you
would need to type
in
BCDEDIT.EXE
/Set
IncreaseUserVa
2900 in command
prompt (cmd).
Default Value
Suggested Value
TcpTimedWaitDelay
This parameter controls the amount of time the OS waits to
reclaim a port after an application closes a TCP connection,
has a default value of 4 minutes. During a heavy users load,
these limits may be exceeded resulting in an address in use:
connect exception.
Tip: In registry set this parameter using following:
HKLM\System\CurrentControlSet\Services\Tcpip\Parameters
Value: TcpTimedWaitDelay
Value Type: dword
Data: 30 (decimal)
240
30
MaxUserPort
The number of user-accessible ephemeral ports that can be
used to source outbound connections is configurable using
this parameter.
Tip: In registry set this parameter using following:
HKLM\System\CurrentControlSet\Services\Tcpip\Parameters
Value: MaxUserPort
Value Type: dword
Data: 65534 (decimal)
5000
65534
2.1.4
This topic describes how to tune the AIX (6.1 / 7.1) operating system to optimize the
performance of your Oracle Business Intelligence Enterprise Edition.
AIX Parameter
Default Value
Suggested Value
tcp_timewait
By reducing the value of this entry, TCP/IP can
release closed connections faster, providing more
resources for new connections.
14400
(2 hours)
600
(5 minutes)
150
10
150
40
1024
8192
16384
16384
tcp_keepidle
KeepAlive packet ensures that a connection stays in
an active/ESTABLISHED state.
Tip: Set using command no -r -o tcp_keepidle=600
tcp_keepintvl
Specifies the interval between packets that are sent to
validate the connection.
Tip: Set using command no -r -o tcp_keepintvl=10
tcp_keepinit
Specifies the
connection.
initial
timeout
value
for
TCP
Listen Backlog
Increase this to handle peak incoming connection
rates under heavy load.
Tip: Set using command no -r -o somaxconn=8192
User Limit Parameter: Tip: ulimit -a command displays all the ulimit limits.
time (seconds)
Default
unlimited
file (blocks)
Default
unlimited
data (kbytes)
Default
unlimited
stack (kbytes)
Default
4194304
memory (kbytes)
Default
unlimited
coredump (blocks)
Default
unlimited
nofiles (descriptors)
Default
10240
OBIEE libraries are shared among processes running on the same box. AIX will share common
memory for these libraries, if the "read-other" bit is turned on. It is recommended to change the
permissions for all OBIEE libraries and client libraries (database, ldap, etc).
Tip: How-to Detect: "/bin/genld -l" AIX command produces a list of the libraries used by each
process and the AIX memory address where they are loaded.
64-bit public segment is 9 (9xxxxxxxxxxxxxxx"); private segment is 8.
genld -l | grep -v ' d| 9' | sort +2 > (provides a list of privately loaded libraries)
Tip: How-to Repair: chmod o+r <libname>
AIX shared libraries will have a suffix of ".so" or ".a". Another technique is to change all libraries in
a selected directory to repair those that might not be currently loaded. The usual directories that
need repair are obiee code, httpd code and plugins, database client libraries and java.
chmod o+r /shr/dir/*.a /shr/dir/*.so
Set
the
following
environmental
variables
OracleBIPresentationServicesComponent process:
in
opmn.xml
file
for
running
AIXTHREAD_SCOPE=S
Tip: For OBIEE, you should set this value to S, which signifies system-wide contention scope (1:1).
AIXTHREAD_MNRATIO=1:1
AIXTHREAD_MUTEX_DEBUG=OFF
Tip: Maintains a list of active mutexes for use by the debugger.
AIXTHREAD_RWLOCK_DEBUG=OFF
Tip: For optimal performance, you should set the value of this thread-debug option to OFF.
AIXTHREAD_COND_DEBUG=OFF
Tip: Maintains a list of condition variables for use by the debugger
SPINLOOPTIME=1000
Tip: SPINLOOPTIME controls the number of times the system will retry a busy lock before
yielding to another process. The default value is 40. This should be increased to 1000 or higher
because a busy lock retry is inexpensive compared to the alternative. Use the tprof command to
determine if the check_lock routine has high CPU usage. If it does, you can increase the value even
more.
YIELDLOOPTIME=4
Tip: This variable has been shown to be effective in OBIEE, where multiple locks are in use. The
number of times to yield the processor before blocking on a busy lock is n. The default is 0
MALLOCMULTIHEAP=considersize,heaps:4
Multiple heaps are required so that a threaded application can have more than one thread issuing
malloc(), free(), and realloc() subroutine calls. With a single heap, all threads trying to do a
malloc(), free(), or realloc() call would be serialized (that is, only one call at a time). The result is a
serious impact on multi-processor machines.
MALLOCTYPE=buckets
MAXDATA
The BI System Components executable requires a lot of heap space for execution.
If the servers crash for insufficient storage, the size can be adjusted with this AIX environment
variable.
LDR_CNTRL=IGNOREUNLOAD@LOADPUBLIC@PREREAD_SHLIB@MAXDATA=0x200000000
Important Note: 8GB or 16GB would be a good starting point for 64-bit. Based on your system
resources availability you can set MAXDATA to higher value i.e. 32GB.
Tip: How to review the bmaxdata value
$ dump -X32_64 -o sawserver to check bmaxdata
DB2_SKIP_TRACE=1
For more information about AIX tuning, you should consult your AIX vendors
documentation. Note the above TCP/kernel tunable parameters and their
corresponding values are provided as examples and rough guidelines only. You can
adjust these and other parameters based on actual system load, usage patterns such
as the number of concurrent users and sessions and so on.
10
2.1.5
This topic describes how to tune the Solaris operating system to optimize the
performance of your Oracle Business Intelligence Enterprise Edition. Note: Solaris
default network tuning parameters should be sufficient in most cases. For heavy,
network centric workloads, its always best practice to increase the size of key TCPrelated parameters to improve system performance and reduce dropped
connections.
Sun Solaris Parameter
Default Value
Suggested Value
256
65535
60000
30000
49152
11
networks, you should make sure that the values are set
to at least 32K.
tcp_recv_hiwat
The tcp_xmit_hiwat and tcp_recv_hiwat parameters
control the default size of the send window and receive
window for each TCP connection, respectively. On
very fast networks, you should make sure that the
values are set to at least 32K.
49152
ipcl_conn_hash_size
This parameter controls the size of a hash table that
helps quickly locate the TCP sockets data structure in
the kernel. If the size is too small, it will result in long
hash chains in each bucket that force the operating
system into a linear search for the socket entry of
interest, and performance will suffer accordingly.
0
(in Solaris 10)
Connection backlog
This parameter controls the maximum allowable
number of completed connections waiting to return
from an accept call (that have completed the three-way
TCP connection handshake). You should increase this
parameter only if you notice that your system is
dropping connections.
128 (max_q)
1024 (max_q0)
1024 (max_q)
4096 (max_q0)
12
2.2
This topic describes how to tune the WebLogic Server to optimize the performance
of your Oracle Business Intelligence Enterprise Edition.
2.2.1 Tuning JDBC Data Sources
You can improve Oracle Business Intelligence Enterprise Edition performance and
stability by properly configuring the attributes in JDBC data sources in your
bifoundation_domain domain.
2.2.1.1 Increase the Number of Connection Pool
If JDBC (BI) data sources are running out of connections to the relational database
then you need to set the maximum number of connections to high value for the
following Business Intelligence Enterprise Edition data sources:
Important Note: Reason why the default value maximum capacity size is set to
lower value is because it is important to protect database resources. So make sure you
have enough resources on the database server (available memory is a constraint). If you
increase initial / maximum capacity to very high value, you may exceed the limit of
PROCESSES / OPEN_CURSORS on Oracle database server.
Data Source Name
Default Value
Suggested Value
bip_datasource
Initial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
mds-owsm
Initial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
EPMSystemRegistry
Initial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
Important Note: You need to determine the optimal value for the Maximum Capacity as
part of your pre-production performance testing. Once optimal values are determined
then set the value of Initial Capacity equal to the value for Maximum Capacity in
order to boost performance of JDBC in WebLogic Server web applications.
Tip: Here is the example on how to size open cursors value on Oracle database, if
you have a data source with 150 connections deployed on 1 server and if you use
default Statement Cache Size 10, you may open 1500 (150 x 1 x 10) cursors on your
database server for the cached statements.
Tip: To increase this value:
1. Log in to the WebLogic Server administration console.
2. Select your data source (for example mds-owsm), and then click on
Connection Pool tab, and then Maximum Capacity.
13
Default Value
Suggested Value*
bip_datasource
10
mds-owsm
10
EPMSystemRegistry
10
*The above setting to 0 is Oracle-specific. If need be statement caching can be set at driver level. The
latest Oracle JDBC 11.2 driver versions allow this, and will permit significant memory savings by
sharing/reclaiming memory if set at the driver level.
14
Some operations (for example, long running large reports, complex large
dashboards, Database / Essbase call, computation loop inside an application) can
get into an infinite loop. This can cause the thread to be stuck with that operation
for whatever time it takes to complete that operation.
Performance of the server can degrade significantly if many such threads get
stuck.
Tip: Changing the stuck threads values will only change the detection of stuck
threads. This does not mean that there are no stuck threads any more. Only the stuck
threads reporting by WebLogic Server changes. Meaning how long usually a specific
operation should take in OBIEE. If such an operation takes longer than 600 seconds
(default) then you should increase the stuck thread value to a higher one to prevent
the reporting of stuck threads. The performance is not impacted or controlled
through the stuck thread detection. WebLogic Server will not "delete" or "kill" a
thread in case it becomes stuck.
Use the below Stuck Threads suggested settings as a starting point, and then, after
careful testing of your long running transactions, adjust as needed.
Managed Servers
Default Value
Suggested Value
bi_server1
AdminServer
Tip: To configure above stuck thread detection behavior settings, you need to:
Click on BI Managed Server (bi_serverN) and then click on Tuning tab.
Then click on Overload tab for each BI Managed Server (bi_serverN).
15
Tip: If you see BI Managed Server JVM seg faults and then it restarts / hangs when a user
logs in, check the user group memberships in Active Directory. If the user belongs to groups
in a circular reference (group A belongs to group B that belongs to group A), without
additional configuration parameters, the JVM may segfaults. To workaround that set in
WebLogic Server administration console the "Max Group Membership Search Level" for the
AD provider. For more information, refer to Improving the Performance of WebLogic and
LDAP
Authentication
Providers
section
at
http://download.oracle.com/docs/cd/E12840_01/wls/docs103/secmanage/atn.html .
16
Tip: To disable wrapping for each JDBC data source (as stated in above table):
1. If you have not already done so, in the Change Center of the Administration
Console, click Lock & Edit.
2. In the Domain Structure tree, expand Services, then select Data Sources.
3. On the Summary of Data Sources page, click the data source name for
example mds-owsm.
4. Select the Configuration: Connection Pool tab.
5. Scroll down and click Advanced to show the advanced connection pool
options.
6. In Wrap Data Types, deselect the checkbox to disable wrapping.
7. Click Save.
8. To activate these changes, in the Change Center of the Administration
Console, click Activate Changes.
Important Note: This change does not take effect immediatelyit requires
the server be restarted.
17
2.3
Newly deployed web application server instances use default memory heap
settings, which are often too small to accommodate Oracle Business Intelligence
Enterprise Edition 64 bit requirements. Optimal JVM settings can be determined
only by close monitoring of application server performance under peak realistic
load.
2.3.1
In JRockit JVM (R28), the heap grows faster than before. The JVM also ensures that
the heap size grows up to the maximum Java heap size (-Xmx) before an
OutOfMemory error is thrown. In addition, the default value of the -Xmx option is
changed from 1 GB to 3 GB on 64-bit OS. Note the JRockit JVM auto shrinks the heap if it
is unused or if other applications require more physical memory.
Below table lists the memory settings that applies to all managed servers in BI
domain i.e. Admin Server, bi_serverN. Note the suggested value (depending on
available physical memory on each server), and the Oracle JRockit Java argument:
Setting
Suggested
Value
Java Argument*
Minimum heap
1024 MB
-Xms1024m
Maximum heap
2048 MB
-Xmx2048m
-XXcompressedRefs:size=32GB
-XXtlasize:min=8k,preferred=128k
18
*Warning: Make sure you have enough physical memory allocated / available on 64bit
servers and then use the above settings as a starting point, and after load testing, adjust as
needed. For a stable / an even performance over time, you should set the initial heap size (Xms) to the same value as the maximum heap size (-Xmx).
2.3.1.1 Common JRockit JVM issues seen with BI (i.e. bi_server1) managed
servers:
Startup Times Take Longer When Using JRockit JVM Versus Sun JVM: The
Oracle JRockit JVM is by default optimized for server use, which means that
the startup times can be longer in favor of high performance as soon as the
application is up and running. Moreover, as the application runs, the JRockit
JVM optimizes frequently called methods, improving the performance further.
When you run the JRockit JVM on a 64-bit system with a heap size less than 4
GB, if native OutOfMemory errors occurs despite memory being available only
then add the parameter -XXcompressedRefs:size=32GB in JVM startup options to
enable heap to grow up to 32 GB heap (practical limit of 25 GB). This will fix the
issue native OutOfMemory errors. Moreover heap size still be restricted till the
limits defined using -Xmx only benefit would be your heap will get the
capability to grow beyond 4 GB up to 25 GB due to increase in size of pointer to
32 bit address.
It is important to keep Oracle JRockit R28.1.x up to date with latest fix release
because the latest release provide fixes for security vulnerabilities, performance,
and other improvements of the Oracle JRockit R28.1.x.
19
2.3.2
Below table lists the memory settings that applies to all managed servers in BI
domain i.e. Admin Server, bi_serverN. Note the suggested value (depending on
available physical memory on each server), and the Oracle Sun Java argument.
Setting
Suggested Value
Java Argument*
Minimum heap
1024 MB
-Xms1024m
Maximum heap
2048 MB
-Xmx2048m
256 MB
-XX:PermSize=256m
512 MB
-XX:MaxPermSize=512m
512 MB
-XX:NewSize=512m
512 MB
-XX:MaxNewSize=512m
*Warning: Make sure you have enough physical memory allocated / available on
servers and then use the above settings as a starting point, and after load testing,
adjust as needed. For a stable / an even performance over time, you should set the
initial heap size (-Xms) to the same value as the maximum heap size (-Xmx).
20
2.3.3
Below table lists the memory settings that applies to all managed servers in BI
domain i.e. Admin Server, bi_serverN. Note the suggested value (depending on
available physical memory on each server), and the IBM Java argument.
Setting
Suggested Value
Java Argument*
Minimum heap
1024 MB
-Xms1024m
Maximum heap
2048 MB
-Xmx2048m
2m
-Xss2m
512m
-Xmns512m
512m
-Xmnx512m
-Xrs
-Xgcpolicy:gencon is a generational garbage collector for the IBM JVM. The generational
scheme attempts to achieve high throughput along with reduced garbage collection pause
times.
-Xverify:none With no parameters, enables the verifier. Note that this is the default; used
on its own, this option has no effect
*Warning: Make sure you have enough physical memory allocated / available on
servers and then use the above settings as a starting point, and after load testing,
adjust as needed. For a stable / an even performance over time, you should set the
initial heap size (-Xms) to the same value as the maximum heap size (-Xmx).
21
2.4
Newly created application server instances use default memory settings, which are
often too small to accommodate Oracle Business Intelligence Enterprise Edition
requirements. For 32bit JVM in order to allocate a heap of more than 1024 MB, you
need to use a 64bit JVM in order to allocate more than 1024 MB of maximum heap.
See section Tune 64bit Java Virtual Machines (JVM) in this guide.
2.4.1 Tuning 32bit JRockit JVM
This table lists the memory settings, the suggested value, and the Oracle JRockit
Java argument:
Setting
Suggested Value
Java Argument
Minimum heap
1024 MB
-Xms1024m
Maximum heap
1024 MB
-Xmx1024m
Important Note: By setting minimum heap size = maximum heap size gives you a
controlled environment where you get a good heap size right from the start and also
to minimize minor/major garbage collections.
Important Note: By default, JRockit JVM uses the dynamic garbage collection
strategy to optimize for throughput. For OBIEE JVM component, it is not
recommended to change to other available dynamic strategies.
2.4.2 Tuning 32bit Sun JVM
This table lists the memory settings, the suggested value, and the Suns Java
argument:
Setting
Suggested Value
Java Argument
Minimum heap
1024 MB
-Xms1024m
Maximum heap
1024 MB
-Xmx1024m
128 MB
-XX:PermSize=128m
256 MB
-XX:MaxPermSize=256m
Important Note: By setting minimum heap size = maximum heap size gives you a
controlled environment where you get a good heap size right from the start and also
to minimize minor/major garbage collections.
Important Note: Also for application servers that use Sun's Java Virtual Machine
(JVM), make sure that the size of the permanent generation heap must be increased
(as stated in above table). When the permanent area of the heap is too small, the JVM
will do a full garbage collection of the entire heap before resizing the permanent
area.
22
2.5
This topic describes how to tune the HTTP server to optimize the performance of your Oracle Business Intelligence
Enterprise Edition.
2.5.1 IIS 7.0+ HTTP Server Tuning
Parameters
Default Value
Suggested Value
20
1740
240
50
How-To:
Navigate to Path: C:\Windows\System32\Inetsrv\ , Run the Command:
appcmd.exe set config -section:system.applicationHost/webLimits /minBytesPerSecond:"50" /commit:apphost
23
Connection Timeout
Tip: In order to avoid random
disconnections for remote users, it is
recommended to increase this value.
120
7,200
How-To:
Navigate to Path: C:\Windows\System32\Inetsrv\, Run the Command:
appcmd.exe set config -section:system.applicationHost/sites "/[name='Default Web Site'].limits.connectionTimeout:02:00:00" /commit:apphost
= Default
= Default
=0
=0
= Default
= Default
= 1048576
= 1572864
Important Note: Use the above suggested settings as a starting point, and then, after careful testing, adjust as needed. To avoid potential
performance issues, values for IIS 7.0+ parameters should be set only after considering the nature of the workload and the system capacity
(i.e. CPU and memory).
24
2.5.2
Oracle HTTP Server uses directives in httpd.conf file. The directives for each MultiProcessing Module (MPM) type are defined in the httpd.conf file. The default MPM
type is Worker MPM.
Parameters
Default Value
Suggested Value
MaxKeepAliveRequests
100
KeepAliveTimeout
15
KeepAlive
On
On
Timeout
300
6000
= 150
= Default
=0
= 1048
= 1000
=0
=2
= 150
= Default
= 25
= 75
= 25
=0
=2
= 1024
= 1000
= 25
= 75
= 64
=0
= OS dependent
= OS dependent
= 8123292
= 8123292
</IfModule>
SendBufferSize
ReceiveBufferSize
Important Note: Only add this settings if your customer
have large volume of data to display e.g. 2000 to 10000
rows of data.
Important Note: Use the above suggested settings as a starting point, and then, after
careful testing, adjust as needed. To avoid potential performance issues, values for
OHS parameters should be set only after considering the nature of the workload and
the system capacity (i.e. CPU and memory).
25
2.6
Why use Web Server Compression / Caching for Oracle Business Intelligence?
1. Bandwidth Savings: Enabling HTTP compression can have a dramatic
improvement on the latency of responses. By compressing static files and
dynamic application responses, it will significantly reduce the remote (high
latency) user response time.
2. Improves request/response latency: Caching makes it possible to suppress the
payload of the HTTP reply using the 304 status code. Minimizing round trips over
the Web to revalidate cached items can make a huge difference in browser page load times.
Tip: Implement the following recommendations to optimize Internet Explorer 7.0 /
8.0; see below MS KB articles at:
In the IE browser navigate to: Tools -> Internet Options -> Advances -> Security and Locate the
Enable native XMLHTTP support option and check the check box.
HTTP
IE / Firefox
Decompression Layer
(gizp, deflate)
Web Browser Rendering
VPN (WAN)
HTTP
WebLogic Server
bi_server1:9704
26
2.6.1
This topic describes how to enable caching / compression in Oracle HTTP Server of
your Oracle Business Intelligence Enterprise Edition. Important Note: High load of
HTTP replies with 304 status code causes the OBIEE 11g UI to work slow in IE
browser 7 / 8. To resolve this issue, it is highly recommended to implement HTTP
caching and compression that will help to minimize the round trips over the Web to
revalidate cached items, can make a huge difference in browser page load times.
a. How to Enable Compression and Caching:
1. To implement HTTP compression / caching, install and configure Oracle HTTP
Server (OHS) 11.1.1.x for the bi_serverN Managed Servers (refer to "OBIEE Enterprise
Deployment Guide for Oracle Business Intelligence" document for details).
2. On the OHS machine, open the file HTTP Server configuration file (httpd.conf) for
editing. This file is located in the OHS installation directory. For example:
ORACLE_HOME/Oracle_WT1/instances/instance1/config/OHS/ohs1
3. In httpd.conf file, verify that the following directives are included and not
commented out:
LoadModule expires_module "${ORACLE_HOME}/ohs/modules/mod_expires.so
LoadModule deflate_module "${ORACLE_HOME}/ohs/modules/mod_deflate.so
4. Add the following lines in httpd.conf file below the directive LoadModule section to
compression / caching and restart the OHS:
#HTTP Compression
<IfModule mod_deflate.c>
SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
</IfModule>
27
Average time
(in sec)
Before
After
(Non-Compressed)
(Compressed)
300 seconds
20 seconds
93%
18,534,557
1,123,646
94%
350
355
HTTP/200: 483
HTTP/304: 525
HTTP/200: 15
Transaction Name
% Improvement
28
2.6.2
This topic describes how to enable compression and caching of your Oracle Business
Intelligence Enterprise Edition.
Important Note: High load of HTTP replies with 304 status code causes the OBIEE 11g UI
to work slow in IE browser 7 / 8. To resolve this issue, it is highly recommended to
implement HTTP caching and compression that will help to minimize the round trips over
the Web to revalidate cached items, can make a huge difference in browser page load times.
Tip: Refer to WebLogic Server documentation on how to install and configure the Microsoft
Internet Information Server Plug-In for IIS 7.0.
a. How to Enable Compression IIS 7.0+:
1.
HTTP Compression should be enabled on the OBIEE site level as performance may
hindered if enabled globally.
Tip: Run the following command line option to apply Dynamic Compression on OBIEE
web site level, note in below command replace the obiee website placeholder with
your web site name:
Navigate to Path: C:\Windows\System32\Inetsrv\
Run the command: Appcmd.exe set config
/doDynamicCompression:"True"
"obiee"
-section:system.webServer/urlCompression
Important Note: Raise the dynamic compression level above seven only if you need
more network bandwidth and have sufficient CPU capacity to handle the extra load.
3. Restart the IIS HTTP Server.
29
30
2. As stated below change the level of compression (in gzip, deflate) for dynamic
files to 7. Important Note: raise the dynamic compression level above seven only if
you need more network bandwidth and have sufficient CPU capacity to handle the
extra load.
Navigate to Path: C:\Inetpub\AdminScripts
Run the commands for gzip and deflate:
cscript adsutil.vbs set w3svc/filters/compression/gzip/HcDynamicCompressionLevel 7
cscript adsutil.vbs set w3svc/filters/compression/deflate/HcDynamicCompressionLevel 7
3. HTTP Compression should be enabled on the site level as performance may hindered
if enabled globally. Use two new metabase properties that are DoStaticCompression and
DoDynamicCompression, to enable compression at the individual OBIEE site.
Tip: Run the following command line options to enable compression on the OBIEE site
level, note in below commands to replace obiee site level with your web site name:
Navigate to Path: C:\Inetpub\AdminScripts
Run the commands: cscript adsutil.vbs set w3svc/1/root/obiee/DoStaticCompression true
Navigate to Path: C:\Inetpub\AdminScripts
Run the commands: cscript adsutil.vbs set w3svc/1/root/obiee/DoDynamicCompression true
31
32
3. Add the following lines in httpd.conf file below the directive LoadModule section to
compression / caching and restart the Apache HTTP Server:
#HTTP Compression
<IfModule mod_deflate.c>
SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
SetEnvIfNoCase Request_URI
</IfModule>
33
#HTTP Compression
Output fn="insert-filter" filter="http-compression" vary="false" compressionlevel="9" fragment_size="8096"
34
2.7
3.0
3.1
You can use following built-in BI metrics to monitor System Components (BI
processes) and WebLogic Server processes.
Tip:
Use Oracle Enterprise Manager (EM) URL to monitor end to end OBIEE real time
performance: http://<server>:7001/em
In Oracle Business Intelligence 11g, the perfmon URL is still valid to use i.e.
http://<server:port>/analytics/saw.dll?Perfmon
35
36
37
38
3.2
Counter
Guidance
Memory
Memory
Memory: Available
Mbytes
Memory
Memory: Page
Reads/sec
Processor
Processor: % Processor
Time_Total
Processor
Processor: % Processor
Time_(N)
Disk
Disk
PhysicalDisk: Average
Disk Queue Length
Disk
PhysicalDisk:
Disk sec/Read
Average
39
Disk
PhysicalDisk: Average
Disk sec/Write
Network
Network
Network Interface:
Packets Outbound
Errors
3.2.2
Product process
Counters / Guidance
40
3.3
This section outlines the key performance monitoring utilities/commands that can be
employed in the UNIX environment i.e. Linux, Solaris and AIX.
a. Linux
top - This program shows a lot of performance statistics that goes on with your
system. In the program, you can type:
M for memory usage information
P for CPU information
q to quit
It also shows you the memory usage, uptime, load average, CPU states, and
processes.
mpstat - Report processors related statistics
e.g. mpstat -P ALL 2 5
Display five reports of statistics for all processors at two second intervals.
vmstat - vmstat reports virtual memory statistics of process, virtual memory, disk,
trap, and CPU activity.
b. Oracle Solaris
iostat: Reports terminal and disk I/O activity and CPU utilization. The first line of
output is for the time period since boot & each subsequent line is
for the prior interval. Kernel maintains a number of counters to keep track of the
values.iostat's activity class options default to tdc (terminal, disk, and CPU).
vmstat: vmstat reports virtual memory statistics of process, virtual memory, disk,
trap, and CPU activity. Tip: Use nmon to monitor as vmstat only allows 4 disks to
be observed.
sar: Collects, reports, or saves system activity information. Example: sar -A -o <name
of the file> <interval> <count>. This file can be read by using the command sar f
<name of the file>.
prstat - Report statistics on active process stats.
41
c. AIX
vmstat Statistics about virtual memory and cpu/hard disk usage
Usage: vmstat [ -f ] [ -i ] [ -s ] [ -I ] [ -t ] [ -v ] [ PhysicalVolume ... ] [Interval [ Count ] ]
Example: vmstat hdisk0 hdisk1 5 - Displays the statistics of the disks every 5
seconds.
iostat - Reports Central Processing Unit (CPU) statistics and input/output statistics
for the entire system, adapters, TTY devices, disks and CD-ROMs.
Usage: iostat [ -s ] [ -a ] [ -d | -t ] [ -T ][ -m ][ Physical Volume ... ] [ Interval [Count ] ]
-a specifies adapter throughput report
-s specifies system throughput report
-t specifies tty/cpu report only
-T specifies time stamp
-d specifies drive report only
-m specifies statistics for paths
nmon: Collects, reports, or saves system activity information.
sar: Collects, reports, or saves system activity information.
Usage: /usr/sbin/sar [ { -A | [ -a ] [ -b ] [ -c ] [ -d ][ -k ] [ -m ] [ -q ] [ -r ] [-u ] [ -V ] [ -v ]
[ -w ] [ -y ] } ] [ -P ProcessorIdentifier, ... | ALL ] [-ehh [ :mm [ :ss ] ] ] [ -XFile ] [ -fFile
] [ -iSeconds ] [ -oFile ] [ -shh [:mm [ :ss ] ] ] [ Interval [ Number ] ]
Example: sar -A -o <name of the file> <interval> <count>. This file can be read by
using the command sar f <name of the file>.
lvmstat: Reports input/output statistics for logical partitions, logical volumes and
volume groups.
Usage: lvmstat { -l | -v } Name [ -e | -d ] [ -F ] [ -C ] [ -c Count ] [ -s ] [Interval [
Iterations ] ]
Tip: Your operating systems network monitoring tools can be used to monitor
utilization, verify that the network is not becoming a bottleneck, or detect packet
loss or other network performance issues. For details on network performance
monitoring, refer to your operating system documentation.
42
4.0
This chapter includes the following sections that provide a quick start for tuning
Oracle Business Intelligence system components (OBIPS, BI Server).
4.1
inside <JavaHost>:
<JavaHost>
<JobManager>
<MinThreads>100</MinThreads>
<MaxThreads>200</MaxThreads>
<MaxPendingJobs>200</MaxPendingJobs>
</JobManager>
</JavaHost>
c. In opmn.xml file located at ORACLE_INSTANCE/config/OPMN/opmn/ , for Javahost (64bit)
set heap size to -server Xmx2048M and for Javahost (32bit) set heap size to -server
Xmx1536M . Tip: In following section of opmn.xml file, increase the heap size to 2GB
for 64bit Javahost process:
<process-type id="OracleBIJavaHostComponent" module-id="CUSTOM">
<module-data>
<category id="start-parameters">
<data id="start-executable" value="$ORACLE_HOME/jdk/bin/java" />
<data id="start-args" value="-server Xmx2048M
. >
43
<MaxAgeMinutes>240</MaxAgeMinutes>
<MaxLastAccessedSeconds>14400</MaxLastAccessedSeconds>
</CatalogXml>
<Query>
<MaxEntries>5000</MaxEntries>
<!-- AbsoluteMaxEntries is the enforced maximum number of entries. When this maximum is reached -->
<!-- subsequent queries will fail until the maximum is no longer exceeded. -->
<AbsoluteMaxEntries>20000</AbsoluteMaxEntries>
<!-- CruiseEntries is amount of entries the OracleBI Presentation server tries to maintain in its cache. -->
<CruiseEntries>3000</CruiseEntries>
<!-- Forces the cache to attempt to remove an old entry when MaxEntries is exceeded. -->
<ForceLRU>true</ForceLRU>
</Query>
<Accounts>
<!-- Cleanup this cache every N minutes. -->
<CleanupFrequencyMinutes>14400</CleanupFrequencyMinutes>
</Accounts>
<AccountIndex>
<!-- Cleanup this cache every N minutes. -->
<CleanupFrequencyMinutes>14400</CleanupFrequencyMinutes>
</AccountIndex>
<CatalogAttributes>
<!-- Cleanup this cache every N minutes. -->
<CleanupFrequencyMinutes>14400</CleanupFrequencyMinutes>
</CatalogAttributes>
</Cache>
</ServerInstance>
44
45
Important Note: When you have multiple instances of a given Oracle Business
Intelligence component in the deployment, you should first configure shared files and
directories (i.e. Oracle BI Presentation Catalog, Global cache) for the clustered
components to use. Then, use Fusion Middleware Control to increase the number of
Presentation Services and Javahost components that run on the existing Oracle
Business Intelligence host (for more information on vertical scaling refer to
documentation library link @ http://docs.oracle.com/cd/E21764_01/bi.1111/e10541/cluster.htm#BABCABBC ).
Caution: Use the above suggested settings as a starting point, and then, after careful
performance testing, adjust as needed. To avoid potential performance issues, values for
OBIPS parameters should be set only after considering the nature of the workload and the
system capacity (i.e. CPU and memory).
46
4.2
Following are the important parameters to tune in NQSConfig.INI file and will
increase the performance of the BI system under high users load:
Initialization blocks
Repository init-blocks: Called once during server startup and refreshed after the refresh
interval as needed.
Session init-blocks: called for each new session; slow SQL can slow down login.
o Session init-blocks should be minimized and lazily loaded (deferred)
Previously it was not possible to defer row-wise init-blocks; restriction has
been lifted in 11.1.1.6 release.
Number of Init Blocks
For a given session, Init Block queries are executed serially and represent the per session memory
costs. Be judicious in creating Init Blocks. Verify that the same Init Block query is not already being
used in some other Init Block. Verify that cache is enabled for the Init Block and is being utilized
Init block connection pool and location of data source
As a practice, allocate a separate DB connection pool for Init Blocks [ensure enough connections are
available] in the OBIS repository. Init Block query response time will be high if the connection pool
points to a remote database.
Init Blocks Cache
The number of Init Block result sets that are cached with respect to row-wise initialization, can
be tuned.
NQSConfig.INI parameter:
[ SERVER ]
INIT_BLOCK_CACHE_ENTRIES = 5000
47
Maximum number of concurrent client connections (sessions) to OBI server can be tuned.
Each client request to OBI Server uses one SERVER_THREAD.
Each SERVER_THREAD uses 0 or more DB_GATEWAY_THREADs depending upon the
number of DB queries executed.
NQSConfig.INI parameters to be tuned:
[ SERVER ]
MAX_SESSION_LIMIT=5000
SERVER_THREAD_RANGE = 40-260;
DB_GATEWAY_THREAD_RANGE = 50-520;
One of the main advantages of query caching is to improve apparent query performance. It
might be valuable to seed the cache during off hours by running queries and hence causing the
server to cache their results.
The number of cache entries and disk size limit for the cache can be configured.
This configuration parameters can be set through by Oracle Business Intelligence Enterprise
Manager.
NQSConfig.INI parameters to be tuned :
[ CACHE ]
ENABLE = YES;
MAX_ROWS_PER_CACHE_ENTRY = 100000; // Too many rows in cache can slow down performance
# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager
MAX_CACHE_ENTRY_SIZE = 40 MB;
# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager
MAX_CACHE_ENTRIES = 5000;
Read-Only Mode
READ_ONLY_MODE = YES;
48
It helps to have the sort directory on a fast disk (e.g. a RAM disk)
NQSConfig.INI parameters to be tuned:
[GENERAL]
WORK_DIRECTORY_PATHS = "C:\Temp"; /* /dev/shm on Linux */
SORT_MEMORY_SIZE = 4 MB ;
SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
Increase VIRTUAL_TABLE_PAGE_SIZE
When VIRTUAL_TABLE_PAGE_SIZE is increased, I/O operations are reduced. Complex queries
may use 20 to 30 virtual tables, while simple queries may not even require virtual tables. The
default size of 128 KB is a reasonable size when one considers that the size for virtual paging in
Windows is 64 KB. This parameter can be tuned depending on the number of concurrent users and
the average query complexity. In general, setting the size higher than 256 KB does not yield a
corresponding increase in throughput due to the 64 KB size limit of Windows system buffers, as
each I/O still goes through the system buffers
NQSConfig.INI parameters to be tuned:
[GENERAL]
VIRTUAL_TABLE_PAGE_SIZE = 256 KB;
Tip: For more details on above parameters in NQSConfig.INI, refer to System Administrators
Guide for Oracle Business Intelligence.pdf
Important Note: You need to determine the optimal value for the parameter stated in above
table as part of your pre-production performance / load testing.
49
Important Note: When you have multiple instances of a given Oracle Business
Intelligence component in the deployment, you should first configure shared files and
directories for the clustered components to use. Then, use Fusion Middleware Control
to increase the number of BI Server components that run on the existing Oracle
Business Intelligence host (for more information on vertical scaling refer to
documentation library link @ http://docs.oracle.com/cd/E21764_01/bi.1111/e10541/cluster.htm#BABCABBC ).
4.2.2 Disable Oracle Client Library signal handling
The Oracle DB Client Libraries are distributed with BIEE 11g install. By default, the
library enables a signal handler, which will capture a call stack if the application
crashes. The signal handler is not needed, and definitely disruptive to BI Server
diagnostics. It is recommended to disable the signal handler.
Tip: sqlnet.ora is located at Oracle BI Home : $ORACLE_HOME/network/admin/sqlnet.ora
Add below line at the top of the sqlnet.ora file:
DIAG_SIGHANDLER_ENABLED=FALSE
50
51
5.0
This chapter includes the following sections that provide a quick start for tuning
OBIEE with the Essbase ASO cube as a data source. For more tunings options, refer
to Essbase DBA guide at http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?launch.html .
5.1
This topic describes how to tune the Essbase ASO to optimize the performance of
your applications. Following are the tuning areas to consider when tuning Essbase
ASO:
Aggregated Views
Essbase CFG Settings
Database level settings
MDX Optimizations
Understanding the Dynamic Calculations Log Messages
5.1.1 Aggregated Views
Perform first time Aggregations.
Important Note: The default selection of aggregate views proposed by Essbase
provides excellent performance.
Tip: The following process is recommended for defining and materializing
aggregations:
o After the outline is created or changed, load data values.
o Perform the default aggregation. Do not select specify a storage stopping
point option.
o Materialize the suggested aggregate views and save the default selection
in an aggregation script.
o Run the types of queries the aggregation is being designed for.
o If query time or aggregation time is too long, consider fine-tuning the
aggregation as stated below.
o Save the aggregation selection as an aggregation script.
52
100
25
Important Note: Use the above settings as a starting point, and then, after careful testing,
adjust as needed. AGENTTHREADS default value is 5. It is strongly recommended that you
use this default value if you are running Essbase a 32-bit platform. If your Essbase Server
computer freezes while running multiple reports at the same time, increase the value of
SERVERTHREADS by one for each report you run.
; NETDELAY
; NETRETRYCOUNT
Important Note*: It is recommended to set the timeout to default values, which is 2 minutes.
If need be, the max amount of timeout that is recommended should not be above 5 minutes.
The reason is because there have been a lot of customers complaining about Essbase
hangs. However, technically there is no hang until this hang exceeds the
NetDelay/NetRetryCount settings.
Tip: How to calculate the timeouts from these settings, i.e. NetDelay (200) is in
milliseconds so divide by 1000 then multiple by NetRetryCount (600) to get timeout
in seconds, for example: (200/1000) * 600 = 120 seconds (120/60 = 2 minutes)
53
54
The saving obtained from defining non empty behaviors can be very significant.
Savings increase with the complexity of the formula and sparseness of the data.
55
Comments welcome
Your comments are important to us!
We want our Best Practices Guide for Infrastructure Tuning Oracle Business
Intelligence Enterprise Edition to be as helpful as possible. Send us your comments
about this at in an email to Product Assurance team @ https://blogs.oracle.com/pa/entry/test
Best Practices Guide for Tuning Oracle Business Intelligence Enterprise Edition
Revised edition: December 2012
Author: Ahmed Shahzad Awan
Oracle EPM/BI Product Development Product Assurance (PA) @ http://blogs.oracle.com/pa
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
Worldwide Inquiries:
Phone: +1.650.506.7000
Fax: +1.650.506.7200
www.Oracle.com
Oracle is a registered trademark of Oracle Corporation. Various
product and service names referenced herein may be trademarks
of Oracle Corporation. All other product and service names
mentioned may be trademarks of their respective owners.
Copyright 2012 Oracle Corporation
All rights reserved.
56