Managing Database Settings

In This Section:

Understanding the Essbase Server Kernel

Understanding Kernel Components

Understanding Kernel Startup

Understanding the Precedence of Database Settings

Understanding How Essbase Reads Settings

Viewing Most-Recently Entered Settings

Customizing Database Settings

Understanding the Essbase Server Kernel

The kernel provides the foundation for a variety of functions of Essbase Server, including data loading, calculations, spreadsheet lock and send, partitioning, and restructuring. The kernel reads, caches, and writes data; manages transactions; and enforces transaction semantics to ensure data consistency and data integrity.

The kernel has the following functions:

  • Handles disk storage and caching of Essbase files

  • Handles data retrieval

  • Handles data updates

  • Controls input/output functions related to Essbase

  • Consolidates free space for reuse

  • Manages concurrent operations

  • Recovers databases after a server crash

  • Issues locks

  • Manages transactions

See:

Understanding Buffered I/O and Direct I/O

The Essbase Kernel uses buffered I/O (input/output) by default, but direct I/O is available on the operating systems and file systems that Essbase supports, with the exception of Linux. See the Oracle Hyperion Enterprise Performance Management System Certification Matrix (http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html).

Buffered I/O uses the file system buffer cache.

Direct I/O bypasses the file system buffer cache and is able to perform asynchronous, overlapped I/Os. The following benefits are provided:

  • Faster response time. A user waits less time for Essbase to return data.

  • Scalability and predictability. Essbase lets you customize the optimal cache sizes for its databases.

If you set a database to use direct I/O, Essbase attempts to use direct I/O the next time the database is started. If direct I/O is not available on your platform when the database is started, Essbase uses buffered I/O, which is the default. However, Essbase will store the I/O access mode selection in the security file and attempts to use that I/O access mode each time the database is started.

Note:

Cache memory locking can only be used if direct I/O is used. You also must use direct I/O if you want to use an operating system's no-wait (asynchronous) I/O.

Viewing the I/O Access Mode

Buffered I/O is the default for all databases.

  To view which I/O access mode a database is currently using or is currently set to, use a tool:

Tool

Topic

Location

Administration Services

Selecting an I/O Access Mode

Oracle Essbase Administration Services Online Help

MaxL

display database

Oracle Essbase Technical Reference

ESSCMD

GETDBINFO

Oracle Essbase Technical Reference

Setting the I/O Access Mode

  To use direct I/O instead of the default buffered I/O for any database, use a tool:

Tool

Topic

Location

Administration Services

Selecting an I/O Access Mode

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM

Oracle Essbase Technical Reference

You may also need to increase the size of some caches. See Sizing Caches.

Understanding Kernel Components

The kernel contains components that control all aspects of retrieving and storing data:

  • The Index Manager finds and tracks the location of requested data. See Index Manager.

  • The Allocation Manager, part of the Index Manager, allocates space and manages some file operations. See Allocation Manager.

  • The Data Block Manager retrieves the data pointed to by the index and stores the data. See Data Block Manager.

  • The LRO Manager handles retrieval and storage of LROs. See LRO Manager.

  • The Lock Manager handles the locking of data blocks to regulate concurrent data access. See Lock Manager.

  • The Transaction Manager tracks transactions and handles internal commit and abort operations. See Transaction Manager.

Index Manager

Index Manager manages the database index and provides a fast way to look up Essbase data blocks. Index Manager determines which portions of the database index to cache in the index cache, and manages the index cache.

Table 150 lists the components that Index Manager controls:

Table 150. Index Manager Components

Component

Description

Index

The method that Essbase uses to locate and retrieve data. The term index also refers to the index file.

Index file

File that Essbase uses to store data retrieval information. It resides on disk and contains index pages. Essbase names index files incrementally on each disk volume, using the naming convention essxxxxx.ind, where xxxxx is a number. The first index file on each disk volume is named ess00001.ind.

Index page

A subdivision of an index file that contains index entries that point to data blocks.

Index entry

A pointer to a data block. An index entry exists for every intersection of sparse dimensions.

Index cache

A buffer in memory that holds index pages.

The term index refers to all index files for a single database. The index can span multiple volumes, and multiple index files can reside on a single volume. Use the disk volumes setting to specify disk spanning parameters. For information on setting the index cache size, see Sizing the Index Cache. For information about allocating storage space with the disk volumes setting, see Specifying Disk Volumes.

Allocation Manager

