Performance Improvement When Building Aggregate Views on Aggregate Storage Databases

You might encounter the following message while building aggregate views on an aggregate storage database:

For better performance, increase the size of aggregate storage cache

This message sometimes occurs when an aggregate storage database is larger than a few hundred million input cells.

To improve the performance of building aggregates, take the following steps.

  1. Increase the size of the aggregate storage cache to at least 512 MB or 20% of the input data size, whichever is smaller. (If the cache setting is already greater than this amount, proceed to the next step.) You can use Administration Services Console or the following MaxL command:

    alter application appname set cache_size xMB

    This setting takes effect after you restart the application.

  2. If you still see the message when building aggregate views after increasing the aggregate storage cache, use the ASOSAMPLESIZEPERCENT configuration setting. Syntax:

    ASOSAMPLESIZEPERCENT [appname [dbname]] n

    Gradually increase the n value until the message disappears and optimal aggregation performance is reached. For a database that contains:

    • 20 million input cells, start with 5%

    • 100 million cells, start with 1%

    • More than 1 billion cells, start with 0.1%

    Clear the aggregate views; then reselect and rebuild them. If the message still appears, increase the setting and try again.

    Performance of building aggregate views may not improve until the message no longer occurs. When the message no longer occurs and performance no longer improves, stop increasing the setting.

    Note:

    If you increase the ASOSAMPLESIZEPERCENT setting too high, performance will start to degrade again. The optimal setting for a database larger than 1 billion cells will probably be less than 3%. For more information on ASOSAMPLESIZEPERCENT, see the Oracle Essbase Technical Reference.