HP ProLiant 3000 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser
HP ProLiant 3000 Manual
View all HP ProLiant 3000 manuals
Add to My Manuals
Save this manual to your list of manuals |
HP ProLiant 3000 manual content summary:
- HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 2
trademarks of their respective companies. © 1998 Compaq Computer Corporation All rights reserved. Printed in the USA 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 3
...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 © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 4
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 benchmark evaluates performance of various decision support systems by the execution of sets of - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 5
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. © 1998 Compaq Computer Corporation, All - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 6
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 memory. If the data is not found in memory, the shadow thread goes directly to the datafiles and reads the data into the © 1998 Compaq Computer - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 7
server threads and coordinates the results from the parallel server threads to send the results back to the user. The number of parallel server depth discussion of the Oracle8 Server architecture can be found in the Oracle8 Server Concepts Manual. © 1998 Compaq Computer Corporation, All Rights Reserved - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 8
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers New Features in Oracle8 Version 8.0 Oracle8 version 8.0 is Oracle8 Server Migration Manual. New Feature Partitioned Tables and Indexes ROWID Enhancement Data Dictionary Reverse-key indexes SQL*Loader Partitioned Object Support - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 9
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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 10
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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 11
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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 12
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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 13
or NTFS file systems in Windows NT. Compaq recommends using FAT for the initial boot using Oracle's setlinks utility). Oracle8 Server Install the Oracle partition option. startmode manual where SSSS is your instance ID, PWD is your internal password and U is the maximum number of internal users. Use - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 15
Oracle8 Server Application Developer's Guide. end result of tuning and evaluating three main areas of a DSS system: I/O, CPU and memory on Compaq ProLiant 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 17
table. Oracle8 supports prefixed and on specific drives Compaq 4.3-Gigabyte Ultra to a 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 18
Oracle8 for Windows NT on Compaq Servers Parallel Execution When Oracle is not resource of your system like memory or CPU bandwidth for example. • If a table has both a PARALLEL hint specification in the query and a users. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/ - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 19
-D benchmarks on Compaq ProLiant machines has shown server processes comes from the process memory, which in turn comes from virtual memory. You will need to make sure that you have enough memory for all of your processes. The PROCESSES parameter specifies the maximum number of operating system user - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 20
using the Compaq SMART Array Controllers. This information is specific to the Compaq SMART-2/P and percent or 0/100 percent for the amounts of memory that will be reserved for the read-ahead/ in the Compaq Database Engineering White Paper Configuring Compaq RAID Technology for Database Servers, you - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 21
Disk I/O System Processor Scalability Oracle8 Server for Windows NT provides good system processor improvement from the addition of more powerful processors and more processors. Additional cache number of users and the processor time that each will require. © 1998 Compaq Computer Corporation - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 22
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 4 processors. Total Query Time - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 23
at the connect time for a particular user, therefore the amount 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 24
user processes that connect to the Oracle instance. This number must also include the Oracle service, background, and parallel server memory required for Oracle8 and its processes, memory must also be available for Windows NT. Memory for a TPC-D on a Compaq ProLiant 6500, using 1M cache and 3-Gigabyte - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 25
on CompaqServers Page 23 NOTE: There is a constraint in Oracle8 memory usage which prohibits the Oracle process from using all 3GB of virtual address space. Figure 13: Compaq ProLiant 6500, 1M cache (3G, 2G, 1G, 512M RAM) - TPCD Query Times ProLiant 6500, 1M cache 800 700 600 500 400 300 3G 2G - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 26
Windows NT on Compaq Servers of 2/7 of performance, which is most likely due to overhead of loading memory which may not be used efficiently. Query 11 contains a environment than in an OLTP environment. (DB_BLOCK_BUFFERS*DB_BLOCK_SIZE) Dimension tables and amount of updates will dictate the size - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 27
user thread will have SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE * number of parallel query processes of memory PARALLEL_SERVER_IDLE_TIME - The time which parallel servers will remain active. If parallel users will usually inform you if this is necessary. Good luck! © 1998 Compaq Computer Corporation, All - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 on - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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. For text, size 199 Compound Primary Key: PS_PARTKEY, PS_SUPPKEY © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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, size - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 31
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix A LINEITEM Table Layout Column Name Datatype Requirements Comment L_ORDERKEY identifier Foreign key reference to O_ORDERKEY L_PARTKEY identifier Foreign key reference to PS_PARTKEY L_SUPPKEY identifier - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 Layout - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 33
Compaq Servers Appendix B: TPC-D Query Definitions Appendix B Query 1 - Pricing Summary Report TPC-D Business Question: Provides a summary pricing , for each Part of a certain type and size, the Supplier who can supply it at minimum cost. If several suppliers in that region offer the desired part - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 34
Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers year out of seven). The largest detail table has the fraction of the revenue from the products of a specified type in that Region that was supplied by Suppliers from the given Nation. The query determines this for the years 1995 and 1996. - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 represent a - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 36
Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Functionality: Query 15 requires either a view with /Supplier Relationship TPC-D Business Question: Counts the number of Suppliers who can supply Parts that satisfy a particular customer's requirements. The Customer is interested in - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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, SUM - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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' - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 FROM LINEITEM WHERE L_SHIPDATE >= TO_DATE('1997-01-01','YYYY-MM-DD') AND L_SHIPDATE < ADD_MONTHS(TO_DATE('1997- - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 AND - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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) 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 >= - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - 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(DISTINCT < (SELECT 0.2* AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY); © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398 - HP ProLiant 3000 | DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 45
Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C User Registration/Evaluation Form Please fill out q Other: RDBMS q Microsoft SQL Server q Sybase System 10 q Oracle8 q Other: Processing Type q On-Line Transaction Processing q Decision Support q Batch Processing q Other: Please
DSS Sizing and Tuning of Oracle8 for
Windows NT on Compaq Servers
White Paper
________________________________________________________________
Prepared By
Database Engineering
Compaq Computer Corporation
March 1998