How to Enable Parallel Processing in a Datawarehouse Environment

I am using Oracle 9i Rel 2 Version Enterprise Edition on Windows 2000 Advanced Server SP-4 installed on a 2X2GHZ Dell server supporting a new data warehouse.

We just switched over to Enterprise Edition and I'm trying to implement all of the DW features that I can.  The word parallel is used in a lot of the functionality and I'm not sure that I understand all of it's application.

First question, is there a difference between parallel server and parallel processing?  

Second question:  These are the parameters I have and their settings.  What do I have to change to implement parallel processing?

fast_start_parallel_rollback         string                           LOW
log_parallelism                          integer                          1
parallel_adaptive_multi_user         boolean                          FALSE
parallel_automatic_tuning            boolean                          FALSE
parallel_execution_message_size      integer                          2148
parallel_instance_group              string
parallel_max_servers                 integer                          5
parallel_min_percent                 integer                          0
parallel_min_servers                 integer                          0
parallel_server                      boolean                          FALSE
parallel_server_instances            integer                          1
parallel_threads_per_cpu             integer                          2
recovery_parallelism                 integer                          0

Question:  I have two Intel Xeon(512k cache) CPUs that actually appear, I guess because of the hyperthreading technology, as four processors.  How many dbwriters and io-slaves should I configure.  Does configuring these things automatically provide for parallel operations?


Who is Participating?
seazodiacConnect With a Mentor Commented:
--->First question, is there a difference between parallel server and parallel processing?  
Parallel Query (PQ) allows one to break a SELECT or DML statements into multiple smaller chunks and have PQ slaves execute those smaller chunks on separate CPU's in a single box.
Oracle parallel Server (PS ) or later called Oracle Real Application Clusters (RAC) allows one to take advantage of a multi-node clustered environment for availability and performance reasons. It is commonly used to access a very large database from different nodes of a cluster.

If both RAC and OPQ are available one can split operations across multiple CPUs and multiple nodes in a cluster for even further performance improvements

----->Second question:  These are the parameters I have and their settings.  What do I have to change to implement parallel processing?

The following INIT.ORA initialization parameters can be set to control Parallel Query execution:


# enable intelligent defaults for parallel execution parameters (8i and above)

After setting the INIT.ORA parameters necessary for Parallel Query to work, do the following:
Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

Put hints in SQL statements to indicate that Oracle should try to execute them in parallel:

SELECT /*+ PARALLEL(table_alias, degree, nodes) */ FROM table ...

In Oracle 9i init<sid>.ora is not the place where the parameters reside. You have to set the parameters either in SPFILE using the statement:
or to create PFILE from SPFILE:
to edit the pfile and after that to recreate the SPFILE
Because you will use Datawarehouses (synonym is OLAP - On Line Analytycal Processing) you should relay on Oracle Instalation decision. In Oracle 9i the Installer asks the DBA how to optimize the instance. You should answer OLAP. So Oracle Installer will set most of the parameters in the SPFILE to the right values. Basically this means that a big blocksize is preferable (the standard choice is 8192 bytes, but in OLAP is not enough), big value for  DB_FILE_MULTIBLOCK_READ_COUNT parameter.

About your second question:
DBWR_IO_SLAVES is relevant only on systems with only one database writer
process (DBW0). It specifies the number of I/O server processes used by the DBW0
process. The DBW0 process and its server processes always write to disk. By default,
the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server
processes used by the ARCH and LGWR processes is set to 4. However, the number of
I/O server processes used by Recovery Manager is set to 4 only if asynchronous
I/O is disabled (either your platform does not support asynchronous I/O or disk_
asynch_io is set to false.
Typically, I/O server processes are used to simulate asynchronous I/O on platforms
that do not support asynchronous I/O or that implement it inefficiently. However,
you can use I/O server processes even when asynchronous I/O is being used. In
that case the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O
throughput, even if asynchronous I/O is enabled.
All Courses

From novice to tech pro — start learning today.