Improving Analytic Services Performance Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Improving Analytic Services Performance


You can improve Analytic Services performance with these basic techniques:

Recognizing Basic Design Issues That Affect Optimization

Use the following list to identify basic design issues that affect optimization outside this volume:

Resetting Databases to Increase Performance

You can periodically reset a database, and then reload it. Even if you reload a database very often, the main database files, .pag files, can grow unless you reset the database.

To reset a database, use either of the following methods:


Tool
Topic
Location

MaxL

alter database appname.dbname reset

Technical Reference

ESSCMD

RESETDB

Technical Reference



Using Database Settings to Customize for Maximum Performance

You can customize Analytic Services for maximum performance, using database settings at the database level in Administration Services, ESSCMD, or MaxL.

Note: If you are migrating a database, see the Essbase Analytic Services Installation Guide for information about the default settings after migration.

The following sections list performance settings and describe how to adjust them.

Database Cache Settings

The following table describes database cache settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.


Table 67: Database Cache Settings  

Setting
More Information
Location in Administration Services, MaxL, ESSCMD

Index cache size

Sizing the Index Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set index_cache_size n

  • ESSCMD: SETDBSTATEITEM 12

Data file cache size

Sizing the Data File Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set data_file_cache_size n

  • ESSCMD: SETDBSTATEITEM 27

Data cache size

Sizing the Data Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set data_cache_size n

  • ESSCMD: SETDBSTATEITEM 5

Index page size

Fixed size.

N/A

Cache memory locking

Deciding Whether to Use Cache Memory Locking

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname enable cache_pinning

  • ESSCMD: SETDBSTATEITEM 26


For more information about these settings in Administration Services, see "Setting Database Properties" in Essbase Administration Services Online Help.

Database Disk Volumes Settings

The following table describes database disk volume settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.


Table 68: Database Disk Volume Settings  

Setting
More Information
Location in Administration Services, MaxL, ESSCMD

Volume name

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD:

    SETDBSTATEITEM 23

    SETDBSTATEITEM 24

Partition size

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD:

    SETDBSTATEITEM 23

    SETDBSTATEITEM 24

File type

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD: SETDBSTATEITEM 23

Maximum file size

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD: SETDBSTATEITEM 23


For more information about these settings in Administration Services, see "Setting Disk Volumes" in Essbase Administration Services Online Help.

Database Transaction Control Settings

The following table describes database transaction control settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.

For more information about database transaction control settings in Administration Services, see "Setting Data Integrity Options" in Essbase Administration Services Online Help.


Table 69: Database Transaction Control Settings  

Setting
More Information
Location in Administration Services, MaxL, ESSCMD

Isolation level

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

  • ESSCMD: SETDBSTATEITEM 18

Commit Blocks

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

    and

    alter database appname.dbname set implicit_commit after n blocks

  • ESSCMD: SETDBSTATEITEM 21

Commit Rows

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

    and

    alter database appname.dbname set implicit_commit after n rows

  • ESSCMD: SETDBSTATEITEM 22

Wait for write access to locked data block

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname set lock_timeout

  • ESSCMD: SETDBSTATEITEM 20

Pre-image access

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable pre_image_access

  • ESSCMD: SETDBSTATEITEM 19


Miscellaneous Database Settings

The following table describes miscellaneous database settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.


Table 70: Miscellaneous Database Settings  

Setting
More Information
Location in Administration Services, MaxL, ESSCMD

Retrieval buffer size

Setting the Retrieval Buffer Size

  • Administration Services: Database Properties window, General tab

  • MaxL:

    alter database appname.dbname set retrieve_buffer_size n

  • ESSCMD: SETDBSTATEITEM 16

Retrieval sort buffer size

Setting the Retrieval Sort Buffer Size

  • Administration Services: Database Properties window, General tab

  • MaxL:

    alter database appname.dbname set retrieve_sort_ buffer_size n

  • ESSCMD: SETDBSTATEITEM 17

Data compression

Data Compression

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname enable compression

    and

    alter database appname.dbname set compression type

  • ESSCMD:

    SETDBSTATEITEM 14

    SETDBSTATEITEM 15

