Solved

How to Enable Parallel Processing in a Datawarehouse Environment

Posted on 2003-11-26
3
3,191 Views
Last Modified: 2011-10-03
I am using Oracle 9i Rel 2 Version 9.2.0.4 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?

Thanks,

0
Comment
Question by:DonFreeman
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
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:
PARALLEL_MIN_SERVERS

PARALLEL_MAX_SERVERS

PARALLEL_AUTOMATIC_TUNING = TRUE
# 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

ALTER TABLE TAB_XXX PARALLEL (DEGREE 8);
Put hints in SQL statements to indicate that Oracle should try to execute them in parallel:

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

0
 
LVL 47

Expert Comment

by:schwertner
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:
ALTER SYSTEM SET par_name=value SCOPE=BOTH
or to create PFILE from SPFILE:
CREATE PFILE='c:\....' FROM SPFILE
to edit the pfile and after that to recreate the SPFILE
CREATE SPFILE='....' FROM PFILE='....'
0
 
LVL 47

Expert Comment

by:schwertner
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

809 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