Allocation Manager, part of the Index Manager, performs these tasks:

  • Creation and extension of index and data files on disk

  • File open and close operations

  • Designation of which volume to use for a new file

  • Sequence of volume use

When one of these tasks must be performed, the Allocation Manager uses this process to allocate space:

  1. It attempts to use free space in an existing file.

  2. If not enough free space is available, it attempts to expand an existing file.

  3. If not enough free space is available in existing files, it creates a file on the current volume.

  4. If it cannot expand a file or create a file on the specified volume, it attempts to use the next specified volume.

  5. If all specified volumes are full, an error message is displayed, and the transaction is aborted.

The Allocation Manager allocates space for index and data files based on the database settings for storage.

  To check current values and set new values, use a tool:

Tool

Topic

Location

Administration Services

Setting Database Properties

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM 23

Oracle Essbase Technical Reference

See Specifying Disk Volumes.

For information on how Essbase stores data, see Storage Allocation.

Data Block Manager

The Data Block Manager brings data blocks into memory, writes them out to data files, handles data compression, and writes data files to disk. The Data Block Manager controls four components. The following table describes each component:

Table 151 lists the components that Data Block Manager controls:

Table 151. Data Block Manager Components

Component

Description

Data file

A file that contains data blocks. Essbase generates the data file upon data load and stores it on disk. Essbase names data files incrementally—essxxxxx.pag, where xxxxx is a number, starting with 00001.

Data block

The primary storage unit within Essbase. A data block is a multidimensional array that represents cells of the dense dimensions for a given intersection of sparse dimensions.

Data cache

A buffer in memory that holds uncompressed data blocks.

Data file cache

A buffer in memory that holds compressed data files (.pag).

The size of the data file cache determines how much of the data within the data files can fit into memory simultaneously. The data cache size and the data block size determine how many data blocks can fit into memory simultaneously. Data files for a single database can span multiple volumes; multiple databases can reside on the same volume. See Sizing the Data File Cache and Sizing the Data Cache. Also see Specifying Disk Volumes.

LRO Manager

LROs enable you to associate objects, such as flat files, with data cells. Using the Spreadsheet Add-in, users can create and store LRO files, with an .lro extension.

LRO files are stored in the database directory (ARBORPATH/app/appname/dbname; for example, app/Sample/Basic).

Essbase stores information about LROs in an LRO catalog. Each catalog resides in its own Essbase index page and coexists in an index file with other, non-LRO Essbase index pages.

See Linking Objects to Essbase Data and the Oracle Essbase Spreadsheet Add-in User's Guide.

Lock Manager

The Lock Manager issues locks on data blocks, which in turn controls concurrent access to data.

The committed access and uncommitted access isolation levels use different locking schemes. For more information on isolation levels and locking, see Ensuring Data Integrity.

Transaction Manager

The Transaction Manager controls transactions and commit operations and manages database recovery.

Essbase commits data automatically. Commits are triggered by transactions that modify data—data loading, calculating, restructuring, and spreadsheet lock and send operations.

How Essbase commits data depends on whether the transaction isolation level is set to committed or uncommitted access (the default). See Committed Access and Uncommitted Access.

The Transaction Manager maintains a transaction control table, dbname.tct, to track transactions.

For information about commit operations and recovery, see Recovering from a Crashed Database.

Understanding Kernel Startup

The sequence of events during kernel startup:

  1. After the Essbase Server starts, a user connects to it from a client.

  2. The user starts a database.

  3. Essbase loads the database.

  4. The Essbase Agent passes database settings to the server.

  5. The kernel begins its initialization process.

  6. The kernel starts its components—the Index Manager, Lock Manager, LRO Manager, Data Block Manager, and Transaction Manager.

If it encounters an error during start up, the Essbase Kernel shuts itself down.

Understanding the Precedence of Database Settings

Essbase provides default values for some database storage settings in the essbase.cfg file. You can leave the default settings or change their values in two places:

  • You can define storage settings for all databases on the Essbase Server by changing values in the essbase.cfg file.

  • You can define storage settings for a single database by using any of the methods specified in Specifying and Changing Database Settings.

Changes made for an individual database permanently override essbase.cfg settings and Essbase defaults for the relevant database until they are changed or withdrawn.

If you use MaxL or Administration Services Console to change settings at the database level, the changes become effective at different times, as shown in Table 152:

Table 152. Database Setting Precedence

Setting

When setting becomes effective

  • Index cache

  • Data file cache

  • Data cache

  • Cache memory locking

  • Disk volume

