Database Template

Initialization Parameters

Data Files

Control Files

Redo Log Groups

Create the Data Warehouse

Review the .ORA Files

1. Open the $ORACLE_HOME/network/admin/tnsnames.ora file. You should see something like the following entry:


ORADWH.WORLD =
(DESCRIPTION =
  (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = oradwh.world)
  )
)

2. Open the $ORACLE_HOME/network/admin/listener.ora file. You should see something like the following entry:


(SID_DESC =
    (GLOBAL_DBNAME = oradwh.world)
    (ORACLE_HOME = /u02/oracle/product/82)
    (SID_NAME = oradwh)
  )

Add a Plan Table to the Data Warehouse

1. Start a SQL*Plus session, enter the following commands:


sqlplus system/manager@ oradwh.world;
@?/rdbms/admin/utlxplan
 

2. Make sure that Oracle TopSessions is working correctly by launching it from the desktop and connecting to the Data Warehouse database. Select Start-> Programs -> Oracle - Ora81Home -> Diagnostics Pack -> TopSessions.
3. At the login script, enter the username and password as system/manager, enter the service as oradwh.world, and click OK.
4. TopSessions should start with no error messages. Close TopSessions.

Add Specific Data Warehousing Initialization Parameters to the Database

1. First, stop the Data Warehouse database.

sqlplus system/manager@oradwh as SYSDBA

connect internal;

shutdown;

2. Edit the database initialization file $ORACLE_HOME/dbs/initoradwh.ora.The Oracle Database Configuration Assistant has already set some of the following data warehouse parameters. It also depends on the changes you made during your database template creation.

shared_pool_size = 15728640
In general, shared pool size should be equal to approximately 20% of the total physical memory.

db_block_buffers = 2048

The number of database block buffers should be equal to approximately 2% of the total physical memory. The actual parameter value can be calculated as


(2% of physical memory)/db_block_size).

db_block_size = 8192

Database blocks should be large in data warehouses. This improves performance of operations involving scans of large amounts of data. (Check if your Operating Systems supports 16k)

sort_area_size = 66560

For queries, sort area size should remain relatively small, even for large databases. However, sort area size should be increased while building indexes.

4. Add the following lines:

job_queue_processes = 1
open_links = 4

In Oracle 8i, Materialized Views refreshes require at least one job queue process.

hash_area_size = 8388608

Hash joins perform better with a larger hash area size. In a database that has a significant number of joins, up to 75% of the physical memory can be dedicated for hash joins.

Parallel query parameters have been greatly simplified in Oracle9i.

The following parameters are recommended for both new data warehouses and existing data warehouses that are being upgraded to Oracle9i:

parallel_automatic_tuning = true
parallel_threads_per_cpu = 4

This parameter determines the default number of parallel query processes. Typically, two parallel processes per CPU provide good performance. However, for systems with a smaller number of CPUs, more parallel processes may be desired.

optimizer_mode = "all_rows"
always_anti_join = hash

All data warehouses should use the cost-based optimizer.

All advanced performance features, such as star query support, hash joins, and bitmap indexes are accessible only via the cost-based optimizer.

The following parameters may provide better performance for certain environments:

always_semi_join = hash
hash_multiblock_io_count = 8
 

One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.
To enable query rewrite set:

query_rewrite_enabled = true
query_rewrite_integrity = trusted

To get the best possible performance for star queries, it is important to follow some basic guidelines. A bitmap index should be built on each of the foreign key columns of the fact table or tables. The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE.
This enables an important optimizer feature for star-queries. It is set to FALSE by default for backwards-compatibility.

star_transformation_enabled = true

To collect and analyze the summary event set, you must do the following(optional):
Set six initialization parameters to collect data via Oracle Trace.
Enabling these parameters incurs some additional overhead at database connection, but is otherwise transparent.

oracle_trace_collection_name = oraclesm
oracle_trace_collection_path= location
oracle_trace_collection_size = 0
oracle_trace_enbable = true
oracle_trace_facility_name = oraclesm
oracle_trace_facility_name = location

5. Save the file.
6. Restart the database.

Load The Data Warehouse with some Example Data

Create a new tablespace called demo size 200MB. From a SQL*Plus session, run this script: sh_main.sql.
It creates the SH schema and also creates and populates the following tables:

 

Table	Number of Rows
customers	50.000
countries	19
products	10.000
sales	1.016.271
times	1461
promotions	500
channels	5

You are now ready to examine the data warehouse schema itself.