[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to Enable Parallel Processing in a Datawarehouse Environment

Posted on 2003-11-26
3
Medium Priority
?
3,208 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
[X]
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
3 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 1000 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 48

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 48

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

649 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