How to Enable Parallel Processing in a Datawarehouse Environment

Posted on 2003-11-26
Last Modified: 2011-10-03
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?


Question by:DonFreeman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 23

Accepted Solution

seazodiac earned 250 total points
ID: 9826261
--->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 ...

LVL 48

Expert Comment

ID: 9848647
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
LVL 48

Expert Comment

ID: 9848724
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.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question