After you stop and restart a database

Isolation level parameters, concurrency parameters

The first time after setting these values that there are no active transactions

All other settings

Immediately

If you manually change these database settings in essbase.cfg, you must stop and restart the relevant application to make them effective.

Note:

The size of index pages is fixed at 8 KB to reduce input-output overhead, as well as to simplify database migration.

Understanding How Essbase Reads Settings

Essbase reads essbase.cfg when you start Essbase Server and applies settings to the databases that you created using the methods described in Specifying and Changing Database Settings.

Database settings that you specify using Administration Services, ESSCMD, or MaxL always override essbase.cfg settings, even if you change a setting in essbase.cfg after you have applied a setting for a database. Only removing a setting triggers Essbase to use essbase.cfg, and then only after Essbase Server is restarted.

Viewing Most-Recently Entered Settings

  To view the most-recently entered settings, use a tool:

Tool

Topic

Location

Administration Services

Setting Database Properties

Oracle Essbase Administration Services Online Help

MaxL

display database

Oracle Essbase Technical Reference

ESSCMD

GETDBSTATE

GETDBINFO

Oracle Essbase Technical Reference

Customizing Database Settings

You can customize settings for each database on Essbase Server. The information in this section helps you understand what each setting controls, how to specify settings, and lists examples. For a table of performance-related settings, see Improving Essbase Performance.

Note:

Configure settings that are applied to an entire Essbase Server in essbase.cfg. For more information, see the Oracle Essbase Technical Reference.

Table 153 lists the major database settings that you can customize:

Table 153. Major Kernel Settings

Setting

See

Index cache size

Sizing the Index Cache

Data file cache size

Sizing the Data File Cache

Data cache size

Sizing the Data Cache

Cache memory locking

Deciding Whether to Use Cache Memory Locking

Disk volumes

Storage Allocation

Data compression

Data Compression

Isolation level

Understanding Isolation Levels

The following sections describe how to change kernel settings and list examples.

Specifying and Changing Database Settings

Before you change database settings, review information about precedence of the settings as changed in different parts of Essbase, and how Essbase reads those settings:

  To specify most database settings, use a tool:

Tool

Topic

Location

Administration Services

Setting Database Properties

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM

SETDBSTATE

Oracle Essbase Technical Reference

These methods provide different ways to change the same database settings. In rare cases, you may want to use essbase.cfg to specify settings.

Caution!

In previous versions of Essbase, you can specify many database settings in the essbase.cfg file on Essbase Server. In Version 5.x and later, Essbase overrides most of the .cfg settings. For an explanation of how newer versions of Essbase handle settings, see Understanding the Precedence of Database Settings and Understanding How Essbase Reads Settings.

Using alter database in MaxL

Issue a separate alter database statement for each database setting you want to change. For example, the following MaxL script logs on to Essbase, changes three database settings, and logs off:

login admin identified by secretword;
alter database sample.basic enable committed_mode;
alter database sample.basic set lock_timeout immediate;
alter database sample.basic disable create_blocks;
logout;

Note:

Terminate each MaxL statement with a semicolon when issuing them using the MaxL Shell; however, if MaxL statements are embedded in Perl scripts, do not use the semicolon statement terminator.

You can use MaxL to write batch scripts that automate database setting changes. See the MaxL Language Reference, located in the Oracle Essbase Technical Reference.

Using SETDBSTATEITEM in ESSCMD

For simple items, specify the command, item number representing the parameter, application, database, and value for the parameter:

SETDBSTATEITEM 2 "SAMPLE" "BASIC" "Y";

For parameters that require multiple values, such as Isolation Level (item 18), specify multiple values; in this case, all the values after “BASIC”:

SETDBSTATEITEM 18 "SAMPLE" "BASIC" "1" "Y" "-1";

If you do not know the parameter number, omit it, and Essbase lists all parameters and their corresponding numbers. Essbase also prompts you for a database and an application name.

Use a separate SETDBSTATEITEM command for each parameter; you cannot string parameter numbers together on the same line.

See the Oracle Essbase Technical Reference for information about the parameters for the SETDBSTATE and SETDBSTATEITEM commands.

Note:

SETDBSTATEITEM or SETDBSTATE affects only the specified database.

You can include SETDBSTATEITEM (or SETDBSTATE) in batch scripts. For a comprehensive discussion of batch processing, see Using Script and Batch Files for Batch Processing. For information on specific ESSCMD syntax, see the Oracle Essbase Technical Reference.