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

The rest of this section explains the two available access modes, and describes how to set the access modes.

Note:

For information about fatal errors in the Essbase Server Kernel, see Understanding Fatal Error Handling.

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 most of the operating systems and file systems that Essbase supports. For a list of the supported platforms, see the Oracle Hyperion Enterprise Performance Management System Installation Start Here.

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

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

      The Index Manager controls five components, described in the following table:

      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:

        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 at one time. The data cache size and the data block size determine how many data blocks can fit into memory at one time. Data files for a single database can span multiple volumes; multiple databases can reside on the same volume. For information on setting the data file cache size and data cache size, see Sizing the Data File Cache and Sizing the Data Cache. For information about allocating storage space with the disk volumes setting, 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 87:

        Table 87. When Database-Level Storage Settings Become Effective

        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.

          You can customize the following major database settings:

          Table 88. Major Kernel Settings

          Setting

          More Information

          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.