E Enabling Memory Guard Features

This section describes property settings available to safeguard your system against memory failures caused by report requests that generate excessive data.

It includes the following sections:

What Are Memory Guard Features?

BI Publisher provides a set of features to protect against out-of-memory errors by blocking report requests that generate excessive amounts of data or consume excessive amount of memory.

These memory guard features consist of a set of properties. The properties enable you to configure conditions and processing points at which data size and free memory availability are inspected to determine whether the system continues to process a report request or terminates processing.

Key Features

The section gives you information on the key features of memory guard and data model properties.

The full set of properties is listed in Configuring Memory Guard and Data Model Properties. The properties enable you to protect against out of memory errors and enhance data processing by setting controls such as:

  • Maximum data size for reports

  • Maximum data size for scheduled reports

  • Minimum free memory size

  • SQL pruning for unused data set columns

  • Time out for SQL queries and also for reporting

The following section highlights some of the properties and provides detail on how the system responds to the settings:

Restricting Maximum Data Sizes for Report Processing

By restricting the data size allowed for report processing you can prevent out of memory errors when a query returns more data than the system can handle.

Specify a Maximum Data Size Allowed for Online Processing

Property: Maximum report data size for online reports.

This property enables you to specify a maximum data size allowed for online report viewing. When you set a maximum data size, the following occurs when a user opens a report for online viewing:

  1. A user submits a report to view online in the browser.

  2. The data engine generates the data for the report.

  3. Before document generation, the size of the data (in bytes) is inspected.

  4. If the data generated is larger than the maximum setting, the report processing is ended. The user gets the following message:

    The report you are trying to run exceeds the data limit set for this server. Either re-run with parameters that reduce the data or schedule this report. Contact your Administrator if you have questions.

    The user can then either set parameters (if available for the report) to limit the data and resubmit online; or use the BI Publisher scheduler to submit the report.

The default value for this property is 300 MB.

Specify a Maximum Data Size Allowed for Offline (Scheduled Report) Processing

Property: Maximum report data size for offline (scheduled) reports.

This feature enables you to specify a maximum data size allowed for scheduled reports. When you set a maximum data size, the following occurs when a scheduled report job executes:

  1. The scheduler commences processing of a report job.

  2. The data engine generates the data for the report.

  3. If the data generated is larger than the maximum setting, the report processing is ended. The scheduled report job fails with the following status message:

    Report data size exceeds the maximum limit (<nnn> bytes). Stopped processing.

    The user can then set parameters (if available for the report) to limit the data.

The default value for this property is 500 MB.

Configuring Free Memory Threshold

This set of properties helps you to protect against out of memory conditions by establishing a minimum available free memory space.

This set of properties enables your system to automatically protect free memory availability and intelligently process reports with large data sets based on this availability.

Specify A Minimum Free Memory Threshold for Report Processing

Property: Free memory threshold

This setting enables you to specify a minimum value for free JVM space. This enables you to control whether to run a report based on two factors: current usage and the size of the report data. This feature requires the setting of several properties that work together. You specify the threshold JVM space, the report maximum report size that will be allowed when the JVM falls below the threshold, and the maximum wait time to pause the report to wait for more JVM free space to become available.

When you set these properties, the following occurs when a user opens a report for online viewing:

  1. A user submits a report to view online in the browser.

  2. The data engine generates the data for the report.

  3. JVM memory is inspected. If the available JVM memory is above the Free memory threshold property value, the report processes as usual and there is no system intervention.

    If the available JVM memory is below the threshold value, the size of the report data is inspected and compared to the property setting for Maximum report data size under the free memory threshold. If the report data is below this threshold, then the report continues processing.

    If the report data size exceeds the threshold, then the report is paused to wait for free memory to become available. The report will wait for the time specified in the property Maximum Wait Time for Free Memory to Come Back Above Threshold Value. If the free memory does not rise back above the minimum in the wait period specified, the report request is rejected.

The default value for this property is 500 MB.

Specify Maximum Report Data Size Under the Free Memory Threshold

Property: Maximum report data size under the free memory threshold

Default value: (value of Free Memory Threshold)/10

Maximum single report data size allowed when free JVM memory is under the specified threshold value set in Free memory threshold. For example (assuming the default setting), if the data generated for a single report exceeds one-tenth of the value set for Free memory threshold, then processing is terminated. Therefore if the Free memory threshold is set to 100 MB and a single report data extract exceeds 10 MB, then the report processing is terminated.

This property takes effect only when Free memory threshold is set to be a positive value.

Set Minimum Time Span Between Garbage Collection Runs

