Setting Up Your Data Warehouse System

Balanced Hardware Configuration

A properly sized and balanced hardware configuration is required to maximize data warehouse performance. The following sections discuss some important considerations in achieving this balance:

• How Many CPUs and What Clock Speed Do I Need?

• How Much Memory Do I Need?

• How Many Disks Do I Need?

• How Do I Determine Sufficient I/O Bandwidth?

Verifying the Data Warehouse Hardware Configuration

Before you install Oracle Database, you should verify your setup on the hardware and operating-system level. The key point to understand is that if the operating system cannot deliver the performance and throughput you need, Oracle Database will never be able to perform according to your requirements. Two tools for verifying throughput are the dd utility and Orion, an Oracle-supplied tool.

bs=BYTES: Read BYTES bytes at a time; use 1 MB
count=BLOCKS: copy only BLOCKS input blocks
if=FILE: read from FILE; set to your device
of=FILE: write to FILE; set to /dev/null to evaluate read performance; 
  write to disk would erase all existing data!!!
skip=BLOCKS: skip BLOCKS BYTES-sized blocks at start of input

dd bs=1048576 count=200 if=/raw/data_1 of=/dev/null &
dd bs=1048576 count=200 skip=200 if=/raw/data_1 of=/dev/null &
dd bs=1048576 count=200 skip=400 if=/raw/data_1 of=/dev/null &
dd bs=1048576 count=200 skip=600 if=/raw/data_1 of=/dev/null &
dd bs=1048576 count=200 skip=800 if=/raw/data_1 of=/dev/null &
dd bs=1048576 count=200 if=/raw/data_2 of=/dev/null &
dd bs=1048576 count=200 skip=200 if=/raw/data_2 of=/dev/null &
dd bs=1048576 count=200 skip=400 if=/raw/data_2 of=/dev/null &
dd bs=1048576 count=200 skip=600 if=/raw/data_2 of=/dev/null &
dd bs=1048576 count=200 skip=800 if=/raw/data_2 of=/dev/null &

$ orion -run simple -testname mytest -num_disks 8

Orion VERSION 10.2

Command line:
-run advanced -testname orion14 -matrix point -num_large 4 -size_large 1024
-num_disks 4 -type seq -num_streamIO 8 -simulate raid0 -cache_size 0 -verbose

This maps to this test:
Test: orion14
Small IO size: 8 KB
Large IO size: 1024 KB
IO Types: Small Random IOs, Large Sequential Streams
Number of Concurrent IOs Per Stream: 8
Force streams to separate disks: No
Simulated Array Type: RAID 0
Stripe Depth: 1024 KB
Write: 0%
Cache Size: 0 MB
Duration for each Data Point: 60 seconds
Small Columns:, 0
Large Columns:, 4
Total Data Points: 1

Name: /dev/vx/rdsk/asm_vol1_1500m    Size: 1572864000
Name: /dev/vx/rdsk/asm_vol2_1500m    Size: 1573912576
Name: /dev/vx/rdsk/asm_vol3_1500m    Size: 1573912576
Name: /dev/vx.rdsk/asm_vol4_1500m    Size: 1573912576
4 FILEs found.

Maximum Large MBPS=57.30 @ Small=0 and Large=4

How Should I Set the Memory Management Parameters?

On a high level, there are two memory segments:

• Shared memory: Also called the system global area (SGA), this is the memory used by the Oracle instance.

• Session-based memory: Also called program global area (PGA), this is the memory that is occupied by sessions in the database. It is used to perform database operations, such as sorts and aggregations.

Oracle Database can automatically tune the distribution of the memory components in two memory areas. As a result, you must set only the following parameters:

• SGA_TARGET

  The SGA_TARGET parameter is the amount of memory you want to allocate for shared memory. For a data warehouse, the SGA can be relatively small compared to the total memory consumed by the PGA. To get started, assign 25% of the total memory you allow Oracle Database to use to the SGA. The SGA, at a minimum, should be 100 MB.

• PGA_AGGREGATE_TARGET

  The PGA_AGGREGATE_TARGET parameter is the target amount of memory that you want the total PGA across all sessions to consume. As a starting point, you can use the following formula to define the PGA_AGGREGATE_TARGET value:

  • PGA_AGGREGATE_TARGET = 3 * SGA_TARGET If you do not have enough physical memory for the PGA_AGGREGATE_TARGET to fit in memory, then reduce PGA_AGGREGATE_TARGET.

