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

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 25 • HASH_AREA_SIZE - Memory area allocated for each process to do hash join work. The hash area does not cache blocks in the buffer cache. For DSS systems, this value may range from 8MB to 32MB. Larger tables require a larger HASH_AREA_SIZE. Setting the HASH_AREA_SIZE is system dependent and should be tuned over a period of time until an acceptable hash area size is identified. • HASH_MULTIBLOCK_IO_COUNT - Determines the number of hash buckets for I/O transfer. For larger tables (greater than 100gig in size), this parameter should be increased. During our TPC-D test, HASH_MULTIBLOCK_IO_COUNT is set to 16. • HASH_JOIN_ENABLED - Should be set to TRUE to allow for hash joins. • SORT_AREA_SIZE - Specifies the maximum amount of PGA memory, which any Oracle thread can use for sorting. • SORT_WRITE_BUFFERS - If SORT_DIRECT_WRITES is set to true, each user thread will have SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE * number of parallel query processes of memory allocated to it. These buffers are then used for sorting instead of the main buffer cache. • ORDERED_NESTED_LOOP - Helps cost based optimizer to determine order of execution for each loop within a nested loop when ORDERED_NESTED_LOOP is set to TRUE. • OPTIMIZER_MODE - Use CHOOSE or COST. Do not use RULE. The default value is CHOOSE. • OPTIMIZER_PERCENT_PARALLEL - Specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans. The recommended value is 100 divided by your number of concurrent users. • PARALLEL_MIN_SERVERS - Specifies the minimum of parallel query processes for each instance. Oracle8 Server creates these processes at the instance startup. • PARALLEL_MAX_SERVERS - Specifies the maximum of parallel query processes for each instance. As the number of SQL statements processed increases, Oracle8 Server will automatically create a new parallel process as needed. It will not exceed the value specified in PARALLEL_MAX_SERVERS. • PARALLEL_SERVER_IDLE_TIME - The time which parallel servers will remain active. If parallel process has been idle for the period of time specified by PARALLEL_SERVER_ IDLE_TIME, then Oracle8 Server will terminate that process. The number of parallel processes will never drop below the value of PARALLEL_MIN_SERVERS. Please refer to Oracle8 Server Tuning for more information regarding Oracle parallel execution parameters. Conclusion Tuning a DSS system is a highly system dependent and reiterative job. One configuration may work well for one DSS system and poorly for another DSS system. When initially setting up and tuning your system, you may have to go through numerous trials before getting an acceptable level of performance. You will also want to evaluate the DSS performance after your system is set up and determine if any of the above tuning suggestions need to be reapplied. Don't worry - your users will usually inform you if this is necessary. Good luck! © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398