|Oracle9i Installation Guide
Release 2 (22.214.171.124) for hp OpenVMS Alpha
Part Number B10508-01
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:
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.
This section includes the following information:
The Oracle 64-bit feature provides the ability to support Very Large Memory (VLM) system configurations with large amounts of RAM.
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.
Starting with release 126.96.36.199.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.
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.
For example, for a 3 GB buffer cache, with DB_BLOCK_SIZE=8192, set DB_BLOCK_BUFFERS=400000.
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.
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.
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:
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.