APPENDIX C. Oracle 64-bit Feature
Nothing to be fixed except your performance.
Noel Coward
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:
Attention: The 64-bit feature is a standard feature of Oracle7 for Alpha OpenVMS and can not 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 Oracle 64-bit Feature
This section includes the following information:
Introducing the Oracle 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 Oracle 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 many 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 Alpha OpenVMS
Version 7.3.2.3.2 of Oracle7 for Alpha OpenVMS supports the Very Large Memory (VLM) 64-bit feature. The OpenVMS Alpha operating system has native 64-bit memory addressing, which allows Oracle7 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 new support in the 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.
2. Choose DB_BLOCK_BUFFERS based on the size of the Oracle buffer cache that 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 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 (64K) is usually sufficient.
Check statistics, such as V$SYSSTAT, to see if the number of sorts that are performed in memory and on the disk is high. If it is, then increase the value of SORT_AREA_SIZE.
5. The CACHE_SIZE_THRESHOLD parameter controls the space used in the buffer cache exclusively for table scans. If most queries require table scans, increase the CACHE_SIZE_THRESHOLD parameter value. The maximum value is 50% of DB_BLOCK_BUFFERS, and the default is 10% of DB_BLOCK_BUFFERS. See the section "Other Recommendations."
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 (under svrmgrl or sqlplus),
alter table <tablename> cache
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. Pay special attention to the CACHE_SIZE_THRESHOLD parameter as described in the section above, "Suggested Parameter Settings."