Minimum time span in seconds between any two subsequent garbage collection runs. Set this value to avoid overrunning JVM garbage collection. The server enforces the minimum of 120 seconds, which means the value will be reset to 120 seconds if it falls below the minimum.

The default is 300 seconds.

Set Maximum Wait Time for Free Memory to Come Back Above the Threshold

The maximum time in seconds that a run-report request will wait for free JVM memory to come back above the threshold value. This property value takes effect only when a positive value for Free memory threshold is specified.

If the free memory becomes available within the time specified, the request will proceed immediately to generate the document. If free memory is still below the threshold value after the time specified, the request is rejected. For online requests, the larger this property value, the longer the browser will wait for a request to run.

The default for this property is 30 seconds.

Setting Data Engine Properties

The data engine property settings provide additional points to protect your system against out of memory errors.

These include:

Set Maximum Data Size That Can Be Generated by the Data Engine

This property is used only when you generate XML data via data model editor. In a normal report generation scenario, since template is chosen always, memory guard side properties (maximum report data size for online/offline for each template type) take precedence over this property.

Setting maximum data size sets an absolute limit to the data that can be generated from the execution of a data model. This setting applies to both online report requests and to requests submitted through the scheduler. When the size of the file generated by the data engine exceeds the limit, the data engine terminates execution of the data model and throws the exception:

"oracle.xdo.dataengine.diagnostic.XMLSizeLimitException: XML Output (NNNNNNbytes) generated exceeds specified file size limit (NNNNNbytes)..!!!!!!!".

If the report request was submitted through the scheduler, the job will show as failed in the Report Job History page. The exception error noted above displays when you rest your cursor over the status. If the report request was submitted online, the user will get the error "Unable to retrieve the data XML."

Set Maximum Sample Data Size

A sample data set is required for all data models. The sample data is used during template design. Sample data can be generated by the data model editor or uploaded to the data model. Large sample data sets can impact the performance of the design tools.

Set this property to limit the size of the sample data file that can be uploaded to the data model.

Set Automatic Database Fetch Size

This setting calculates and sets database fetch size at run time depending on total number of data set columns and total number of query columns. Setting this property will override the server-level and data model-level database fetch size properties. When set, this property takes effect for all data models and can significantly slow processing time. This setting is recommended for implementations of BI Publisher that frequently process complex queries of hundreds of columns, such as Oracle Fusion Applications implementations. This setting is not recommended for most general implementations of BI Publisher.

Configuring Memory Guard and Data Model Properties

Implement the memory guard features by setting the properties in the Properties tab of the Administration > Runtime Configurationpage or by using the runtimepropertiesconfig.sh command line utility.

The Memory Guard and Data Model property settings in the Properties tab of the Administration > Runtime Configuration page are described in the following tables.

Memory Guard

Property description Default Value

Maximum report data size for online reports

300MB

Maximum report data size for offline (scheduled) reports

500MB

Free memory threshold

500MB

Maximum report data size under the free memory threshold

free_memory_threshold/10

Maximum wait time for free memory to come back for offline (scheduled) reports

30 (seconds)

Minimum time span between garbage collection runs

300 (seconds)

Maximum wait time for free memory to come back above the threshold value

30 (seconds)

Timeout for online report

600 (seconds)

Maximum rows for CSV output

1000000

Data Model

Property Description

Maximum data size limit for data generation

Default value: 500MB

Maximum XML data size in that can be generated from the execution of a data model. This setting applies to both online report requests and to requests submitted through the scheduler. When the size of the file generated by the data engine exceeds the value set for this property, the data engine terminates execution of the data model and throws an exception.

Validation rule: [1-9][0-9]*[KB|MB|GB]?

Examples:

  • 123MB

  • 128974848

  • 2GB

  • 2147483648

To turn this property off, enter 0 or a negative number.

Maximum sample data size limit

Default value: 1MB

Maximum file size of a sample data file that can be uploaded to the data model editor.

Enable Data Model scalable mode

Default: True

Processing large data sets requires the use of large amounts of RAM. To prevent running out of memory, activate scalable mode for the data engine. In scalable mode, the data engine takes advantage of disk space when it processes the data.

You can also set this property for specific data models. The data model setting overrides the system setting here.

Enable Auto DB fetch size mode

Default value: True

When set to True, BI Publisher calculates and sets database fetch size at run time according to the total number of data set columns and total number of query columns.

This setting avoids out of memory conditions, but can significantly slow processing times.

When set to True, any other DB fetch size settings are ignored.

This setting is recommended for implementations of BI Publisher that frequently process complex queries of hundreds of columns, such as Oracle Fusion Applications implementations. This setting is not recommended for most general implementations of BI Publisher.

This property overrides the data model- level database fetch size properties.

