A Oracle Database 64-Bit Feature

This appendix introduces the 64-bit feature or Very Large Memory (VLM) and recommends the parameter settings for this feature.

The topics covered in this appendix are:

Section A.1, "Introduction to Oracle Database 64-Bit Feature"
Section A.2, "Suggested Parameter Settings"
Section A.3, "Other Recommendations"

Note:
The 64-bit feature is a standard feature of Oracle Database for OpenVMS and cannot be removed. The 64-bit feature tuning is under constant revision. Therefore, this information will be updated as further tuning suggestions are realized.

A.1 Introduction to Oracle Database 64-Bit Feature

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.

Section A.1.1, "Benefits of Oracle Database 64-Bit Feature"
Section A.1.2, "Implementation of Oracle 64-Bit Feature"

A.1.1 Benefits of Oracle Database 64-Bit Feature

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.

A.1.2 Implementation of Oracle 64-Bit Feature

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 SGAPAD.

By default, SGA creation uses OpenVMS facilities to create global sections.

A.2 Suggested Parameter Settings

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.
Configure the size of the Oracle buffer cache such that it provides the best possible cache hit ratio without affecting memory requirements of other Oracle and system processes.

Note:
You cannot combine the DB_BLOCK_BUFFERS with the dynamic DB_CACHE_SIZE parameter. If you combine these parameters in the same parameter file, then it will produce an error.

For example, for a 3 GB buffer cache with DB_BLOCK_SIZE=8192, set DB_BLOCK_BUFFERS=400000.
Set the 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 SORT_AREA_SIZE.

A.3 Other Recommendations

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
```
or
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.