Hp COMPAQ PROLIANT 3000, COMPAQ PROLIANT 5500 DSS Sizing and Tuning of Oracle8 for Windows NT

DSS Sizing and Tuning of Oracle8 for

Windows NT on Compaq Servers

White Paper

________________________________________________________________

Prepared By

Database Engineering

Compaq Computer Corporation

March 1998

NOTICE

The information in this publication is subject to change without notice.

COMPAQ COMPUTER CORPORATION SHALL NOT BE LIABLE FOR TECHNICAL OR EDITORIAL ERRORS OR OMISSIONS CONTAINED HEREIN, NOR FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE, OR USE OF THIS MATERIAL.

This publication contains information protected by copyright. Except for internal use distribution, no part of this publication may be photocopied or reproduced in any form without prior written consent from Compaq Computer Corporation.

This publication does not constitute an endorsement of the product or products that were tested. The configuration or configurations tested or described may or may not be the only available solution. This test is not a determination of product quality or correctness, nor does it ensure compliance with any federal, state or local requirements. Compaq does not warrant products other than its own strictly as stated in Compaq product warranties.

Product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

Compaq, ProLiant

Registered U.S. Patent and Trademark Office.

DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq

Servers

First Edition (March 1998)

Document Number ECG156/0398

Compaq Computer Corporation

Table of Contents

Introduction .........................................................................................................................................2

TPC Benchmark D (TPC-D) ................................................................................................................2

DSS vs. OLTP......................................................................................................................................2

Oracle Overview ..................................................................................................................................2

Architecture ..................................................................................................................................2

New Features in Oracle8 Version 8.0 ...................................................................................................6

Basic Tuning........................................................................................................................................7

Installation Issues ..........................................................................................................................7

Tuning Goals.......................................................................................................................................13

I/O Tuning...........................................................................................................................................13

Disk Layout................................................................................................................................... 13

Partitioning...................................................................................................................................14

Parallelism....................................................................................................................................15

Availability vs. Performance..........................................................................................................17

Array Controllers ..........................................................................................................................18

I/O Limits .....................................................................................................................................18

System Processor Scalability ................................................................................................................ 19

Memory Tuning...................................................................................................................................21

System Global Area (SGA) Size....................................................................................................21

Program Global Area (PGA) Size ..................................................................................................21

Processes.......................................................................................................................................22

Windows NT.................................................................................................................................22

Total Memory ............................................................................................................................... 22

Conclusion...........................................................................................................................................25

Appendix A: TPC-D Schema ................................................................................................................27

Appendix B: TPC-D Query Definitions.................................................................................................31

Appendix C: TPC-D Queries (SQL Code)............................................................................................. 35

Page 2 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

Introduction

The purpose of this document is to share the knowledge acquired by Compaq Systems Engineers in the area of configuration and performance tuning of Decision Support Systems using the Oracle8 Database and Microsoft Windows NT on Compaq servers. The system tested by Compaq represents a single query stream TPC Benchmark D (TPC-D) on the Compaq ProLiant family of Servers. General information and query data for the TPC-D benchmark are included in this paper. It is our desire to deliver the best technical information possible on a specific topic in a timely manner and in a highly useable format. Any comments, suggestions and feedback are always appreciated.

TPC Benchmark D (TPC-D)

The TPC Benchmark D is a decision support benchmark, which consists of business oriented ad-hoc queries and concurrent updates. The benchmark illustrates decision support systems that examine large volumes of data; execute queries with a high degree of complexity; and gives answers to critical business questions. The TPC-D benchmark evaluates performance of various decision support systems by the execution of sets of seventeen queries against a standard database. Appendix A of this document contains the schema of the TPC-D database, appendix B contains descriptions of each of the seventeen queries, and appendix C contains the SQL code for each of the seventeen queries used in the TPC-D benchmark examples. A full description of the TPC-D specification is available from the Transaction Processing Performance Council (Phone: 408-295-8894, Web site: www.tpc.org/dspec.html).

DSS vs. OLTP

Decision Support Systems (DSS) is a term used to describe the capability of a system to support the formulation of business decisions through complex queries against a database. It can also specifically refer to a database which is intended for this purpose, as opposed to one which primarily supports on-line transaction processing (OLTP) operations. Decision Support is different from OLTP. OLTP applications are update-intensive and generally consist of shorter transactions that access a small portion of a database, often through a primary key or index. Decision support applications typically consist of long and often complex read-only queries that access large portions of the database. Decision support databases are updated relatively infrequently, either by periodic batch runs, or by background "trickle" update streams. The database need not contain real-time or up-to-the-minute information, as decision support applications tend to process large amounts of data which usually would not be affected significantly by individual transactions.