When set, this property takes effect for all data models and can significantly slow processing time.

DB fetch size

Default value: 20 (rows)

The maximum database fetch size for a data model. This property value takes effect only when Enable Auto DB fetch size mode is set to False. When the fetch size is met, the rows are written to a temp file and another fetch is executed; this process is repeated until all the rows are returned to the temp file.

A smaller fetch size requires more round trips from BI Publisher to the database and can impact overall processing time; however, the smaller data chunks ensure against excessive memory usage.

This property can also be set at the data model level. The data model setting overrides the server property.

SQL Query Timeout

Default: 600 seconds

Timeout for SQL query-based data models. If the SQL query is still processing when the timeout value is met, the error "Failed to retrieve data xml." is returned.

This property can also be set at the data model level. The data model setting overrides the server property here.

Irrespective of the settings at the instance level or data model level, the maximum SQL query timeout is 10 minutes for all BI Publisher reports running online. This avoids stuck threads and server outages.

Enable Data Model diagnostic

Default value: False

If you set this property to true, BI Publisher writes the data set details, memory, and SQL execution time information to the log file. Oracle recommends setting this property to true only for debugging purposes. When set to true, processing time is increased.

Enable SQL Session Trace

Default value: False

If you set this property to true, for every SQL query that is executed, BI Publisher writes a SQL session trace log to the database. A database administrator can examine the log.

Oracle recommends that you turn this property on only in test and development environments.

To enable this property, the user that you define for the database connection must be granted the Alter Session privilege on the database (Syntax: GRANT ALTER SESSION TO <USER NAME>). See Setting Up a JDBC Connection to the Data Source.

Enable SQL Pruning

Default value: False

Applies to Oracle Database queries only that use Standard SQL. If your query returns many columns but only a subset are used by your report template, SQL pruning returns only those columns required by the template. Setting this property enhances processing time and reduces memory usage. Note that Enable SQL Pruning is also a data model-level property that can be turned on or off for particular data models to override this server-level setting.

SQL pruning is not applicable for PDF, Excel, and E-text template types.

See Setting Data Model Properties in Data Modeling Guide for Oracle Business Intelligence Publisher.

Using runtimepropertiesconfig.sh Command Line Utility

This section describes how to use the runtimepropertiesconfig.sh command line utility to configure the memory guard properties.

Command Line Utility Requirements

To run the runtimepropertiesconfig.sh command line utility, you must do the following:

  • Set the environment variable. For example, export JAVA_HOME=/home/jdk/jdk1.8.0_40. By default, JAVA_HOME=$BI_HOME/jdk

  • Unzip the <BI_HOME>/modules/BIPConfigService.zip file and place the runtimepropertiesconfig.sh utility in <BI_HOME>/modules/BIPConfigService/bin directory.

    cd <BI_HOME>/modules

    unzip —d BIPConfigService BIPConfigService.zip

  • Change directory to the location of the runtimepropertiesconfig.sh command line utility.

    cd <BI_HOME>/modules/BIPConfigService/bin

  • Provide the path for BI_DOMAIN_HOME when the utility prompts.

    For example: /user_projects/domains/bidomain/

Syntax

runtimepropertiesconfig.sh <Operation> <Options>

where

Operation: update, get, or help

Options for update operation : KEY1=VALUE1,KEY2=VALUE2

Options for get operation: KEY1,KEY2

Memory Guard Properties

Property Description
server.ONLINE_REPORT_MAX_DATA_SIZE Maximum report data size for online reports

Default value: 300MB
server.OFFLINE_REPORT_MAX_DATA_SIZE Maximum report data size for offline (scheduled) reports

Default value: 500MB
server.FREE_MEMORY_THRESHOLD Free memory threshold

Default value: 500MB
server.MAX_DATA_SIZE_UNDER_FREE_MEMORY_THRESHOLD Maximum report data size under the free memory threshold

Default value: free_memory_threshold/10
server.MINIMUM_SECOND_RUN_GARBAGE_COLLECTION Minimum time span between garbage collection runs

Default value: 300 (seconds)
server.WAIT_SECOND_FOR_FREE_MEMORY Maximum wait time for free memory to come back above the threshold value

Default value: 30 (seconds)
server.ONLINE_REPORT_TIMEOUT Timeout for online reports.

Default value: 600 (seconds)
server.MAX_ROWS_FOR_CSV_OUTPUT Maximum rows for CSV output

Default value: 1000000
server.XML_DATA_SIZE_LIMIT Maximum data size limit for data generation

Default value: 500MB
server.MAX_SAMPLE_XML_DATA_SIZE_LIMIT Maximum sample data size limit

