Solved

How to Enable Parallel Processing in a Datawarehouse Environment

Posted on 2003-11-26
3
3,187 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
Comment Utility
--->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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now