How the SIZE Parameters Affect Oracle Business Intelligence Performance

§SORT_MEMORY_SIZE
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.
§ SORT_BUFFER_INCREMENT_SIZE
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.
§ VIRTUAL_TABLE_PAGE_SIZE
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.
USE_LONG_MONTH_NAMES
Specifies whether month names are returned as full names, such as JANUARY and FEBRUARY, or as three-letter abbreviations, such as JAN and FEB. Valid values are YES and NO. Specify YES to have month names returned as full names or NO to have months names returned as three-letter abbreviations. The default value is NO.
Example: USE_LONG_MONTH_NAMES = NO ;
USE_LONG_DAY_NAMES
Specifies whether day names are returned as full names, such as MONDAY and TUESDAY, or as three-letter abbreviations, such as MON and TUE. Valid values are YES and NO. Specify YES to have day names returned as full names or NO to have day names returned as three-letter abbreviations. The default value is NO.
Example: USE_LONG_DAY_NAMES = NO ;
UPPERCASE_USERNAME_FOR_INITBLOCK
Specifies whether the users are authenticated with case sensitivity. The default value is NO (or false internally). When it is set to YES, then all user names are changed to uppercase for authentication purposes in the SiebelAnalytics.rpd file. Otherwise, case is maintained.
Example: UPPERCASE_USERNAME_FOR_INITBLOCK = NO ;
AGGREGATE_PREFIX
Specifies the Domain Server Name for Aggregate Persistence. The prefix must be between 1 and 8 characters long and should not have any special characters ('_' is allowed).
Example: AGGREGATE_PREFIX = "SA_"  ;