From:Steve Adams
Date:18-Sep-2000 17:12
Subject:   Parallel query slower than serial

Have another look at the Ixora tip entitled Disk Configuration for Parallel Table Scans to see why striped disks are bad for parallel table scans.

To identify the contention you would need to use utlbstat/utlestat or StatsPack to compare the I/O times for the parallel query with those for the equivalent serial query. Yes, the V$FILESTAT section of the report would be a good place to look.

The table partitions have been placed on striped disks(EMC), nevertheless isn't v$filestat the view that I need to refer to , to identify I/O contention ?.

It could be that the serial plan uses an entirely different approach that is more efficient. It could be that your parameter settings are such that you do not have enough memory to support that number of processes doing large hash joins. It could be due to skew the data distribution. However, this is normally due to I/O contention between the parallel query slaves. It is possible to layout your partitions on disk in such a way as to avoid such I/O contention, but very few people bother to do it. Then they wonder why they get such poor scalability from Oracle's parallel query features!

I have a 10Gig table(which is growing at about 100mb per day) which I have partitioned (range partitioning with has sub partitioning within each partition) based on a date column(indexed and locally partitioned--about 1.5 G in size ) which is used by most of the queries on that table. The table contains information pertaining to the last 3 years and the partitioning has been done in such a way that the data has been divided on a monthly basis for the previous 2 years and on a weekly basis for the current year (as per instructions from our parent branch's DBA dept) , now the problem I face is that when I try to execute parallel queries ( 8 - 10 processes, 2* 400mhz cpus , 500mb SGA and total ram to the tune of 1G) on the table( and a couple of other small tables) I find that the queries are much slower than when they are executed serially -- the execution plan show the use of hash joins . What could be the reason for this ? Could it just be that the machine does not have enough memory to handle all those concurrent parallel process?