Compaq ProLiant 6500 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 15

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 13 Tuning Goals There are several factors involved in achieving the best performance from your system. This paper focuses on the hardware, Oracle8 Server, and the operating system. It is important to tune the application software to take advantage of the system resources. Due to the diversity of applications, tuning application software is beyond the scope of this paper. For more information on the tuning of applications, refer to the Oracle8 Server Application Developer's Guide. When tuning a DSS system, the primary goal is to shorten the time it takes to run each query as much as possible. Since each query may differ significantly, it is likely that tuning the system to shorten one query may lengthen another. It is, then, important to determine which queries need to be reduced for acceptable response times across the board. Your most frequently run queries should be tuned first. Once these are tuned, or if you are unsure which of the queries should be tuned, you should tune your longest and your shortest running queries. Tuning your longest running queries can significantly reduce your query response time, while tuning your short running queries will free up high contention resources and ease the contention in many cases as well. This paper is the end result of tuning and evaluating three main areas of a DSS system: I/O, CPU and memory. I/O Tuning Due to the sequential nature of DSS systems, tuning I/O on DSS is very different from that of OLTP. Adding disk spindles will often reduce I/O contention in OLTP systems, due to their random I/O access. In a DSS system, you will need to tune I/O beyond the simple addition of disk spindles. This section will discuss I/O tuning for a DSS system in regards to disk layout, partitioning, parallelism, availability, performance and I/O limits. Disk Layout Disk Layout and partitioning are two of the most important areas of I/O tuning and DSS performance. It is helpful to know the partitioning requirements of your system prior to determining your disk layout. Within the area of disk layout, the following recommendations have been tested on Compaq ProLiant systems and offer optimal DSS performance. • Since DSS is extremely read intensive, you will want to balance your largest tables (i.e. DSS fact tables) across as many drives as possible. For example, in the TPC-D, we balanced the part, partsupp, lineitem, orders, supplier and customer tables across 40 drives, putting 1/40th of each table and its index on one drive. Smaller objects, including the system tablespace, rollback segments, and nation and region tables, are combined on a single drive. Each partition can be assigned to its own tablespace, which allows the user to assign partitions to physical drives. In the TPC-D example, each 1/40th of the tables is on its own partition, which is tied to a specific drive (i.e. partsupp is on tablespaces PS_01, PS_02...PS_40). With partitioning, this will reduce your read time by splitting it evenly among your drives. • There should be one logical drive per physical disk, which will allow for one partition per physical disk for each database object. (i.e. If you have a table that has 40 partitions, you will want to set up 40 logical drives, as separate disks or group of disks, to contain that table) This will reduce the disk contention when reading from or writing to disk and increase the amount of sequential access, which can be much faster than random I/O. Depending upon the query, indexes may still provide better performance (i.e. index-only retrieval). Once the disk layout has been determined, the Compaq Array Configuration Utility and Windows NT Disk administrator are used to configure the disks and create the partitions. In the Compaq Array Configuration Utility, one array needs to be created for each logical disk. (i.e. If © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398