B
Oracle9i 64-bit Feature

This appendix introduces the 64-bit feature or Very Large Memory (VLM) and makes setting and other recommendations for this feature.

Topics covered in this appendix are:

• Introduction to the Oracle9i 64-bit Feature

• Suggested Parameter Settings

• Other Recommendations

  Attention: The 64-bit feature is a standard feature of Oracle9i for HP OpenVMS Alpha and cannot be de-installed.  64-bit feature tuning is under constant revision.  Therefore, this information will be updated as further tuning suggestions are realized.

Introduction to the Oracle9i 64-bit Feature

This section includes the following information:

• Introducing the Oracle9i 64-bit Feature

• Benefits of the Oracle9i 64-bit Feature

• Implementation of the Oracle 64-bit Feature on HP OpenVMS Alpha

Introducing the Oracle9i 64-bit Feature

The Oracle 64-bit feature provides the ability to support Very Large Memory (VLM) system configurations with large amounts of RAM.

Benefits of the Oracle9i 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 memory, the database issues fewer calls to disk, thus eliminating the delay of disk I/O.

Oracle's 64-bit feature also provides the advantage of "scalability", to support larger number of users and larger amounts of data.  The system does not need to swap data in and out of memory to process all of the transaction requests and can more effectively accommodate requests for larger amounts of data.

The 64-bit feature can benefit both query-intensive (DSS) and read-write (OLTP) transactions.  For DSS, the database feature provides particular advantage for index builds, full table scans, ad hoc queries, and multi-way joins.  For OLTP, the feature provides the ability to support very large tables, large amounts of data, and large numbers of users.

Implementation of the Oracle 64-bit Feature on HP OpenVMS Alpha

Starting with release 7.3.2.3.2, HP OpenVMS Alpha supported the Very Large Memory (VLM) 64-bit feature.  The HP OpenVMS Alpha operating system has native 64-bit memory addressing, which allows Oracle9i to implement the 64-bit feature.  The need to estimate the maximum System Global Area (SGA) at installation time is now eliminated and process startup times are faster.  The Server no longer includes an SGAPAD.

By default, SGA creation uses the support which first appeared in the HP OpenVMS Alpha version 7.1 operating system that allows the creation of global sections that are not backed by any file.  These global sections are not pageable and do not require a backing file.

Suggested Parameter Settings

1. Big Oracle Blocks (BOB) provide the ability to support larger I/O transfers between memory and disk.  BOB complements large SGA configurations, because BOB allows 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 decision support system (DSS) application, you may wish to choose a large value (such as 32K) for DB_BLOCK_SIZE.  For an OLTP type of application, choose a lower value (such as, 2K or 4K).  The larger the DB_BLOCK_SIZE, the more serious the impact on single-row lookups.

2. Configure the size of the Oracle buffer cache such that it will provide the best possible cache hit ratio without affecting memory requirements of other Oracle and system processes.

   For example, for a 3 GB buffer cache, with DB_BLOCK_SIZE=8192, set DB_BLOCK_BUFFERS=400000.

3. To enable Cost Based Optimizer (CBO), set:

   optimizer_mode = choose
   

   To use CBO, make sure all the tables and indexes are analyzed so that the statistics are up-to-date.  Use the SQL*Plus commands "analyze ...estimate" for large tables and "analyze index ... compute statistics" for indexes.

   Note: When no statistics are gathered or available, the Rule-based Optimizer (RBO) is used.

   Note: Ensure the query does not use the rule hint (/*+ rule */).  Otherwise the CBO will be disabled.

4. Set the SORT_AREA_SIZE parameter with care.  SORT_AREA_SIZE is the space used in Program Global Area (PGA) for each sort executed by each Oracle process.  If the value is too high, the PGA will use excessive memory when sorting.  The default value (512 K) 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.

Other Recommendations

Check the size in number of rows of the tables involved in the query, and translate this size into total number of blocks.  Based on the query, try to fit as many of the hard hit table blocks in DB_BLOCK_BUFFERS.

For example, if there are four tables involved in the query, but columns from one of the tables are used repeatedly in the "where" clause in joins, "in", etc.; try to fit as many blocks from this table as possible into the cache to see if
DB_BLOCK_BUFFERS can be increased.  To ensure the hard hit tables are cached and stay in the most recently used (MRU) end of the cache, perform either of the following steps:

• Type (using SQLPLUS),

  alter table <tablename> cache
  

  or

• At the time of creation,

   create table <tablename> ... cache
  

If there are enough buffers to accommodate all blocks from all tables involved in the query, 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.