Creating Simulations to Help Optimize Database Settings

The performance of the database depends on many parameters. To arrive at optimal values of various database parameters, you can create simulations by applying different combinations of parameter values to the database to see how the performance statistics change. Oracle Hyperion Calculation Manager provides options to analyze the performance statistics you obtain for each simulation and create a report with those performance statistics.

Note:

You can create simulations for block storage application databases.

To create a simulation and a simulation report:

In System View, click the Database Properties icon.
In Enterprise View, expand the Oracle Hyperion Planning application type and the application that contains the database for which you want to create a simulation.
Right-click the plan type and select Tune DB Settings.
In Select Task, select Create a new simulation for outline properties, and click Next.
On Initialize scenario, perform these tasks:
1. Enter the number of iterations to run. (The minimum is one and the maximum is six.)
2. Enter or select the rule to be run before starting each iteration.
3. Enter or select the rules to be run during each iteration.
4. Enter or select the rule to be run after each iteration.
5. Select one of these error message levels to specify what types of errors should be written to the log file during the simulation:
  - Info, to capture routine actions that the server performs during the simulations
  - Warns, to capture conditions that are not deemed serious by the server
  - Error, to capture errors that occurred while performing the simulations
  - Detail, to capture detailed information about calculation statistics during the simulations
  - Summary, to capture only a summary of the calculation statistics during the simulations
  - None, to capture no information or errors during the simulations
6. Select whether you want to create a new database after each simulation.
7. Click Next.
In Set up iteration parameters, set the initial and the incremental values for these parameters:
- Data cache setting: A buffer in memory that holds uncompressed data blocks.
- Index cache setting: A buffer in memory that holds index pages. How many index pages are in memory simultaneously depends upon the amount of memory allocated to the cache.
- Buffer size: The database buffer is a server buffer, per database, that holds extracted row data cells before they are evaluated. Each database has a retrieval buffer setting, in kilobytes, that you can change. The default buffer size is 10 KB for 32-bit platforms and 20 KB for 64-bit platforms. If you are increasing the size of the buffer, Oracle recommends that you do not exceed 100 KB, although the size limit is set at 100,000 KB.
- Sort buffer size: Specifies the size, in kilobytes, of the server buffer that holds the data to be sorted during a retrieval. You can adjust the buffer size on a per-database basis. The default buffer size is set to 10 KB on 32-bit platforms and to 20 KB on 64-bit platforms.
Click Finish.
If there are no validation errors, a View Report tab is displayed with the following information:
- In the Available Statistics area, you can view the following information:
  - Runtime statistics: This tab contains database specific parameters like the average cluster ratio and the index cache hit ration per iteration
  - Execution time statistics: This tab shows the execution time (in seconds) for each rule you ran in each iteration.
- In the Statistics chart area, you can display a line, bar, or column graph of the amount of time (in seconds) that it took to run each rule in each iteration.
- In the Summary area, on the Iteration Details tab, you can review details of the parameters you set for each iteration, such as the data and index cache settings and the buffer sizes.
  
  On the Dimensions tab, you can view information about the dimensions and members used in each iteration, such as the names and types (that is, dense or sparse) of the dimensions, the number of members in each dimension, and the number of members stored in each dimension.
- In the Logs area, on the Charts tab, you can view a line, bar, or column graph of the types of log messages generated for each rule in each iteration.
  
  On the Logs tab, you can view the text of the log messages, if there are any, for each rule in each iteration. You can filter the log text by rule, iteration, and message level.
To change any of the database parameters while you are on the View Report tab, you can select the Overview tab

, modify the database parameters, and rerun the simulation. After you do that, a new report is displayed.
To export the simulation report, select File and then Export. Enter a name for the xml file.
To import the simulation report, in Select Task, select Import/Load simulation report.