Lately I get a lot of OBIEE “performance” tuning questions.
FI: Our client is downloading a report with 4 million rows and the server get’s very slow our even crashes……….
Or: Out client has a report with 2 million rows and 20 columns with a sort option on each column…….
First: I said it before and will say it again and again…..
OBIEE is not an ETL tool. It is NOT designed to handle large amounts of non aggregated data. It’s designed to handle dimensional structured data with sufficient aggregation tables.
So please please stop hammering in nails with an plush screwdriver!
Of course there are some tuning possibilities you might want to explore.
WORK_DIRECTORY_PATHS
From the OBIEE documentation:
Specifies one or more directories for temporary space.
Each directory listed needs to be an existing fully-qualified, writable directory pathname, with double quotes ( " ) surrounding the pathname. Specify mapped directories only. UNC path names and network mapped drives are allowed only if the service runs under a qualified user account. To change the account under which the service is running, see the corresponding topic in the Oracle Business Intelligence Enterprise Edition Deployment Guide.
Specify multiple directories with a comma separated list. Valid values are any fully qualified pathname to an existing, writable directory.
For optimum performance, temporary directories should reside on high performance storage devices.
If you specify more than one directory, they should reside on different drives.
Syntax: WORK_DIRECTORY_PATHS = "<full_directory_path_1>" [,"<full_directory_path_2>"{, "<full_directory_path_n>"}] ;
Example 1: WORK_DIRECTORY_PATHS = "C:\Temp" ;
Example 2: WORK_DIRECTORY_PATHS = "D:\temp", "F:\temp" ;
NOTE: Specifying more than one directory per drive does not improve performance because file I/O takes place through the same I/O controller. In general, specify only one directory per disk drive.
Specifying multiple directories on different drives improves the overall I/O throughput of the Oracle Business Intelligence Server because internally, the processing files are allocated using a roundrobin algorithm that balances the I/O load across the given disk drives.
Rule of thumb: Invest in fast drives, consider virtual / RAM drives.
SORT_MEMORY_SIZE
From the OBIEE documentation:
Specifies the maximum amount of memory to be used for each sort operation. Multiple operations can each use memory up to the value specified. The limit for SORT_MEMORY_SIZE is determined by the physical memory of the server machine and on the number of sort operations that might occur simultaneously. Specify KB for kilobytes, MB for megabytes, and no units for bytes.
Example: SORT_MEMORY_SIZE = 4 MB ;
The size specified by SORT_MEMORY_SIZE sets the upper limit on how large the sorting buffer can be in the Oracle Business Intelligence Server. When this limit is exceeded, data is sorted in allotments of the size set by SORT_MEMORY_SIZE and the sorted sets are merged together. For example, suppose SORT_MEMORY_SIZE is set to 4 MB and the size of the data to be sorted is 32 MB. The server performs the sort once per each 4 MB of data, for a total of eight sort operations,
and then merge the results into a single result set. This technique allows the Oracle Business Intelligence Server to sort data of indefinite size.
The merge process itself is generally not costly in terms of resources, but it does include a read and write of each result set in a temporary file. To reduce the time this takes, increase the SORT_MEMORY_SIZE. This parameter can be tuned over time by taking into consideration the data size of the query and the number of concurrent users.
Rule of thumb: Don’t go overboard with this value, 10mb for a hundred concurrent users is 1 Gb of server memory. Don’t let OBIEE push other processes into the swap memory of the OS.
SORT_BUFFER_INCREMENT_SIZE
From the documentation:
Specifies the increment that the sort memory size is increased by as more memory is needed. As more memory is required, the size increases by the value specified until it reaches the value of
SORT_MEMORY_SIZE.
Example: SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
This parameter defines the increment by which SORT_MEMORY_SIZE should be reached. For example, suppose SORT_MEMORY_SIZE is set to 4 MB and the data to be sorted is just one megabyte. As data is fed into the sort routine, the size of the sort buffer increases only by the increment size, rather than the full size allowed by SORT_MEMORY_SIZE. This mechanism allows the Oracle Business Intelligence Server to sort smaller result sets efficiently without wasting memory.
Rule of thumb: 5 – 10 % of the SORT_MEMORY_SIZE
VIRTUAL_TABLE_PAGE_SIZE
From the documentation:
Several operations—sort, join, union and database fetch—can require memory resources beyond those available to the Oracle Business Intelligence Server. To manage this condition, the server uses a virtual table management mechanism that provides a buffering scheme for processing these operations. When the amount of data exceeds the VIRTUAL_TABLE_PAGE_SIZE, the remaining data is buffered in a temporary file and placed in the virtual table as processing
continues. This mechanism supports dynamic memory sizes and ensures that any row can be obtained dynamically for processing queries.
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 NT 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 NT system buffers, as each I/O still goes through the system buffers
Rule of thumb: Trust the documentation, more then 256 KB is not necessary.
All the parameters are set in the NQSConfig.INI File
Till Next Time