Default value: 1MB
server.DB_FETCH_SIZE DB fetch size

Default value: 20
server.SQL_QUERY_TIMEOUT SQL Query Timeout

Default value: 600 (seconds)

Example E-1 Update Memory Guard Properties

The following command updates the values of server.ONLINE_REPORT_MAX_DATA_SIZE and server.SQL_QUERY_TIMEOUT properties.

./runtimepropertiesconfig.sh update server.ONLINE_REPORT_MAX_DATA_SIZE=223MB,server.SQL_QUERY_TIMEOUT=600

Example E-2 List Values of All Memory Guard Properties

The following command lists the values of all the memory guard properties.

./runtimepropertiesconfig.sh get

Example E-3 List Values of Specified Memory Guard Properties

The following command retrieves the values of the specified properties.

./runtimepropertiesconfig.sh get server.ONLINE_REPORT_MAX_DATA_SIZE,server.SQL_QUERY_TIMEOUT

Example E-4 Get Help

The following command lists all the memory guard properties along with the default value.

./runtimepropertiesconfig.sh help

Configuring a Maximum Threads Constraint to Avoid Out of Memory Errors

During the processing of large BI Publisher reports Oracle WebLogic Server can use multiple concurrent threads to generate the report.

If the threads are not constrained, out of memory errors can occur when Oracle WebLogic Server allots too many threads to report generation. To avoid this error, you can create a Work Manager to enforce the maximum number of threads that Oracle WebLogic Server can allot to BI Publisher report processing.

To configure a maximum threads constraint perform the following procedures:

  1. Creating the Maximum Threads Constraint in Oracle WebLogic Server

  2. Creating the Work Manager (XdoWorkManager)

  3. Redeploying the xmlpserver.ear File

    Note:

    This procedure describes redeploying the xmlpserver.ear file to activate the new Work Manager. Alternatively, you can perform one of the following instead of step 3:

    • Restart (stop & start) the bipublisher application

    • Restart the Oracle WebLogic Server instances (for example, bi_server1, bi_server2)

Once this initial setup procedure is completed, changing the value of the maximum threads count (for example from 10 to 20) takes effect immediately; no restart or redeployment operations are required.

Creating the Maximum Threads Constraint in Oracle WebLogic Server

You create the maximum threads constraint component in the Oracle WebLogic Console.

To create the maximum threads constraint component:
  1. Log in to Oracle WebLogic Console.
  2. In the Domain Structure pane, click Work Managers.
  3. In the Change Center pane, click Lock & Edit.
  4. In the Work Managers, Request Classes and Constraints table, click New.
  5. In the Create a New Work Manager Component dialog, select Maximum Threads Constraint and click Next.
  6. Under Maximum Threads Constraint Properties, enter the following property values:

    • Name — enter XdoMaxThreadsConstraint

    • Count — enter the maximum number of threads to allot for BI Publisher report generation, for example, 10


    Description of GUID-E0567C79-D94C-40DF-BF79-6A621B65DDE9-default.gif follows
    Description of the illustration GUID-E0567C79-D94C-40DF-BF79-6A621B65DDE9-default.gif

    Click Next.

  7. Under Select deployment targets, select "bi_cluster" and then click Finish.

Creating the Work Manager (XdoWorkManager)

Now that you have created the Maximum Threads Constraint component and named it "XdoMaxThreadsConstraint"; next create the work manager and associate it to the XdoMaxThreadsConstraint component.

To create the work manager:

  1. While still on the Summary of Work Managers page, click New again.
  2. In the Create a New Work Manager Component dialog, select Work Manager and click Next
  3. Under Work Manager Properties enter the Name property as: XdoWorkManager.
  4. Under Select deployment targets, select "bi_cluster" and then click Finish.
  5. Back on the Summary of Work Managers page, click your newly created XdoWorkManager link.
  6. On the Settings for XdoWorkManager page, on the Configuration tab, specify the Maximum Threads Constraint as XdoMaxThreadsConstraint and click Save.

Redeploying the xmlpserver.ear File

You use the Upgrade Application Assistant to redeploy the xmlpserver.ear file.

To redeploy the xmlpserver.ear file:
  1. In the left pane of the Console, select Deployments.
    Description of GUID-9E304758-DEA8-4025-B2D5-2427D224F900-default.gif follows
    Description of the illustration GUID-9E304758-DEA8-4025-B2D5-2427D224F900-default.gif

    A table in the right pane displays all deployed applications and modules.

  2. In the table, select the bipublisher application.
  3. Click Update.
  4. In the Upgrade Application Assistant, click Next.
  5. Click Finish.
  6. Click Activate Changes in the Change Center pane.