Essbase processes requests for database information (queries) from many sources. For example, Essbase processes queries from Oracle Essbase Spreadsheet Add-in and Report Writer. Essbase uses additional memory when it retrieves the data for these queries, especially when Essbase must perform dynamic calculations to retrieve the data. This section describes Essbase memory requirements for query processing.
Essbase is a multi-threaded application in which queries get assigned to threads. Threads are automatically created when Essbase is started. In general, a thread exists until you shut down Essbase Server. See Multithreading.
As Essbase processes queries, it cycles through available threads. For example, assume that 20 threads are available at startup. As each query is processed, Essbase assigns each succeeding query to the next sequential thread. After it has assigned the 20th thread, Essbase cycles back to the beginning, assigning the 21st query to the first thread.
While processing a query, a thread allocates some memory, and then releases most of it when the query is completed. Some memory is released to the operating system, and some is released to the dynamic calculator cache for the database being used. However, the thread holds on to a portion of the memory for possible use in processing subsequent queries. As a result, after a thread has processed its first query, the memory held by the thread is greater than it was when Essbase first started.
Essbase uses the maximum memory for query processing when both conditions are true:
In the example where 20 threads are available at startup, the maximum amount of memory is used for queries when at least 20 queries have been processed and the maximum number of simultaneous queries are in process.
Calculating the Maximum Additional Memory Required
Use the following variables when you calculate the formula in Estimating the Maximum Memory Usage for a Query Before and After Processing:
Determining the Total Number of Threads
The potential number of threads available is based on the number of licensed ports that are purchased. The actual number of threads available depends on settings that you define for the Agent or the server. Use the number of threads on the system as the value for Total#Threads in later calculations.
Estimating the Maximum Number of Concurrent Queries
Determine the maximum number of concurrent queries and use this value for Max#ConcQueries in later calculations. This value cannot exceed the value for Total#Threads.
Estimating the Maximum Memory Usage for a Query Before and After Processing
The memory usage of individual queries depends on the size of each query and the number of data blocks that Essbase needs to access to process each query. To estimate the memory usage, calculate the additional memory Essbase uses during processing and after processing each query.
Choose several queries that you expect to use the most memory. Consider queries that must process large numbers of members; for example, queries that perform range or rank processing.
To estimate the memory usage of a query:
Turn the dynamic calculator cache off by setting the essbase.cfg setting DYNCALCACHEMAXSIZE to 0 (zero). Turning off the dynamic calculator cache enables measurement of memory still held by a thread by ensuring that, after the query is complete, the memory used for blocks during dynamic calculations is released by the ESSSVR process to the operating system.
Using memory monitoring tools for the operating system, note the memory used by Essbase Server before processing the query. Use the value associated with the ESSSVR process.
Using memory monitoring tools for the operating system, note the peak memory usage of Essbase Server while the query is processed. This value is associated with the ESSSVR process.
Using memory monitoring tools for the operating system, after the query is completed, note the memory usage of Essbase. This value is associated with the ESSSVR process.
When you have completed the above calculations for all the relevant queries, compare all results to determine the following two values:
Insert the two values from step 7 into the formula in the following statement.
The additional memory required for data retrievals will not exceed the following:
Max#ConcQueries * MAXAdditionalMemDuringP + (Total#Threads - Max#ConcQueries) * MAXAdditionalMemAfterP
Write the result of this calculation, in bytes, to the cell labeled ME in Table 250, Worksheet: Estimating Memory Requirements for a Database.
Because this calculation method assumes that all of the concurrent queries are maximum-sized queries, the result may exceed your actual requirement. It is difficult to estimate the actual types of queries that will be run concurrently.
To adjust the memory used during queries, you can set values for the retrieval buffer and the retrieval sort buffer. See Setting the Retrieval Buffer Size and Setting the Retrieval Sort Buffer Size.