Oracle Overview

The information in this document contains information related to Oracle8 Server version 8.0.4 for Windows NT version 4.0 or later. Because there is information available concerning generic tuning of Oracle8 Server, this document focuses on specific tuning suggestions for DSS on Compaq servers and Windows NT. A section on general Oracle8 architecture is included as a part of the Oracle Overview. Tuning sections follow the Oracle Overview. Wherever possible, this White Paper references other useful tuning documentation.

Architecture

Oracle8 Server for Windows NT is a 32-bit Windows NT application. Oracle8 Server is implemented on Windows NT as a single process, multi-threaded architecture. Each Oracle8 Server instance consists of a single process with multiple threads. The number of threads associated with the Oracle8 Server process varies depending upon options selected (background threads) and user connections (shadow threads). Note that Compaq disk subsystems provide asynchronous I/O, therefore no DBWR and LGWR I/O slave threads are required. The maximum number of shadow threads, and thus the maximum number of user connections, is

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 3

1024. The Windows NT Performance Monitor displays the process (ORACLE80) and threads (Threads 0,1 represent the Oracle service threads). If ARCHIVELOG mode is enabled, threads 2,3,4,5,6,7,8

represent the background threads [PMON, DBWR, ARCH, LGWR, CKPT, SMON and RECO respectively]. If ARCHIVELOG mode is not enabled, threads 2,3,4,5,6,7 represent the background threads [PMON, DBWR, LGWR, CKPT, SMON and RECO respectively] as shown in Figure 1. The process ID for each thread is listed in the “Last”, “Average”, “Min” and “Max” boxes in Performance Monitor. Descriptions of each background thread can be found in Figure

3. Figure 1: Performance Monitor – Oracle8 Server process and threads

The Oracle8 Server process is represented under Windows NT as a service (OracleServiceSSSS [SSSS= System ID]) associated with the executable ORACLE80.EXE. The Oracle Service consists of two threads, 0 and 1. Thread 0 is the Oracle process main thread acting as a service dispatcher and creating thread 1 to handle the service.

Each Oracle instance is associated with a specific Windows NT service and consists of a single process and multiple threads. Use the Windows NT Control Panel/Services to display the Oracle Services. OracleServiceTPCD represents the Oracle Instance TPCD as shown in Figure 2.

Page 4 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

Figure 2: Control Panel/Services – Oracle8 Server instances

NOTE: Performance Monitor does separate the instances on the chart, log, and report. However

the instances are all identified as “oracle80”, making it very difficult to monitor multiple instances using Performance Monitor.

An Oracle instance consists of the System Global Area (SGA) and the Oracle service/ background/ shadow threads. Via an Oracle instance, an Oracle database can be created and accessed.

The SGA is a sharable memory construct that contains the following:

• Database Buffers Contains most recently used database blocks

• Shared Pool Contains shared SQL areas and data dictionary cache

• Redo Log Buffers Logs changes made to the database

Figure 3:

The Oracle8 Server background threads consist of the following:

Thread Abbreviation Description

Process Monitor PMON Responsible for the cleanup of abnormally terminated

connections. Database Writer DBWR Writes database blocks to datafiles. Log Writer LGWR Writes the redo log entries to logfile. Checkpoint Process CKPT Signals DBWR to perform updates on all data and

control files of the database. If not present, LGWR does

this. System Monitor SMON Performs instance recovery and cleanup. Recovery Process RECO Resolves failures with the distributed option. Archival Process ARCH Copies full online redo log files to the archive device (if in

ARCHIVELOG mode).

A dedicated shadow thread is a separate dedicated server thread, which acts on behalf of a particular user. One thread is created for each user who connects to the database. Any request that a user has for the database is performed through the shadow thread for that user. Oracle8 Server version 8.0.4 supports a multithreaded server (MTS) environment. An MTS server thread can service requests from any client.

When a shadow thread must read from the database, it checks to see if the data exists in the SGA. If the data exists in the SGA, the shadow thread reads it from the memory. If the data is not found in memory, the shadow thread goes directly to the datafiles and reads the data into the

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 5

SGA. When a shadow thread must write to the database, it writes into the SGA only. At a later time the DBWR writes this “dirty” data out to disk.

Parallel execution begins with a shadow thread executing a SQL statement that contains operations, some of which may be performed in parallel. This becomes the shadow parallel coordinator. It dispatches the execution of a statement to several parallel server threads and coordinates the results from the parallel server threads to send the results back to the user. The number of parallel server threads assigned to a single operation is called the “degree of parallelism”.