Maximum memory for trigger definitions

Understanding Triggers Definitions

MaxL:

create or replace trigger, alter trigger display trigger, and drop trigger



For more information about these settings in Administration Services, see "Setting Database Properties" in the Essbase Administration Services Online Help.

Eliminating Fragmentation

Fragmentation is unused disk space. Fragmentation is created when Analytic Services writes a data block to a new location on disk and leaves unused space in the former location of the data block. Block size increases because data from a data load or calculation is appended to the blocks; the blocks must therefore be written to the end of a data file.

The Analytic Services Kernel merges adjacent fragments into increasingly larger fragments so that unused space is more likely to be re-used.

In some cases, fragmentation cannot be reduced completely. Fragmentation is likely to occur with the following:

If you experience performance slow-downs, you can check to see if there is too much fragmentation of the database, and if there is, you can take steps to reduce the fragmentation:

Measuring Fragmentation

You can measure fragmentation using the average clustering ratio or average fragmentation quotient statistic:

Using the Average Fragmentation Quotient

In ESSCMD, look at the Average Fragmentation Quotient that is returned when you execute GETDBSTATS command. Use this table to evaluate whether or not the level of fragmentation is likely to be causing performance problems:


Database Size
Fragmentation Quotient Threshold

Small (up to 200 Mb)

60% or higher

Medium (up to 2 Gb)

40% or higher

Large (greater than 2Gb)

30% or higher



Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:

Using the Average Clustering Ratio

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

To view the average clustering ratio for a database, use either of the following methods.


Tool
Topic
Location

Administration Services

Viewing Fragmentation Statistics

Essbase Administration Services Online Help

ESSCMD

GETDBSTATS

Technical Reference



Preventing or Removing Fragmentation

You can prevent and remove fragmentation:

Enabling Windows 4 GB RAM Tuning

Essbase supports Microsoft 4 GB RAM Tuning (4GT). 4GT enables users with extremely large databases to take advantage of a larger address space to improve performance.

The total addressable limit of RAM on servers running Windows 2000 or Windows NT is 4 GB. By default, applications can access 2 GB, with the Windows kernel using the other 2 GB. For selected versions of Windows running on Intel architecture servers, Microsoft provides the 4GT feature. The 4GT feature increases the addressable limit for applications to 3 GB, reducing the potential RAM allocated to the Windows kernel from 2 GB to 1 GB.

Essbase currently supports the 4GT feature on computers that use Intel-based processors with more than 2 GB of physical RAM, and that have either of the following versions of Windows:

Enabling the Windows 4GT feature may benefit users if the Essbase installation has both of the following characteristics:

Enabling 4GT

To configure the computer where Essbase is installed to enable the 4GT feature, modify the boot.ini file by adding /3GB to the startup line for each boot partition that is defined for a Windows version that supports 4GT; for example:

[boot loader]
timeout=30 
default=multi(0)disk(0)rdisk(0)partition(2)\WIN2KADV 
[operating systems] 
multi(0)disk(0)rdisk(0)partition(2)\WIN2KADV="Microsoft Windows 2000 Advanced Server" /3GB 
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.0" /3GB  
 

Note:

This change to the boot.ini file is effective only if you are using Windows NT Server 4.0 Enterprise Edition or Windows 2000 Advanced Server. On standard Windows NT, although the /3GB flag relocates the kernel, applications are unable to access more than 2 GB.

On a dual boot system, to ensure that the boot loader supporting the 4GT feature is installed, be sure that the last operating system you install is a version that supports the 4GT feature.

Because the Windows kernel area is reduced, it is unlikely but conceivable that certain applications and workloads in certain environments may experience degraded performance. Testing your workload in your environment is recommended.

For additional information about the Microsoft Windows 4GT feature, see www.microsoft.com.

Finding Additional Optimization Information

Using Database Settings to Customize for Maximum Performance provides general-purpose information and does not account for the wide variety of configuration possibilities. For more information about performance and server, application, or other settings, see these chapters:



Hyperion Solutions Corporation link