• MEMORY_TARGET and MEMORY_MAX_TARGET

  The MEMORY_TARGET parameter enables you to set a target memory size and the related initialization parameter, MEMORY_MAX_TARGET, sets a maximum target memory size. The database then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and aggregate program global area (PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the database also prevents you from setting the target memory size too low.

Example: Setting an Initialization Parameter

You can set an initialization parameter by issuing an ALTER SYSTEM statement, as follows:

ALTER SYSTEM SET SGA_TARGET = 1024M;

What Other Initialization Parameter Settings Are Important?

A good starting point for a data warehouse is the data warehouse template database that you can select when you run the Database Configuration Assistant (DBCA). However, any database will be acceptable as long as you make sure you take the following initialization parameters into account:

• COMPATIBLE

  The COMPATIBLE parameter identifies the level of compatibility that the database has with earlier releases. To benefit from the latest features, set the COMPATIBLE parameter to your database release number.

• OPTIMIZER_FEATURES_ENABLE

  To benefit from advanced cost-based optimizer features such as query rewrite, make sure this parameter is set to the value of the current database version.

• DB_BLOCK_SIZE

  The default value of 8 KB is appropriate for most data warehousing needs. If you intend to use table compression, consider a larger block size.

• DB_FILE_MULTIBLOCK_READ_COUNT

  The DB_FILE_MULTIBLOCK_READ_COUNT parameter enables reading several database blocks in a single operating-system read call. Because a typical workload on a data warehouse consists of many sequential I/Os, make sure you can take advantage of fewer large I/Os as opposed to many small I/Os. When setting this parameter, take into account the block size as well as the maximum I/O size of the operating system, and use the following formula:

  • DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE = <maximum operating system I/O size>

  Maximum operating-system I/O sizes vary between 64 KB and 1 MB.

• PARALLEL_MAX_SERVERS

  The PARALLEL_MAX_SERVERS parameter sets a resource limit on the maximum number of processes available for parallel execution. Parallel operations need at most twice the number of query server processes as the maximum degree of parallelism (DOP) attributed to any table in the operation.

  Oracle Database sets the PARALLEL_MAX_SERVERS parameter to a default value that is sufficient for most systems. The default value for PARALLEL_MAX_SERVERS is as follows:

  • (CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5)

  This might not be enough for parallel queries on tables with higher DOP attributes. Oracle recommends users who expect to run queries of higher DOP to set PARALLEL_MAX_SERVERS as follows:

  • 2 x DOP x NUMBER_OF_CONCURRENT_USERS

  For example, setting the PARALLEL_MAX_SERVERS parameter to 64 will allow you to run four parallel queries simultaneously, assuming that each query is using two slave sets with a DOP of eight for each set.

  If the hardware system is neither CPU-bound nor I/O bound, then you can increase the number of concurrent parallel execution users on the system by adding more query server processes. When the system becomes CPU- or I/O-bound, however, adding more concurrent users becomes detrimental to the overall performance. Careful setting of the PARALLEL_MAX_SERVERS parameter is an effective method of restricting the number of concurrent parallel operations.

• PARALLEL_ADAPTIVE_MULTI_USER

  The PARALLEL_ADAPTIVE_MULTI_USER parameter, which can be TRUE or FALSE, defines whether or not the server will use an algorithm to dynamically determine the degree of parallelism for a particular statement depending on the current workload. To take advantage of this feature, set PARALLEL_ADAPTIVE_MULTI_USER to TRUE.

• QUERY_REWRITE_ENABLED

  To take advantage of query rewrite against materialized views, you must set this parameter to TRUE. This parameter defaults to TRUE.

• QUERY_REWRITE_INTEGRITY

  The default for the QUERY_REWRITE_INTEGRITY parameter is ENFORCED. The database will rewrite queries against only fully up-to-date materialized views, if it can base itself on enabled and validated primary, unique, and foreign key constraints.

  In TRUSTED mode, the optimizer trusts that the data in the materialized views is current and the hierarchical relationships declared in dimensions and RELY constraints are correct.

• STAR_TRANSFORMATION_ENABLED

  To take advantage of highly optimized star transformations, make sure to set this parameter to TRUE.

Installing the Oracle Warehouse Builder Demonstration

In subsequent topics, this guide uses exercises from OBEs for Oracle Warehouse Builder to illustrate how to consolidate data from multiple flat file sources, transform the data, and load it into a new relational target. (OBE stands for Oracle By Example.)

The exercises and examples are available on Oracle Technology Network (OTN) at http://www.oracle.com/technology/obe/admin/owb_main.htm. To facilitate your learning of the product, the demonstration provides you with flat file data and scripts that create various Warehouse Builder objects. The OBE pages provide additional information about OWB and the latest information about the exercises.

To perform the Warehouse Builder exercises presented in this guide, complete the following steps:

1. Download the demonstration. (The OTN

   • Navigate to the location for OWB examples, which is available on OTN from the following location:

     http://www.oracle.com/technology/obe/admin/owb_main.htm

   • Click the link for the Oracle By Example (OBE) set for the latest release.

   The demonstration comprises a set of files in a zip archive called owbdemo_files.zip.

   The zip archive includes a SQL script, two files in comma-separated values format, and scripts written in Tcl.

2. (Optional) Download xsales.zip from the same link, which includes XSALES table data.

3. Edit the script owbdemoinit.tcl.

   The script owbdemoinit.tcl defines and sets variables used by the other tcl scripts. Edit the following variables to match the values in your computer environment:

   • set tempspace TEMP

   • set owbclientpwd workspace_owner

   • set sysuser sys

   • set syspwd pwd

   • set host hostname

   • set port portnumber

   • set service servicename

   • set project owb_project_name

   • set owbclient workspace_owner

   • set sourcedir drive:/newowbdemo

   • set indexspace USERS

   • set dataspace USERS

   • set snapspace USERS

   • set sqlpath drive:/oracle/11.1.0/db_1/BIN

   • set sid servicename

4. Execute the Tcl scripts from the Warehouse Builder scripting utility, OMB Plus.

   For Windows, select Start, Programs, Oracle, Warehouse Builder and then select OMB Plus.

   For UNIX, locate owb home/owb/bin/unix and then execute OMBPlus.sh

   At the OMB+> prompt, enter the following command to change to the directory containing the scripts:

   cd drive:\\newowbdemo\\

   Run all of the Tcl scripts in desired sequence by entering the following command:

   source loadall.tcl

5. Launch the Design Center and log in to it as the workspace owner, using the credentials you specified in the script owbdemoinit.tcl.

6. Verify that you successfully set up the Warehouse Builder client to follow the demonstration.

   In the Design Center, expand the Locations node, which is on the right side and in the Connection Explorer. Expand Databases and then Oracle. The Oracle node should include the follow locations:

   OWB_REPOSITORY

   SALES_WH_LOCATION

When you successfully install the Warehouse Builder demonstration, the Design Center displays with an Oracle module named EXPENSE_WH.