SAP Basis Tech Supports

Symptom
You get an “out of memory” error when executing a query on BIA.
Other terms
BIA, BI Accelerator, huge results, error code 4920, error code 6952) possible limitation of resultset, out of memory
Reason and Prerequisites
In the BIA standard configuration the maximum size of the overall resultset of a BIA query is limited to the following values:Beginning with BIA 7.0 Revision 48:
max_cells_one_index = 40000000 (maximum number of cells)Until BIA 7.0 Revision 47:
max_result_size_merge = 3.000.000 (maximum number of lines)
max_column_number_merge = 3 (maximum number of columns).
When these limits are reached by a resultset an “out of memory” error (e.g. error code 4920 or 6952) occurs.You can customize the maximum size of the overall resultset of a BIA query in the TREX configuration file TREXIndexServer.ini (see SAP_RETRIEVAL_PATH) for each TREX index server of your BIA blade landscape.Solution
Beginning with BIA 7.0 Revision 48:
Beginning with BIA Revision 48 you restrict the size of the resultset of a BIA query by setting the parameter”max_cells_one_index”in the TREXIndexServer.ini file in the section [olap].
Default settings is:
max_cells_one_index = 40000000
Parameter max_cells_one_index is the product of number of lines and number of columns.Until BIA 7.0 Revision 47:
Until BIA 7. 0 Revision 47 you restrict the size of the resultset of a BIA query by customizing the following two groups of parameters in the TREXIndexServer.ini file in the section [olap]:Parameters “max_result_size_merge” (default value: 3000000) and “max_column_number_merge” (default value: 3). Those two parameters restrict the size of the endresult of the query in TREX index server.Parameters “max_result_size_one_index” (default value: 1300000) and “max_column_number_one_index” (default value: 3). Those two parameters restrict the size of temporary data structures, which would be generated during the execution of join operations.
In case of an error the TREXIndexServer trace file shows the parameter values at which the error message occurred. According to these values you can adjust the parameters so that the query result set fits into memory.
Note the following before changing the settings:Be aware that the TrexRfcServer, the RFC connection or the application server can stop working if the resultset does not fit into the memory of the service!It is strongly recommended to set the parameters to the same value in the respective TREXIndexServer.ini configuration file oneachTREX index server of your BIA landscape.The bigger the size of the resultset, the longer the response time of the BIA will be due to longer transport times. So before you raise the limit of the resultset, consider a redesign of the BIA query. Normally you can split the query into several queries with smaller resultsets using more filter criterias in the queries. This also leads to more readable results for the enduser.
After changing the settings in the the TREXIndexServer.ini you have to restart the TREX index server to let the changes in the TREXIndexServer.ini take effect.
Note:
In trace TrexIndexServerAlert.trc you will find the number of rows (-> “hash table size”) and the number of columns (-> “key figures”) of the aborted query. If you want to raise paramter max_cells_one_index in a way that query does not abort you must define max_cells_one_index at least greater than the product of rows and columns. A typical entry in the trace would be
AggregateCalculator returning out of memory with hash table size 3956689, key figures 15
If you do not find that entry in the trace then the execution of the query really ran out of memory and the query will abort anytime independend of value of max_cells_one_index.