Click here for aggregate storage version
Change database-wide settings.
Permission required: create_application.
Syntax
Use alter database set to change the following database-wide settings:
Keyword | Description | ||
---|---|---|---|
retrieve_buffer_size | Change the database retrieval buffer size. This buffer holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer commands. The default size is 10 KB (on 32-bit platforms), and 20 KB (on 64-bit platforms). The minimum size is 2 KB. Increasing the size may improve retrieval performance. | ||
retrieve_sort_buffer_size | Change the database retrieval sort buffer size. This buffer holds data until it is sorted. The Report Writer and Essbase Query Designer use the retrieval sort buffer. The default size is 10 KB (on 32-bit platforms), and 20 KB (on 64-bit platforms). The minimum size is 2 KB. Increasing the size may improve retrieval performance. | ||
data_cache_size | Change the data cache size. The data cache is a buffer in memory that holds uncompressed data blocks. Essbase Server allocates memory to the data cache during data load, calculation, and retrieval operations as needed. The default and minimum size is 3072 KB. | ||
data_file_cache_size | Change the data file cache size. The data file cache is a buffer in memory that holds compressed data files (.PAG files). Essbase Server allocates memory to the data file cache during data load, calculation, and retrieval operations as needed. The data file cache is not used when buffered I/O is used; you must use direct i/o to use the data file cache. The default size is 32 MB. | ||
index_cache_size | Change the index cache size. The index cache is a buffer in memory that holds index pages. When a data block is requested, Essbase looks at the index pages in the index cache to find its location on disk. The default size is 1 MB when buffered I/O is used, and 10 MB when direct I/O is used. Buffered I/O is the default for this release. | ||
currency_database | Link the database with a currency database. A currency database enables you to convert currency values in a database from one currency into another currency. | ||
currency_member | Specify the member to use as a default value in currency conversions. You can specify any valid member of the dimension defined as "Currency Type" in the currency database. | ||
currency_conversion | Specify whether during currency conversion, the calculation method multiplies the currency database exchange rates with the main database values, or that the currency database exchange rates are divided by the main database values. | ||
minimum permission | Set a level of permission that all users or groups can have to the database. Users or groups with higher granted permissions than the minimum permission are not affected. | ||
compression rle | Set the database to use run-length encoding (RLE) compression. Essbase compresses repetitive, consecutive values, including zeros and #MISSING values. The default compression type is bitmap. When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it. | ||
compression bitmap | Set the database to use bitmap compression, the default. Essbase stores only non-missing values and uses a bitmapping scheme. When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it. | ||
compression zlib | Set the database to use ZLIB compression. When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it. If your database allows or requires "Aggregate Missing Values" setting set to YES, then you may want to consider using ZLIB as the compression scheme. ZLIB particularly works well on such databases compared to other compression schemes. However, changing the aggregate missing values setting may have an impact on calculation results - see the Oracle Essbase Database Administrator's Guide. Consider using ZLIB only if you have already determined that the setting should be YES for other reasons. | ||
lock_timeout | Change the interval to wait for blocks to be unlocked when the database is in committed mode. If a transaction request is made that cannot be granted in the allotted time, the transaction is rolled back until a lock can be granted.
| ||
implicit_commit after <number> blocks | When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of blocks has been reached). | ||
implicit_commit after <number> rows | When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of rows has been reached). | ||
io_access_mode | Change the input/output setting you wish to use for the database. The change takes effect the next time the database is started. Buffered I/O uses the file system's buffer cache, and is the default. Direct I/O bypasses the file system's buffer cache, and is able to perform asynchronous, overlapped I/Os, providing faster response time and more potential to optimize cache sizes for databases. If you set a database to use direct I/O, Essbase will attempt to use direct I/O each time the database is started. If direct I/O is not available on your platform at the time the database is started, Essbase will use buffered I/O, which is the default. | ||
variable | Change the value of an existing substitution variable on the database. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&). | ||
default calculation | Change the default calculation (which, by default, is CALC ALL;) to the stored calculation script you specify, or to an anonymous (unstored) calculation string. | ||
active alias_table | Set an alias table as the primary table for reporting and any additional alias requests. Only one alias table can be used at a time. This setting is user-specific; it only sets the active alias table for the user issuing the statement. | ||
ha_trace level | Enable logging of queries generated by Hybrid Analysis operations, such as running a report involving relationally stored members, or drilling into a spreadsheet containing relationally stored members. The queries are logged into the file essha.log, which is found in the root Essbase installation directory, Essbase. The level option controls the amount of information written to essha.log. Level high should be used only for debugging purposes and should be rarely used because it can quickly fill up the log file. Level low is recommended. | ||
ha_trace off | Turn off logging of queries generated by Hybrid Analysis operations. | ||
performance statistics enabled | Turn on performance-statistics gathering. You might do this when you want to tune the system, change hardware configuration, or monitor I/O. The measurement begins for current processes as soon as you enable it. Any subsequent queries for statistics return measurements spanning from the time of enablement to the time of the query. Performance statistics can be retrieved using query database. | ||
performance statistics disabled | Turn off performance-statistics gathering. This halts the collection of statistics; it does not prevent anyone from retrieving old statistics using query database. | ||
performance statistics mode to <PST-SPEC> | Reset performance statistics gathering for a specified persistence and scope. Each of the statistics tables available using query database has a pre-defined persistence and scope. When you use set performance statistics mode, you select the persistence and scope to reset, and the collecting of measurements starts over for the applicable tables. ![]() | ||
note | Create an informational note about the database that Spreadsheet Add-in users can see from the login dialog box. For example, 'Calc in progress: do not update.' Database notes can be up to 64 kilobytes long. |
Example
alter database Sample.Basic set lock_timeout after 120;
Changes the number of seconds to wait for blocks to be unlocked. If a transaction request is made which cannot be granted in 120 seconds, the transaction is rolled back until a lock can be granted.