The redo log contains a history of all committed transactions for the database to perform an instance recovery. A minimum of two redo logs is required and more may be used. When a redo log fills, a log switch occurs. At log switch time all new redo information goes to the next redo log file in line. If the system is running in ARCHIVELOG mode, which is recommended, the previous log file is copied out to an archive log file.

With a recent backup, the redo log files, and the redo log archive files, the database can be recovered if needed. The control and configuration files are used to store information of the state and layout of the database as well as system tunables. A more in-depth discussion of the Oracle8 Server architecture can be found in the Oracle8 Server Concepts Manual.

Page 6 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

New Features in Oracle8 Version 8.0

Oracle8 version 8.0 is a major upgrade from Oracle7 version 7.3 and contains many new features. The features covered in this paper are briefly outlined below. For more details on Oracle8 new features, refer to the Appendix A of the Oracle8 Server Migration Manual.

New Feature Description

Partitioned Tables and Indexes Divides large tables and indexes into smaller and more

manageable pieces (partitions). Oracle8 includes features to create, manage, backup and recover, and utilize partitions for better query and DML performance.

ROWID Enhancement Oracle8 ROWIDs have been modified to handle partitioned tables

and tablespace-relative data block addresses (DBAs). The new 10-byte ROWID contains the data object number, data block address and row number of the row.

Data Dictionary Data Dictionary has more views providing extended information

on partitions, parallel server and latches/locks. Also some views have additional columns defined to include new features and functionality.

Reverse-key indexes Reverses the bytes of each column indexed, except the ROWID,

but keeps the column order. Enables insertions to become distributed across all leaf keys in the index.

SQL*Loader Partitioned Object Support

Supports loading partitioned objects into the database. The direct path has been changed to accommodate mapping rows to partitions of tables and to support local and global indexes. Parallel direct-path includes concurrent loading of an individual partition as well as support for concurrent loading of a partitioned table.

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 7

Basic Tuning

This section contains basic tuning information as it pertains to installation and configuration of the hardware, operating system and Oracle8 server. The Tuning sections of this paper which follow contain information for tuning your DSS system.

Installation Issues

Flashable ROM

Apply the latest Compaq System ROMPaq and Option ROMPaq to the ProLiant Server.

Compaq System Configuration

During configuration of the ProLiant Server using the latest Compaq System Configuration utility, several settings are recommended.

System

Primary Operating System = Windows NT version 4.0 or later

Specifies the Primary Operating System for initial configuration options.

Compaq Memory

Base Memory = 640 Kbytes, Linear

Contiguously maps all memory so that Compaq Built-In Memory is not available at FA0000, but is instead added into extended memory. The previously reserved memory area at FE0000 is now available for use.

Array Configuration Utility

Controller Characteristics- Operating System = Windows NT 3.5x or later

This sets the drive geometry for the controller to be 32 sectors per track and the striping factor to be 256 for RAID-0 and RAID-1. The drive geometry for the controller is 32 sectors per track and the striping factor is 32 for RAID-5.

Log Drives: Array Accelerator Status - Logical Drive # = Disabled Non-Log Drives: Array Accelerator Status - Logical Drive # = Enabled

This increases performance of the controller by writing data to RAM on the controller instead of directly to disk. The RAM on the controller is mirrored and backed up by batteries for increased reliability. The memory for the accelerator is either 4MB or 16MB, depending upon the version of the controller board. The accelerator cache can be configured at 100/0 percent, 75/25 percent, 50/50 percent, 25/75 percent or 0/100 percent for the amounts of memory that will be reserved for the read-ahead/write-posting functionality of the accelerator.

Page 8 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

Windows NT

Prior to or immediately following installation of Oracle8 Server for Windows NT, there are several recommended changes to the Windows NT configuration.

Compaq Software Support for Microsoft Windows NT 4.0

Install the latest version of the Compaq Software Support Diskette to apply the latest drivers specific for Windows NT on Compaq hardware. The available drivers are listed below.

• 10/100 UTP Netflex3

• SMART Controller CPQARRAY

• 32-bit SCSI-2/Integrated PCI SCSI-2 adapters

• ProLiant Storage System

• SCSI Compression adapter

• System Management/Health driver

• UPS support

• Multiprocessor HAL support

The ECI driver for the SMART Controller CPQARRAY can be obtained from the Compaq web site at /www.compaq.com/support/files/server/winnt/index.html.

Microsoft Windows NT 4.0 Workstation and Server Service Pack

Install the latest version of the Service Pack to apply the latest fixes from Microsoft.

Memory

