Parallel degree for the tables

Hai,

I wanted to know , if I give parallel degree of 5 to a table which is having a lot of inserting , will it boost up any performance. I am using 8.0.4.0 ver oracle and OS is Windows NT ver 4.0 having only one CPU.


Thanks ...Thomas

Probably not, unless your table is accross a number of disks (or on RAID).

I have tried parallel query on our database running on 3 different servers....

1. 8 Disks, 2 CPU - Tables not striped. With PQ, the system ran about the same, maybe a little slower.

2. EMC array, 6 CPU (but heavily loaded machine... CPU usage usually >70%+). With PQ, same as 1.

3. EMC array, 36 CPU (lightly loaded machine... CPU usage normally < 20%). Excellent results - Full table scans reduced from approx 50secs to <15 seconds.

Terry

Parallel Options only benefit with multiple CPUs.

PARALLEL DEGREE of 5 helps in reading the data (i.e SELECT statement) from a table which is partitioned across multiple disks. How ever if you insert bulk number of rows through SQL*LOADER, then the PARALLEL option will insert the rows very fast.

Through User Interface where it inserts one row at a time, PARALLEL option may not help to speed up the row insertion.

Watch out with parallel indexes if you want to use the RBO....
Check out this doc on metalink:

Doc ID: Note:70008.1
Subject: ALERT: CBO may be used when expecting RBO if indexes have a PARALLEL clause

The issue described here affects Oracle releases:
8.0.5
8.1.5
The issue does NOT affect releases:
8.0.3
8.0.4
8.0.6
8.1.6 onwards

Basically what it says is that if you have Oracle set to use the RBO but you have
some indexes in the parallel mode then Oracle is going to use the CBO on queries
agains the tables that those indexes belong to, and there is nothing you can
do about it except issue a 'alter index xxx.yyy noparallel' to fix it.