Setting Data Model Properties to Prevent Memory Errors
You can use the different data model properties to help prevent memory errors in your system.
These properties include:
Query Time Out
The Query Time out property specifies the time limit in seconds within which the database must execute SQL statements.
BI Publisher provides a mechanism to set user preferred query time out at the data model level. The default value is 600 seconds.
Queries that cannot execute under 600 seconds are not well-optimized. Your DBA or a performance expert should analyze the query for further tuning.
Increasing the time out value risks Stuck WebLogic Server threads. Do not raise the value unless all other optimizations and alternatives have been utilized.
DB Fetch Size
The Database Fetch Size property specifies the number of rows that are fetched from the database at a time.
This setting can be overridden at the data model level by setting the Database Fetch Size in the general properties of the data model.
Setting the value higher reduces the number of round trips to the database but consumes more memory. Consider the number of elements in the data model before changing this property.
BI Publisher recommends setting the property Auto DB fetch size to true so that the system calculates the fetch size at run time.
Scalable Mode
When the Scalable mode property is on, BI Publisher uses the temp file system to generate data. The data processor uses the least amount of memory.
This scalable mode property can be set at the data model level and the instance level. The data model setting overrides the instance value.
You can set the instance value by expanding the nodes for Administrator, Runtime Properties , and then Data Model:
The instance value can be overridden by Data model setting shown here:
The following table details the expected results for the possible on/off settings at each level:
| Scalable Mode Instance Value | Scalable Mode Data Model Value | Expected Result |
|---|---|---|
|
On |
Instance |
On |
|
Off |
Instance |
Off |
|
On |
On |
On |
|
On |
Off |
Off |
|
Off |
On |
On |
|
Off |
Off |
Off |
SQL Pruning
SQL pruning enhances performance by fetching only the columns that are used in the report layout/template.
Columns that are defined in the query but are not used in the report are not fetched. This improves query fetch size and reduces JDBC rowset memory.
Note that this feature does not alter the where clause but instead wraps the entire SQL with the columns specified in the layout.
To enable SQL pruning – On the Data Model Properties page, select On for the Enable SQL Pruning property.
