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

Page 24 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers of 2/7 of the Lineitem rows and 2/5 of the Customers and Suppliers rows. In this query, our generalization based on the configuration of 1G RAM would not be optimal. Query 9 is optimized using 2G RAM. Like Query 7, Query 9 does a 6-table join, but has selection criteria applied to only one small table. 512M and 1G RAM do not offer enough memory for optimally joining the six tables. Note that additional RAM (i.e. 3G in query 9, or 2G or 3G in query 11) may hurt performance, which is most likely due to overhead of loading memory which may not be used efficiently. Query 11 contains a three-table join of moderate and small size tables. However, this query returns millions of rows. Hence, 512M is too small for the sort size of this query. Looking at the data, 1G RAM should be sufficient. Query 15 has a one-second change in query time from 512M to 3G. It does a simple aggregate function, which can be processed within 512M RAM. Thus, the addition of RAM beyond 512M does not help. In Query 17, correlated subquery functionality is used to perform what-if analysis. It joins the largest table, Lineitem, to Part in both inner and outer queries. 1/1000 of the rows in the Part table qualifies based on the selection criteria. 512M RAM is not enough memory to store the qualifying rows. An additional 512M RAM provides enough memory to store all the qualifying rows, prior to doing the AVERAGE aggregate function. Other INIT.ORA Parameters This white paper has covered I/O, CPU and Memory tuning. Where applicable, INIT.ORA parameters (in all CAPITAL letters) have been discussed. This section contains additional INIT.ORA parameters, or a further explanation of previously discussed parameters, that should be set for a DSS system. When tuning a DSS system, it is important to realize that optimization of parameters for one application do not necessarily transfer to other applications. There is some degree of tweeking that must be done to optimize your DSS system. • DB_BLOCK_SIZE - Must be defined prior to database creation, as a change in DB_BLOCK_SIZE requires the database to be rebuilt. Should be set to the largest value possible. In a DSS system on Oracle 8.0.4 for Windows NT, DB_BLOCK_SIZE should be set to 8K or 16K. • DB_BLOCK_BUFFERS - Will be lower in a DSS environment than in an OLTP environment. (DB_BLOCK_BUFFERS*DB_BLOCK_SIZE) Dimension tables and amount of updates will dictate the size of this. Full table scans, full index scans and full partition scans will bypass the main buffer cache. • 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 of 16K, the recommended DB_FILE_MULTIBLOCK_READ_COUNT is 4. • SHARED_POOL_SIZE - Should be large enough to hold all data dictionary, shared SQL and compiled objects. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398