Performance and Memory Usage Considerations for Smart View with Excel

Oracle Smart View for Office is a Microsoft Office add-in. As an add-in, it is loaded in to the Microsoft Excel process. When a report is imported into Excel through Smart View, the Excel process consumes memory and this can negatively impact performance.

For example, instances of grids and forms, objects in the Undo buffer, XML parsing, etc., will cause the Excel process to consume memory. Because of this, the Excel or machine memory limit becomes the Smart View memory limit as well. This can severely impact performance.

There is no memory configuration parameter in Smart View to manage this.

Excel 32-bit and 64-bit have different memory limits:

  • For Excel 32-bit, the maximum memory limit, or RAM, is 2GB.

    Oracle testing showed that when Excel 32-bit memory usage reaches beyond 700 MB, Excel can behave abnormally and can terminate unexpectedly.

  • The 64-bit versions of Excel are not constrained to 2 GB of RAM, as are the 32-bit Office applications.

    For large reports, Oracle recommends using 64-bit Excel. Or consider reducing the data being sent to Smart View by reducing the number of rows, columns, and POVs in the reports.

Oracle suggests that you consider taking some or all of the actions listed below in an effort to limit memory usage and improve performance.

The following actions can help to limit memory usage:

  1. Ensure that these options are enabled in the Smart View Options dialog box, Advanced tab:

    • Reduce Excel File Size

    • Improve Metadata Options

  2. In the Options dialog box, Advanced tab, set Number of Undo Actions to '0' (zero), if possible, or to a low number such as 1 or 2.

  3. Change Excel calculation from Automatic to Manual. Go to the Excel Formulas ribbon, then select Calculation Options, and then select Manual.

  4. In the Options dialog box, Formatting tab, if you have selected the Use Excel Formatting option, then you should clear the Move Formatting on Operations option.

  5. In the Formatting tab, clear the Adjust Column Width option.

  6. In the Options dialog box, Extensions tab, disable any unneeded extensions.

  7. In Excel, to improve performance and stability, disable or uninstall any unused add-ins. For example, disable or uninstall the Oracle Essbase Spreadsheet Add-in. Other possible add-ins to disable or uninstall are, for example, Adobe PDF, WebEx, or Send to Bluetooth

  8. Reduce the workbook size.

  9. Limit the data imported to Excel:

    1. Use prompts, filters, POVs, or other ways to reduce the data returned back to Smart View.

    2. Enable the Suppress Rows and Suppress Columns options in the Data Options tab of the Smart View Options dialog box.

The following actions can help to improve performance, as well as limit memory usage:

  1. In the Options dialog box, Advanced tab, set Number of Undo Actions to '0' (zero), if possible.

  2. In the Options dialog box, Member Options tab, ensure that these options are cleared:

    • Preserve Formulas and Comments in ad hoc options

    • Formula Fill

    Clear these options only if you do not need to preserve formulas or comments during ad hoc operations.

Also, refer to the following documentation from Microsoft, "Excel 2010 Performance: Performance and Limit Improvements":