Compaq ProLiant 6500 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq
Compaq ProLiant 6500 Manual
View all Compaq ProLiant 6500 manuals
Add to My Manuals
Save this manual to your list of manuals |
Compaq ProLiant 6500 manual content summary:
- Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 1
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers White Paper Prepared By Database Engineering Compaq Computer Corporation March 1998 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 2
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 3
...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 © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 4
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 5
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. © 1998 Compaq Computer Corporation, All Rights Reserved - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 6
for Windows NT on Compaq Servers Figure 2: Control Panel/Services - Oracle8 Server perform updates on all data and control files of the database. If not present, Server version 8.0.4 supports a multithreaded server (MTS) environment. An MTS server thread can service requests from any client - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 7
. 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. © 1998 Compaq Computer Corporation, All Rights - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 8
new features, refer to the Appendix A of the Oracle8 Server Migration Manual. New Feature Partitioned Tables and Indexes ROWID Enhancement Data Dictionary Reverse-key indexes SQL*Loader Partitioned Object Support Description Divides large tables and indexes into smaller and more manageable pieces - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 9
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 10
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 11
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 12
, 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 © 1998 Compaq Computer Corporation, All - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 13
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 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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 14
Page 12 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Figure 8: Registry Editor © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 15
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 of disk layout, the following recommendations have been tested on Compaq ProLiant systems and offer optimal DSS performance. • Since DSS - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 16
Page 14 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers you have 14 disks attached to one controller, you will create 14 arrays setting up partitions in Oracle8, you will need to consider the following. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 17
index on a partitioned table. Oracle8 supports prefixed and non-prefixed local indexes Compaq 9.1-Gigabyte drive), allowing for a decrease in the response time. For more information on partitioning, please refer to the Oracle8 Server Concepts Manual or the Oracle8 Server Application Developer's Guide - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 18
Page 16 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Parallel Execution When Oracle is not parallelizing the execution of SQL statements, each SQL statement is done sequentially by a single process. With parallel execution, - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 19
objects. The degree of parallelism for each table should be a factor of the number of partitions in the table. Optimizing TPC-D benchmarks on Compaq ProLiant machines has shown that the average degree of parallelism per object should be 2-3 times the number of CPUs. For small objects or objects - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 20
18 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers other drives. Log drives should be placed on a RAID-1 drive, so that no log files are lost in the case of a single disk failure. Array Controllers The Compaq SMART Array Controller is preferable to standard SCSI controllers. The - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 21
per drive limit. Hence, you would not have to add more disk drives or rearrange files if you were using 9.1-Gigabyte drives. Figure 10: Performance Monitor - Disk I/O System Processor time that each will require. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 22
faster processors. Adding cache provided minimal improvement, depending upon the number of processors used. Figure 11 displays results from tests on the Compaq ProLiant 5500 and 6500 servers (both Pentium Pro processors), using 3G RAM and 512K cache. They display processor scalability for 1, 2 and - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 23
of free server memory is a limitation to the number of concurrent connections. The PGA's size is affected by the following parameters: • OPEN_LINKS • DB_FILES • LOG_FILES © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 24
maximum concurrent connections (concurrent connections cannot exceed 1024) plus two service threads and the number of background threads. The V$PROCESS 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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 25
the Oracle process from using all 3GB of virtual address space. Figure 13: Compaq ProLiant 6500, 1M cache (3G, 2G, 1G, 512M RAM) - TPCD 6500, 1M cache 800 700 600 500 400 300 3G 2G 1G RAM 512M Figure 14: Compaq ProLiant 6500, 1M cache (3G, 2G, 1G, 512M RAM) - Selected TPCD query data. - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 26
DB_FILE_MULTIBLOCK_READ_COUNT - Determines how many database blocks are read with a single operating system read. The maximum I/O size of the Compaq SMART-2 controller is 64K bytes. Thus, the DB_FILE_MULTIBLOCK_READ_COUNT should be set to 64K/DB_BLOCK_SIZE or a multiple thereof. For a DB_BLOCK_SIZE - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 27
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 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 28
Page 26 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers We would welcome feedback from your configurations and experiences to improve our information products in the future. Please send us any comments or suggestions - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 29
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix A Appendix A: TPC-D Schema Note: SF = Scale Factor of TPC-D database (i.e. variable text, size 199 Compound Primary Key: PS_PARTKEY, PS_SUPPKEY © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 30
Appendix A DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers CUSTOMER Table Layout Column Name C_CUSTKEY C_NAME C_ADDRESS C_NATIONKEY Datatype Requirements identifier variable text, size 25 variable text, size 40 identifier C_PHONE fixed text, - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 31
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix A LINEITEM Table Layout Column Name Datatype Requirements Comment variable text, size 152 Compound Primary Key: L_ORDERKEY, L_LINENUMBER © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 32
Appendix A DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers REGION Table Layout Column Name Datatype Requirements R_REGIONKEY identifier R_NAME fixed text, size 25 R_COMMENT variable text, size 152 Primary Key: R_REGIONKEY TIME Table - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 33
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix B: TPC-D Query Definitions Appendix B Query 1 - Pricing Summary Report TPC-D Business Question: Provides a summary pricing report for all Lineitems shipped as of a given date, a - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 34
Appendix B DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers year out of seven). The largest detail table has no direct selection applied sort determines which 20 rows are to be displayed in the answer set. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 35
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix B Query 11 - Important Stock Identification TPC-D Business Question: Finds, from scanning the available stock of Suppliers in a given Nation, all the Parts that - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 36
Appendix B DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Functionality: Query 15 requires either a view with aggregates or a temporary table. In the view, 1/28th of the Lineitems (3 months) are selected based on date - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 37
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C: TPC-D Queries (SQL Code) Appendix C Query 1 - Original SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS SUM_DISC_PRICE, - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 38
Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Query 3 - Original set_fetchrows=10; SELECT L_ORDERKEY, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE, TO_CHAR(O_ORDERDATE, 'YYYY-MM-DD'), O_SHIPPRIORITY FROM CUSTOMER, ORDERS, LINEITEM WHERE C_MKTSEGMENT = 'MACHINERY' - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 39
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C Query 6 - Original SELECT SUM(L_EXTENDEDPRICE * L_DISCOUNT) AS REVENUE , NATION N2, REGION WHERE P_PARTKEY = L_PARTKEY AND S_SUPPKEY = L_SUPPKEY © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 40
Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers AND L_ORDERKEY = O_ORDERKEY AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND N1.N_REGIONKEY = R_REGIONKEY Query 8 - Variant B (Con't.) AND R_NAME = 'AMERICA' AND S_NATIONKEY = N2.N_NATIONKEY - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 41
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C AND O_ORDERDATE < ADD_MONTHS(TO_DATE('1995-01-01' ,'YYYY-MM-DD'),3) AS HIGH_LINE_COUNT, SUM(DECODE(O_ORDERPRIORITY,'1-URGENT',0,'2-HIGH',0,1)) AS © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 42
Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers LOW_LINE_COUNT FROM ORDERS, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND L_SHIPMODE IN ('SHIP','RAIL') AND L_COMMITDATE < L_RECEIPTDATE AND L_SHIPDATE < L_COMMITDATE Query 12 - Variant B (Con't.) AND L_RECEIPTDATE >= - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 43
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers INSERT INTO SUM_PROMO_SALES0 SELECT SUM(AMOUNT) FROM ALL_SALES0 WHERE TYPE LIKE MAX(TOTAL_REVENUE) FROM REVENUE0 ) ORDER BY S_SUPPKEY; DROP TABLE REVENUE0; © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 44
Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Query 16 - Original SELECT P_BRAND, P_TYPE, P_SIZE, COUNT( L_QUANTITY < (SELECT 0.2* AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY); © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - Compaq ProLiant 6500 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 45
Oracle8 q Other: Processing Type q On-Line Transaction Processing q Decision Support q Batch Processing q Other: Please indicate the type of information you Performance Engineering Compaq Computer Corporation MailCode 090803 20555 SH 249 Houston, Texas 77070 © 1998 Compaq Computer Corporation,
DSS Sizing and Tuning of Oracle8 for
Windows NT on Compaq Servers
White Paper
________________________________________________________________
Prepared By
Database Engineering
Compaq Computer Corporation
March 1998