Improve the Performance of Plans Containing a Large Number of Measures

Oracle continuously looks at opportunities to improve planning cycle times at your enterprise by optimizing the technical processes for faster execution, utilizing techniques such as optimizing the order of columns in database tables. This update introduces one such improvement for plans containing over 255 measures with stored data. It reorders the database columns that store measure data to improve performance.

Your plan’s measure catalog may contain stored measures that are not populated. Even though these measures don’t contain any data, they are still allocated space in the database, specifically in terms of columns in database tables. Moving these columns to the end of the table can result in improved performance for both plan runs and the user interface.

When a plan is run with the Refresh with current data option selected, this feature will reorder the measure columns in the plan’s database tables. The columns are arranged so that those without data are moved to the end, while fully populated measure columns are placed at the beginning. This helps reduce the physical storage space, which in turn shortens the time required to retrieve the data from the database.

Since the Run Plan is already manipulating these database tables, this additional optimization doesn’t add significant time. However, the resulting data tables will be more efficient in terms of both storage space and run time.

The improvements will be most noticeable for plan data tables that have more than 255 columns, but less than 255 columns populated with data. Even if this isn’t the case, improvements can still occur.

Steps to Enable

To support backwards compatibility and prevent any adverse impact on plan run time, this feature is disabled by default. It can be enabled for any demand plan in plan options.

To enable this feature for a demand plan, edit the plan options and click Select Advanced Options on the Demand tab to open the Demand: Advanced Options dialog box.

Demand Plan Options

Demand Plan Options

In the Demand: Advanced Options dialog box,

  1. Enter the text reorgPostTruncate=true in the Parameters Overrides field. Make sure to enter the text with the same capitalization and spaces. If there are other entries in the Parameters Overrides field, enter this value at the end, using a # to separate it from the existing values. For example: REFRESH=TRUE#reorgPostTruncate=true.

Demand: Advanced Options

Demand: Advanced Options

  1. Click Done to close the Demand: Advanced Options dialog box. You must save the plan to save this setting.
  2. Click  Save and Close or click Save and Run on the Edit Plan Options page to save the plan and setting.
  3. To apply the settings, select the Refresh with current data checkbox on the Run Plan page and click OK.

Run Plan

Run Plan

During the plan run, the database tables that store the measure data for the plan will be structured with the optimal column order. This column ordering has been shown to improve the interactive performance of tables and graphs. The level of improvement will vary based on the amount of data being retrieved and the filters applied. Plans with many stored measures that contain no data will see the greatest improvement in performance.

Once the plan has been run successfully, you can remove reorgPostTruncate=true from the Parameters Overrides field. Subsequent plan runs will retain the optimized column order, regardless of whether this setting remains. There is no adverse impact from leaving the setting in place.

Access Requirements

Users who are assigned a configured job role that contains these privileges can access this feature:

  • Monitor Demand Management Work Area (MSC_MONITOR_DEMAND_MANAGEMENT_WORK_AREA_PRIV)
  • Run Plan with Snapshot (MSC_RUN_PLAN_WITH_SNAPSHOT_PRIV)

These privileges were available prior to this update.