Oracle® Database Installation Guide
10g Release 1 (10.1) for hp OpenVMS Alpha
Part No. B13681-01
The topics covered in this appendix are:
The Oracle 64-bit feature provides the ability to support Very Large Memory (VLM) system configurations with large amounts of RAM.
The following sections describe the benefits of the 64-bit feature and its implementation.
The primary benefit of the 64-bit feature is performance, because many operations can now run at memory speed instead of disk speed. With larger amounts of data in the memory, the database issues fewer calls to disk. This greatly reduces the delay of disk I/O.
Oracle Database 64-bit feature also provides the advantage of scalability to support more users and larger volumes of data. The system does not need to swap data in and out of memory to process all the transaction requests, and it can effectively accommodate requests for large amounts of data.
The 64-bit feature benefits both query-intensive decision support system (DSS) and read/write (OLTP) transactions. The 64-bit feature provides index builds, full table scans, ad hoc queries, and multiway joins for DSS. For OLTP, the feature provides the ability to support very large tables, large amounts of data, and large number of users.
The OpenVMS operating system has native 64-bit memory addressing, which enables Oracle Database to implement the 64-bit feature. The need to estimate the maximum System Global Area (SGA) at installation time is now eliminated. This speeds up process startup. The server no longer includes an
By default, SGA creation uses the support, which was first used in OpenVMS version 7.1, that enables the creation of global sections. These global sections are not pageable and do not require a backing file.
The recommended parameter settings for the Oracle 64-bit feature are:
Big Oracle Blocks (BOB) provide the ability to support larger I/O transfers between memory and disk. BOB complements large SGA configurations, because BOB enables the system to move data faster between memory and disk. With VLM configurations, system performance depends directly on the ability of the system to move database blocks into the SGA as efficiently as possible. Without the benefits of improved data transfer, performance can decline.
For a pure DSS application, select a large value (such as 32 KB) for
DB_BLOCK_SIZE. For an OLTP type of application, choose a lower value (such as, 2 KB or 4 KB). The larger the
DB_BLOCK_SIZE, the more serious the impact on single-row lookups.
optimizer_mode = choose
To use CBO, ensure that all tables and indexes are analyzed such that the latest statistics are available. Use the SQL*Plus commands,
analyze ...estimate for large tables and
analyze index ... compute statistics for indexes.
SORT_AREA_SIZE parameter with care.
SORT_AREA_SIZE is the space used in Program Global Area (PGA) for each sort run by each Oracle process. If the value is too high, then the PGA will use excessive memory when sorting. The default value of 512 KB is usually sufficient.
Check statistics, such as
V$SYSSTAT, to see if the number of sorts to disk is high compared to in-memory sorts. If it is, then increase the value of
Check the size of the number of rows of the tables involved in the query, and translate this size into the total number of blocks. Based on the query, try to fit as many of the frequently accessed table blocks in the buffer cache.
For example, if four tables are involved in a query, but columns from one table are used repeatedly in the
WHERE clause in joins,
IN, and so forth, then try to fit as many blocks as possible from this table into the cache to see if
DB_BLOCK_BUFFERS can be increased. To ensure that the frequently accessed tables are cached and stay in the most recently used (MRU) end of the cache, perform either of the following steps:
Enter the following command (using SQL*Plus):
alter table tablename cache
At the time of table creation, enter the following:
create table tablename ... cache
If an adequate number of buffers are available to accommodate all blocks from all tables that are involved in the query, then use the
alter command to cache all the blocks. The purpose is to cache most blocks into memory to ensure that I/O to disks is eliminated or remains low.