The Windows NT Enterprise Edition increases the per-process address limit from 2GB to 3 GB. This feature benefits applications that run on Compaq ProLiant systems with more than 2 GB of physical RAM and that can take advantage of a larger address space. If you have greater than 2GB RAM, you will need to install the Windows NT Enterprise edition and modify the boot.ini file by adding the /3GB parameter to the startup line. For example,

[boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00" /3GB multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00 [VGA mode]" /basevideo /sos/basevideo /sos

Virtual memory is your real memory (RAM) plus your swap file size. The virtual memory should be approximately one to one and a half times that of your physical memory (RAM). The virtual memory should not exceed twice your physical memory as the paging rate may go up during peak times, resulting in decreased performance. Virtual memory is set in the Windows NT Control Panel – System – Performance – Virtual Memory. Note: if you using larger amounts of RAM, it may not be practical to set your virtual memory to one to one and a half times that of your physical memory. When setting your virtual memory and swap file size, keep in mind that you want to reduce your amount of paging and swapping.

Server Configuration

Use Control Panel/Network to choose Server from the Installed Network Software list and Configure. Change the relationship of memory allocated to the network connections and memory allocated to applications running on the server by choosing Maximize Throughput for Network Applications (the default is Maximize Throughput for File Sharing). This optimizes the server

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 9

memory for the Oracle8 Server because it performs its own memory management for caching file and network I/O. See Figure 4.

Figure 4: Control Panel/Network/Services/Server - memory configuration

Network Protocols

Use Control Panel/Network to choose any protocol not required for the activities on the server from the Installed Network Software List and Remove. See Figure 5.

Figure 5: Control Panel/Network - network protocols

Page 10 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

Server and Workstation Network Bindings

Use Control Panel/Network to choose Bindings to configure the Server and Workstation network bindings path. Move the protocol of primary use to the top followed by the protocol of second highest usage and so forth on both the Server and Workstation to reduce the average connection time. See Figure 6.

Figure 6: Control Panel/Network/Bindings - protocol binding order

Services

If the Oracle8 Server is dedicated to being a database server only, various services may be stopped to reduce overhead on the processor(s). The minimum service needed is OracleServiceSSSS [SSSS is the System ID], but Compaq also recommends the Server, EventLog and Workstation services. One or more services, depending upon the versions of Net8 and protocols supported, will be required to support user connections through Net8 (example, OracleTNSListener80). Turn off services using the Control Panel/Services (you may also want to change the service’s Startup option to Manual). See Figure 7.

Figure 7: Control Panel/Services

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 11

Windows NT File System

Compaq testing indicated a 4%-7% performance gain when using raw devices over FAT or NTFS file systems in Windows NT. Compaq recommends using FAT for the initial boot partition to allow booting from a DOS diskette in emergencies to do repair work. Windows NT and Oracle executables are placed on the FAT partition as well. The system, log and data files are normally placed on NTFS or RAW file partitions. If you are I/O bound with your system, you may switch the log and data files to RAW partitions to gain I/O performance. You will, however, be limited by the restrictions of using RAW devices (i.e. backup, copy, etc.). Using Oracle8’s backup utility, OCOPY80, and MKS Toolkit’s dd functionality, you can minimize the impact of these restrictions. Each RAW partition equates to a single file and can be represented by a single drive letter (i.e., \\.\E:), or logical physical drive number (i.e., \\.\PhysicalDrive0) or, if you have many partitions per disk, as symbolic links (i.e., \\.\ORA_PARTITION_1 where ORA_PARTITION_1 is defined using Oracle’s setlinks utility).

Oracle8 Server

Install the Oracle partition option. This option is needed for Oracle parallel query and partition functionality.

Use ORADIM80 to create each Oracle8 instance. ORADIM80 (Oracle Database Instance Manager) creates each database instance and assigns the administrator user ID and password. If you are recreating an Oracle8 instance, you will want to delete the old instance using:

oradim80 -delete -sid SSSS

before creating the new instance using:

oradim80 -new -sid SSSS -intpwd PWD -maxusers U -startmode manual

where SSSS is your instance ID, PWD is your internal password and U is the maximum number of internal users.

Use the Windows NT Registry Editor to make any necessary changes to the Oracle configuration parameters (for example, ORACLE_SID, LOCAL, ORACLE_HOME, and so on). The default setting for ORACLE_PRIORITY is CLASS:normal;DEF:normal. This can be set to CLASS:HIGH;DEF:7. The console will seem sluggish but can be fixed by increasing the foreground window priority under Control Panel – System - Performance.

See Figure 8 for Windows NT Registry settings for ORACLE.

Page 12 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers

Figure 8: Registry Editor

+ 31 hidden pages