HP ProLiant 3000 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 24

Page 22 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers • HASH_AREA_SIZE • SORT_AREA_SIZE For a large data warehouse, HASH_AREA_SIZE may range from 8MB to 32MB or more for hash joins. Processes The init.ora parameter PROCESSES should be adjusted. This parameter specifies the number of operating system user processes that connect to the Oracle instance. This number must also include the Oracle service, background, and parallel server threads initiated at the Oracle instance startup. There are two Oracle service threads. The background threads include the seven processes defined in Figure 3. Therefore, the number must be at least the maximum concurrent connections (concurrent connections cannot exceed 1024) plus two service threads and the number of background threads. The V$PROCESS and V$BGPROCESS contain information about the Oracle threads being used. Windows NT In addition to memory required for Oracle8 and its processes, memory must also be available for Windows NT. Memory usage can be monitored via the Windows NT Task Manager - Performance window. Two primary goals in tuning memory for Windows NT are to reduce the amount of paging and swapping and to fit the SGA and PGAs into main memory. Paging or swapping is the process in which the operating system moves information from one storage location (i.e. real memory, virtual memory, disk) to another. Excessive paging or swapping can reduce performance. To monitor paging, use the Windows NT Performance Monitor - Memory - Pages Input/sec. Pages Input/sec is the number of pages read in from disk to resolve memory references to pages that weren't in memory at the time of reference. This number should be extremely low. If excessive paging occurs, either increase the total memory on your system (RAM) or decrease the amount of memory you have allocated in the SGA. Since the purpose of the SGA is to store data in memory for fast access, the SGA should always be stored in main memory. You can cause Oracle to read the entire SGA into memory when you start your instance by setting the value of PRE_PAGE_SGA to YES. This may increase the amount of instance startup time, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup. Setting PRE_PAGE_SGA to YES does not prevent Windows NT from paging or swapping the SGA after it is initially read into memory. DSS systems, as tested in the TPCD, do not typically have a high cache hit ratio. Note that memory can be better utilized in places such as hash area and sort area than in the SGA. Total Memory The total amount of memory (RAM) for a DSS system is dependent upon your SGA, PGA, number of processes and Windows NT. Thus, the impact of additional RAM is dependent upon your system and database design, as well as your query types. Figure 13 displays the different Total Query Times for a TPC-D on a Compaq ProLiant 6500, using 1M cache and 3-Gigabyte, 2Gigabyte, 1-Gigabyte and 512-Megabyte RAM. Figure 14 displays underlying data for queries significantly impacted by the addition of RAM. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398