Using Parallel hints in Oracle

Hi All,

Before we start using parallel hints we should understand the concept of parallel hints. (helpful both in oracle and SQL server)

Note -- I have seen lot of software engineers using parallel hints just like that to speed up query without looking at explain plans withough giving much thought. Parallel hints is your last option in query optimization , if you have come to it after 2 or 3 days of query tuning its a super bad idea . Read explain plans try to improve plans and after all has been taken care of and upto the highest level then think of parallel hints


When you say parallel execution is done . It does not mean that multiple processors have been used. You can use multiple processes within the same processor. A classic case is a CPU with less utilization. 

http://docs.oracle.com/cd/B10501_01/server.920/a96520/tuningpe.htm

for parallel hints to work well you have to have enough i/o bandwidth and sufficient memory (RAM) to support memory intensive work (sorts, hashing)

At least one of the tables specified in the query requires one of the following:
A full table scan
An index range scan spanning multiple partitions

Parallel operation can be enabled in different ways

1. Create table with parallel degree

2. Setting parallelism at session level

3. Using hint while running select statements  
3.  

Understanding Parallel plans is very different from understanding serial plans. Below is a link

Understanding Parallel Explain plans